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