shane8johnson
New Member
- Joined
- Sep 7, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
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.
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.
VBA Code:
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