0

I am new to VBA in excel and want to use a Do While loop to do some formatting.

What I want to do is compare the value in each cell in column1 (which is names) with the cell directly below it. If the names are match I will automate some calculations. If they do not match I want the loop to continue down the column until it finds the next match.

I want the loop to compare A2 to A3 then A3 to A4 ect.

What is the best way to set up my variables for this? I can do the calculation macros fine, but for some reason this basic component of the loop is making my head hurt.

cheers

shruti1810
  • 3,920
  • 2
  • 16
  • 28
jfizzle
  • 1
  • 1
  • 2

1 Answers1

2

Try to avoid relying on ActiveCell and use direct addressing. The Range.Offset property will let you examine a cell that is one row below the current on in the loop.

    With ActiveSheet    '<- set this worksheet reference properly!
        For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row - 1
            If .Cells(rw, 1).Value = .Cells(rw, 1).Offset(1, 0).Value Then
                ' they are the same - do something
            End If
        Next rw
    End With

In this case, you may wish to opt for the simpler,

            If .Cells(rw, 1).Value = .Cells(rw + 1, 1).Value Then

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1