Stepping thru Cell References

[Ralph]

Help,

I need to loop thru some code. In so doing I need to
be able to increase the ("C14) cell reference each time I
loop.

sub Traffic()
Windows("Traffic.xls").Activate
Sheets("Sheet1").Select
Range("C14").Select
szCellValue = Worksheets(1).Range("c14").Value
MsgBox szCellValue
end sub

In the above example I have my VBA code reading the value
from Cell ("C14"). In the full Macro I will be using the
Value taken from this reference.


This loop will run for x times, where x is provided by a
user input (msgbox) the Cell reference will always start
at C14 and continue until cell is blank or cell c25 is
reached.

Can anyone help me with this???

Your assistance is appreciated.

 

[Jake Marx]

Hi Ralph,

I'm assuming you used the macro recorder to generate the code initially?
That's a good way to do it when learning VBA, but when you're writing your
own code or modifying recorded code, you should try to avoid activating and
selecting objects if possible. Here is some code that should do what you're
looking for:

Sub test()
Dim nCol As Integer
Dim lRow As Long
Dim bBlank As Boolean

nCol = 3
lRow = 14

With Workbooks("Traffic.xls").Worksheets("Sheet1")
Do While lRow <= 25 And Not bBlank
If Len(.Cells(lRow, nCol).Value) Then
MsgBox .Cells(lRow, nCol).Value
Else
bBlank = True
End If
lRow = lRow + 1
Loop
End With
End Sub

Notice that you can just refer to the Workbook, Worksheet, and Range
directly without selecting them first. The With block makes it easier (you
don't have to type out the Workbook and Worksheet references each time) and
more efficient.

Regards,

Jake Marx
MS MVP - Excel