Concatenate in VBA?

[TraceyH]

I have this code, but rather than it copying Cell E7 in to Cell E5, I wondered if it was possible to concatenate E7, E9, H9 & H10 (I've written a formula but can't think how to do it in VBA): =IF(E7="","",CONCATENATE(E7,", ",TEXT(E9,"dd-mmm-yy"),", ",TEXT(H9,"hh:mm")," to ",TEXT(H10,"hh:mm")))

I only ask, as the Course Name (E7) isn't unique, so want to add the date and time to the Event to make a UI?

Sub Event_SaveUpdate()
With Form
    If .Range("E7").Value = Empty Then
        MsgBox "Please choose a name for the Event (from the list provided) BEFORE trying to save"
        Exit Sub
    End If
    If .Range("B5").Value = Empty Then 'New Event
        .Range("D3").Value = .Range("B6").Value 'Event ID
        EventRow = Data.Range("C99999").End(xlUp).Row + 2 'Find first row without data
        Data.Range("C" & EventRow).Value = .Range("B6").Value 'Event ID
    Else 'Existing Event
        EventRow = .Range("B5").Value 'Event Row
    End If
    For EventCol = 4 To 27
        Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol).Value).Value 'Add event to Data worksheet
    Next EventCol
    .Range("B3").Value = True 'Event Update to TRUE
    .Range("E5").Value = .Range("E7").Value 'Event Name in Drop down list
    .Range("B3").Value = False 'Event Update to FALSE
    
End With
End Sub

[Paul_Hossler]

Not tested

Option Explicit


' =IF(E7="","",CONCATENATE(E7,", ",TEXT(E9,"dd-mmm-yy"),", ",TEXT(H9,"hh:mm")," to ",TEXT(H10,"hh:mm")))


Sub drv()
    With ActiveSheet
        If Len(.Range("E7").Value) = 0 Then
            .Range("E5").ClearContents
        
        Else
            .Range("E5").Value = .Range("E7").Value & ", " & Format(.Range("E9").Value, "dd-mmm-yy") & ", " & _
                Format(.Range("H9").Value, "hh:mm") & " to " & Format(.Range("H10").Value, "hh:mm")
        End If
    End With






End Sub

[snb]

Sub M_snb()
  msgbox iif(cells(7,5)="","",join(array([E7],[E9],[H9] & " to " & [H10]),", "))
End Sub

[TraceyH]

Thanks both - where would these bits of code fit within my existing?

Sub Event_SaveUpdate()
With Form
    If .Range("E7").Value = Empty Then
        MsgBox "Please choose a name for the Event (from the list provided) BEFORE trying to save"
        Exit Sub
    End If
    If .Range("B5").Value = Empty Then 'New Event
        .Range("D3").Value = .Range("B6").Value 'Event ID
        EventRow = Data.Range("C99999").End(xlUp).Row + 2 'Find first row without data
        Data.Range("C" & EventRow).Value = .Range("B6").Value 'Event ID
    Else 'Existing Event
        EventRow = .Range("B5").Value 'Event Row
    End If
    For EventCol = 4 To 27
        Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol).Value).Value 'Add event to Data worksheet
    Next EventCol
    .Range("B3").Value = True 'Event Update to TRUE
    .Range("E5").Value = .Range("E7").Value 'Event Name in Drop down list
    .Range("B3").Value = False 'Event Update to FALSE
    
End With
End Sub

I tried this, but get an "Expected End Sub" error?

Sub Event_SaveUpdate()
With Form
    If .Range("E7").Value = Empty Then
        MsgBox "Please choose a name for the Event (from the list provided) BEFORE trying to save"
        Exit Sub
    End If
    If .Range("B5").Value = Empty Then 'New Event
        .Range("D3").Value = .Range("B6").Value 'Event ID
        EventRow = Data.Range("C99999").End(xlUp).Row + 2 'Find first row without data
        Data.Range("C" & EventRow).Value = .Range("B6").Value 'Event ID
    Else 'Existing Event
        EventRow = .Range("B5").Value 'Event Row
    End If
    For EventCol = 4 To 27
        Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol).Value).Value 'Add event to Data worksheet
    Next EventCol
    .Range("B3").Value = True 'Event Update to TRUE
    Sub drv()
        If Len(.Range("E7").Value) = 0 Then
            .Range("E5").ClearContents
        Else
            .Range("E5").Value = .Range("E7").Value & ", " & Format(.Range("E9").Value, "dd-mmm-yy") & ", " & _
                Format(.Range("H9").Value, "hh:mm") & " to " & Format(.Range("H10").Value, "hh:mm")
        End If
    End With
    '.Range("E5").Value = .Range("E7").Value 'Event Name in Drop down list
    .Range("B3").Value = False 'Event Update to FALSE
    
End With
End Sub

[snb]

Please use code tags !!

[Paul_Hossler]

Probably below the marked line

Sub Event_SaveUpdate()
With Form
    If .Range("E7").Value = Empty Then
        MsgBox "Please choose a name for the Event (from the list provided) BEFORE trying to save"
        Exit Sub
    End If

    If .Range("B5").Value = Empty Then 'New Event
        .Range("D3").Value = .Range("B6").Value 'Event ID
        EventRow = Data.Range("C99999").End(xlUp).Row + 2 'Find first row without data
        Data.Range("C" & EventRow).Value = .Range("B6").Value 'Event ID

    Else 'Existing Event
        EventRow = .Range("B5").Value 'Event Row
    End If

    For EventCol = 4 To 27
        Data.Cells(EventRow, EventCol).Value = .Range(Data.Cells(1, EventCol).Value).Value 'Add event to Data worksheet
    Next EventCol

    .Range("B3").Value = True 'Event Update to TRUE


'------------------------------.Range("E5").Value = .Range("E7").Value 'Event Name in Drop down list
        If Len(.Range("E7").Value) = 0 Then
            .Range("E5").ClearContents
        
        Else
            .Range("E5").Value = .Range("E7").Value & ", " & Format(.Range("E9").Value, "dd-mmm-yy") & ", " & _
                Format(.Range("H9").Value, "hh:mm") & " to " & Format(.Range("H10").Value, "hh:mm")
        End If

    .Range("B3").Value = False 'Event Update to FALSE
End With
End Sub