quote:
Originally posted by G105ALY
Is it possible to use this with a do loop for a range of cells i.e. b14:014?
Sure.
I'll tell you how I usually handle these kinds of things in Excel VBA. Your data is arranged such that you need it to march across a row to various columns.
Sub temp()
'start at the beginning
Range("B14").Activate
'start the loop
Do While ActiveCell.Value <> ""
'''insert here the relevant code that you want
'''done on each cell
'move to the next cell (horizontally)
ActiveCell.Offset(0, 1).Activate
'end the loop
Loop
End Sub
Note several things. I usually have a block of data and never know how many columns or rows there are. Therefore I make the loop go until it hits a blank. (Value <>"")
Second, the Offset function has two arguments. Offset(row#, column#). In this case we want to stick to the same row and move across horizontally, thus, the row offset is zero and the column offset is 1.
If, instead, you were moving down a single column row by row, the code would say:
ActiveCell.Offset(1, 0).Activate
This scenario is far more common in my work, as I usually need to "process" a table of data and do the same thing to each entry in a given column.
Finally, have you considered using range names instead of addresses? Give B14 a name, and use that name in the Range("B14") statement. That way, should you insert columns or rows in the future the name will move with the insert. Otherwise, you'll have to edit your code to whatever the new range address is. That can be a real drag.