I'm having some trouble with a macro for Excel. The snippet that's giving me trouble is responsible for:
1) allowing the user to select multiple column headers, one by one 2) taking the contents of each columns, in the order of header selection, and concatenating
Here's the code:
Dim concat1() As Range
Dim rng As Variant
Dim i As Variant
Dim g As Integer
Dim metalabels() As String
Dim concated As String
Dim s As Variant
lastrow = Cells(rows.Count, "A").End(xlUp).Row
i = 0
msgselect = MsgBox("Would you like to concatonate?", vbOKCancel)
On Error GoTo Errhandler
If msgselect = vbOK Then
Do
ReDim Preserve concat1(i)
Set concat1(i) = Application.InputBox("Select the headers you would like to concatonate", Default:=ActiveCell.Address, Type:=8)
msgselect = MsgBox("Another cell?", vbOKCancel)
i = i + 1
Loop While msgselect = vbOK
i = i - 1
Errhandler:
End If
ReDim metalabels(i)
For g = 0 To i
metalabels(g) = concat1(g).Text
Next
ActiveSheet.Range("a1").End(xlToRight).Offset(0, 1).Select
ActiveCell = "Situation"
For h = 1 To lastrow - 1
For g = 0 To UBound(metalabels)
concated = concated + metalabels(g) + ": " + concat1(g).Offset(h, 0).Text + " / "
Next
ActiveCell.Offset(h, 0).Value = concated
concated = ""
Next
End Sub
The problem is here:
Set concat1(i) = Application.InputBox("Select the headers you would like to concatonate", Default:=ActiveCell.Address, Type:=8)
If the user selects "Cancel," the code crashes since the loop depends on vbOK. So, I thought I'd put in an error handler, but, as it is, I get the "object variable or with block not set" error.
As you might sense, I'm still a nube with VB. Any help is greatly appreciated.
Thanks!