0

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!

1

2 Answers 2

1

Place this after your End IF

If concat1(i) Is Nothing Then Exit Sub
Sign up to request clarification or add additional context in comments.

Comments

0

Did you try adding if concat1(i) = false then exit sub before incrementing i?

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.