Looping an offset

Question

I have a sheet of data that 20 rows by 10 columns.

I need a certain piece of code to be run on each cell and I am having trouble looping through the rows, columns.

Here is what I got:

ActiveSheet.Cells(1, 1).Select

Call testing                             ' runs code in first cell

--

For i = 0 To 10                       ' sucessfully goes through each column, all the way to the end.

ActiveCell.Offset(0, 1).Activate

Call testing

Next i

On Error Resume Next

End

--

ActiveCell.Offset(0, -10).Activate  ' need to go back to the column where we began.

Now I need to go down one row, run the code through each cell in the row,

and go down to the next row 20 times.

I imagine there is a smarter way to do this.

Maybe I don't have to activate each individual cell to run the code.

And maybe there is a way to make the code more compact by including the row and column offset in the same loop.

Thank you

Answer 1

Hi,

You don't need to activate anything, you can do it like this. This starts in A1 and loops to J1. It then does the same for row 2 thru row 20.

Sub somesub()

Dim x As Long, y As Long

'20 rows 10 cols

For x = 1 To 20

   For y = 1 To 10

    MsgBox Cells(x, y).Value

   Next

Next

End Sub