How to increment column in Excel VBA?

Question

Hi I am trying to compare two sets of data by having indicators if they increased, decreased, or stayed the same. I was able to get it working on one column. My problem is I can't loop it on multiple columns.

Basically:

If A1 = C1 then D1.Value = 0
If A1 > C1 then D1.Value = 1
If A1 < C1 then D1.Value = 2

I've tried to do the "do while" to add increments on the columns but it did not work.

Sub ChangeIndicator2()

Dim i As Long
Dim a As Long
Dim b As Long
Dim x As Long
Dim y As Long
Dim ProgramCount As Long

i = 2
a = 8
b = 2

x = 0
y = 8
ProgramCount = 12

Do While y <= ProgramCount

    For Each c In Worksheets("Sheet1").Range("A2:A20").Offset(x, y)


            If Worksheets("Sheet1").Cells(i, a).Value = Worksheets("Sheet1").Cells(i, b).Value Then
                c.Value = 0
            ElseIf Worksheets("Sheet1").Cells(i, a).Value < Worksheets("Sheet1").Cells(i, b).Value Then
                c.Value = 1
            ElseIf Worksheets("Sheet1").Cells(i, a).Value > Worksheets("Sheet1").Cells(i, b).Value Then
                c.Value = 2
            End If
            i = i + 1

    Next c
    a = a + 2
    b = b + 2
    y = y + 2

Loop

End Sub

Only the first column works, the second column only shows 0 values.

The i value is continuously adding up by 1, should the i reset to 2 for every Do while loop? — kevin hiew
– kevin hiew, Commented Aug 23, 2019 at 1:06

L42 · Accepted Answer · 2019-08-23 02:12:20Z

1

So basically, what you want to do is compare 2 columns which are 2 columns apart and repeat that on another pair of columns which is 8 columns from the first column. If my assumption is correct then have a go at this:

For i = 0 To (ProgramCount * 8) Step 8
  With Worksheets("Sheet1").Range("A2:A20").Offset(, i + 3)
    .FormulaR1C1 = "=IF(RC[-3]=RC[-1],0,IF(RC[-3]>RC[-1],1,2))"
    .Value2 = .Value2
  End With
Next

Adjust the offset to suit your needs (I may have misunderstood the actual columns you target to update). Hope this helps.

answered Aug 23, 2019 at 2:12

L42

19.8k11 gold badges47 silver badges70 bronze badges

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

5 Comments

Don Paz Over a year ago

Yes, my actual target columns are actually located on two separate sheets, but this is perfect. Thank you so much!

Don Paz Over a year ago

Hi again, so i was editing the offset to my actual target column and the first time i did it, i placed the 2 columns being compared on column B, it worked. But i realized that i should have made it to column D instead, i then edited the offset values the same way i did earlier but it wouldn't work with column D. Please help.

Don Paz Over a year ago

For i = 0 To ((ProgramCount * 2) - 1) Step 2 With Worksheets("Sheet1").Range("A6:A67").Offset(, i + 4) .FormulaR1C1 = "=IF('Sheet2'!RC[-3]='Sheet1'!RC[-3],0,IF('Sheet2'!RC[-3]>'Sheet1'!RC[-3],1,2))" .Value2 = .Value2 End With Next

L42 Over a year ago

@DonPaz The way I set it up is pointing at Column D. Now the tricky part in this approach is you will have to adjust the formula too for it to work if you edit the VBA part offsets. To correct the formula, remove this part first: .Value2 = .Value2. Run your edited code and see if the formula is correct (if it is comparing the correct columns). If not, edit the formula. Once you get it right, go to File tab > Options > Formulas. Check the R1C1 reference style. Then the formula you typed will be in R1C1 notation. Copy that and use it in your macro.

Don Paz Over a year ago

Hi again, so i've been using the code that you've provided and it works perfectly. I have been trying to add another condition to it, but i've not been successful. Basically I wanted to have another condition that if the cell has this "word" or "special character" then do the opposite. Something like this: .FormulaR1C1 = "=IF(RC[-1]="STD" Or "/", IF(RC[-3]=RC[-1],0,IF(RC[-3]>RC[-1],1,2)), IF(RC[-3]=RC[-1],0,IF(RC[-3]>RC[-1],1,2)) )"

Collectives™ on Stack Overflow

How to increment column in Excel VBA?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related