VBA: Nested counter within formula

[shane8johnson]

What I am trying to accomplish is increment my CONCATENATE formula by one. My issue however is that this is already located within a counter that applies the formula to every other row.

I have tried to include detailed comments in my VBA code to follow what everything does. In short, in the code below, H2 is what needs to be incremented by one each time it is ran, within the already existing counter.

Sub CopyCols()
'Where the data is located
Sheets("1").Activate

'LastRow is the last row of the data. Used later so we don't add extra rows
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
 
 'Switch to output sheet which is where we need the data put below
 
 Sheets("Output").Activate
 
 'Counter is used below to add data every other row
 
Dim Counter As Integer

'RangeBuilder is used so I can select the range of the data in sheet one, times 2. For example if sheet 1 has 100 rows, the total range is 200
    Dim rangeBuilder As String
    Let rangeBuilder = "A2:A" & LastRowColumnA * 2
    
'See above comment
Range(rangeBuilder).Select

   'For every row in the current selection...
    For Counter = 1 To Selection.Rows.Count
        'If the row is an odd number (within the selection)...
        If Counter Mod 2 = 1 Then
            'Set the formula to every other row
            'Start Here has data that needs to be concatenated
            'H2 below is what needs to be incremented by 1 every time it is ran, within in the existing counter
            Selection.Rows(Counter).Formula = "=CONCATENATE('Start Here'!H11, '1'!H2)"
        End If
    Next

 

[shane8johnson]

I should add that just replacing H2 with the existing Counter will not achieve what I am looking for. It will make H2 increase to H4 rather than to H3.