Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping thru Cell References
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stepping thru Cell References
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 "Ralph" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stepping in to a cell | Excel Discussion (Misc queries) | |||
Stepping down a cell when a value is entered | Excel Discussion (Misc queries) | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Stepping through Code | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) |