Two Strings won't concatenate VBA Excel

Question

I am writing a little Excel-Macro with VBA. Now I would like to concat two Strings and save them into a String-Array.

What I got:

Dim rowNumberString As String
Dim colIndexString As String
Dim headerArray(1 To colIndexArrayRange) As String

colIndexNumber = 14
colCount = 5
rowNumberString = "12"
addAnotherColumnToArray = True

' Fill the column array with all the month's entries
While addAnotherColumnToArray
    colCount = colCount + 1
    colIndexNumber = colIndexNumber + 1

    If colIndexArray(colCount) = "" Then
        colIndexString = Split(Cells(1, colIndexNumber).Address(True, False), "$")(0)
        colIndexArray(colCount) = colIndexString & rowNumberString
    End If

    Debug.Print colCount & "=" & colIndexArray(colCount)

    If (colIndexNumber > 61) Then
        addAnotherColumnToArray = False
    End If
Wend

The output:

6=O
7=P
8=Q
9=R
10=S
11=T
12=U
' ....

So it seems that this line:

` colIndexArray(colCount) = colIndexString & rowNumberString`

is not concatenating the String the way it should. What did I do wrong? I thought the &-Operator would always work for Strings in VBA.

Your code worked for me (I did have to remove your If... Then Statement). Why don't you go about this a completely different way? Create a Range, and instead of doing a Do While...Loop, just do For Each Cell In YourRng? You can then use Cell.Address. It appears you are trying to make this much more complicated than it needs to be. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Oct 9, 2017 at 15:21

K.Dᴀᴠɪs · Accepted Answer · 2017-10-09 15:37:10Z

2

As I stated in my comment, you could be going about this in a completely different way.

Not sure what you are trying to accomplish, but a For...Next statement using Objects, rather than Strings should help you accomplish your task.

Option Explicit

Sub TEST()

    Dim ws As Worksheet, Rng12 As Range, Cell As Range
    Set ws = ThisWorkbook.Worksheets(1)
    Set Rng12 = ws.Range("L12:Z12") 'Adjust your range

    For Each Cell In Rng12
        Debug.Print Cell.Address
    Next Cell

End Sub

edited Oct 9, 2017 at 15:37

answered Oct 9, 2017 at 14:49

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K.Dᴀᴠɪs Over a year ago

Updated answer to show new method using For...Next rather than Do While... Wend.

Collectives™ on Stack Overflow

Two Strings won't concatenate VBA Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related