Concatenation of string+variable in Excel macro

Question

In the part of cnt1, I'm trying to use an integer (called A) that I'll increase in a loop so it goes from shtSheet1 to shtSheet5. In my code, I have shtSheet$A to clarify what I want to do but I don't know if visual basic allow that kind of concatenation. I'm only used to coding bash Scripts.

Sub RunCompare()

Call compareSheets("PROD", "OSA", "UAT", "INT", "TEST")

End Sub

Sub compareSheets(shtSheet1 As String, shtSheet2 As String, shtSheet3 As String, shtSheet4 As String, shtSheet5 As String)

Dim c As Integer, j As Integer, i As Integer, mydiffs As Integer, cnt1 As Integer, cnt2 As Integer, A As Integer, B As Integer
Dim noexist As Integer

A = 1
B = 2

cnt1 = Worksheets(shtSheet).Cells.SpecialCells(xlCellTypeLastCell).Row
cnt2 = Worksheets(shtSheetB).Cells.SpecialCells(xlCellTypeLastCell).Row

'For each cell in sheet2 that is not the same in Sheet1, color it yellow

LoopStart:

For i = 1 To cnt2
    For j = 1 To cnt1
        If ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(j, 1).Value Then
            For c = 2 To 22
                If Not ActiveWorkbook.Worksheets(shtSheetB).Cells(i, c).Value = ActiveWorkbook.Worksheets(shtSheet2).Cells(j, c).Value Then
                    ActiveWorkbook.Worksheets(shtSheet2).Cells(i, c).Interior.Color = vbYellow
                    mydiffs = mydiffs + 1
                End If
            Next
        Exit For
        End If
        If j = cnt1 Then
            ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Interior.Color = vbRed
        End If
    Next
Next

A = A + 1
B = B + 1

If A <= 4 Then
    GoTo LoopStart
End If 

End Sub


Sub Clear_Highlights_this_Sheet()
ActiveSheet.UsedRange. _
Interior.ColorIndex = xlNone
End Sub

Sub Clear_Highlights_All_Sheets()
Dim sht As Worksheet
For Each sht In sheets
sht.UsedRange.Interior.ColorIndex = xlNone
Next
End Sub

I updated my full macro so it is easier to understand. It compares 2 sheets and I would like to modify it to compare 5 sheets but in a specific way, only comparing the sheets 1-2, 2-3, 3-4, 4-5.

My idea was to use the A and B and a goto to do what I want.

Why not save the names into an array and pass that into your sub instead of a list. Then iterate through each item in your array. — Scott Craner
– Scott Craner, Commented Aug 26, 2015 at 14:50
Your solution my work Scott, I'll have a look on how to use Arrays on vba — Santi
– Santi, Commented Aug 27, 2015 at 6:37

Andre · Accepted Answer · 2015-08-27 06:41:02Z

1

Use a ParamArray to pass the sheet names to your sub, and loop over them.

Sub compareSheets(ParamArray sheets() As Variant)

    Dim vSheet As Variant 
    For Each vSheet In sheets
        cnt = Worksheets(vSheet).Cells.SpecialCells(xlCellTypeLastCell).Row
        ' ...
    Next vSheet

Of course you can also work with indices:

    For i = 0 to 3
        cnt1 = Worksheets(sheets(i)).Cells.SpecialCells(xlCellTypeLastCell).Row
        ' ...
    Next i

edited Aug 27, 2015 at 6:41

answered Aug 26, 2015 at 15:01

Andre

27.7k7 gold badges43 silver badges85 bronze badges

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

2 Comments

Santi Over a year ago

I think your solution won't work for me as I need two loops, one from 1 to 4 and another from 2 to 5.

Santi Over a year ago

Thank you very much, using this plus a few tweaks I managed to make it work. Finally, I used an Array and did the loop with index

Collectives™ on Stack Overflow

Concatenation of string+variable in Excel macro

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related