Posts by benjaminhz

Hi Roy,

Enclosed is the excel file with VBA

In the Worksheet MAIN, the table chart called "Output Table" will display concatened values (done already), but does not keep the hyperlink. SOurce table is on the other worksheet called Other.

What do you think ?

Ben

Hi everyone,

I'm trying to add some code in the following Function in order to keep the hyperlinks from the data i concatenated together, but can't figure out how to do it.

Function ConcatenateRange(ByVal cell_range As Range, _
Optional ByVal seperator As String) As String
Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
For j = 1 To UBound(cellArray, 2)
If Len(cellArray(i, j)) <> 0 Then
newString = newString & (seperator & cellArray(i, j)) & vbLf
End If
Next
Next

If Len(newString) <> 0 Then
newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString
End Function

Any tips is much appreaciated.

Best Benjamin

Re: Macro: Copy Cells To Another Workbook On Closing

Thanks Wigi, I actually could not put my hand on it.

A clue for this issue then ?

Thanks in advance!
Ben

Re: Macro: Copy Cells To Another Workbook On Closing

Any update for this

Re: Macro: Copy Cells To Another Workbook On Closing

Hello Dave,

Working on Excel 2010, I I tried to make good use of the code above but I believe something goes wrong. I modified :
- name of the file
- path folder
- range to copy
- The name of the sheet "Sheet1" with the codename of the Worksheet I have

Here it's how it looks like :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wbTH As Workbook
     
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
    On Error Resume Next
    Set wbTH = Workbooks("NYP_Late PO.xlsm")
    On Error GoTo 0
     
     If wbTH Is Nothing Then
        Set wbTH = Workbooks.Open _
        (Filename:="c:\My Documents\NYP_Late PO.xlsm")
    End If
      
    With Me.Sheet("Cancelled")
        .Range("A1:L500").Copy _
        Destination:=wbTH.Sheets(1).Range("A" & Rows.Count).End(xlUp)(2, 1)
    End With
     
    wbTH.Close SaveChanges:=True
     
    Application.ScreenUpdating = True
    Application.EnableEvents = True
     
End Sub

Is there a step missing ?
Thanks for your help.

Ben

Re: VBA - Check IF empty THEN Modify in a range

It perfectly matches my wish !

Thank you very much
Ben

Hello,

I have been starting to use VBA for a few weeks and I am facing some difficulties to realize the following code :

I want excel to look for each cell in a set range if it's empty. If yes, I want to fill it with a figure (let's say "1").

Sounds easy but still I cannot find a right way to create it. Can anyone brings me some help ?

Thanks
Ben