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.

Related: https://stackoverflow.com/questions/1727699/how-to-concatenate-strings-in-vba — jsheeran, Oct 09 '17 at 14:49
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, Oct 09 '17 at 15:21

K.Dᴀᴠɪs · Answer 1 · 2017-10-09T15:37:10.323

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 09 '17 at 15:37

answered Oct 09 '17 at 14:49

K.Dᴀᴠɪs

9,945
11
33
43

Updated answer to show new method using `For...Next` rather than `Do While... Wend`. – K.Dᴀᴠɪs Oct 09 '17 at 15:41

Two Strings won't concatenate VBA Excel

1 Answers1