Looping Question?

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

How to loop through the range B:H from row 5 until the end?
I like to have the value show up in the message board like current row
no = 5 current cell = 5b current value = 8, somethings like this.

Hopefully I can get the answer for the above question.

Thanks.
 
Sub Tester1()
For Each rw In Range(Cells(5, 2), _
Cells(Rows.Count, 2).End(xlUp))
For Each cell In rw.Resize(1, 7)
Application.StatusBar = "Current Row No = " & rw.Row _
& " Current Cell = " & cell.Address(0, 0) & _
" Current Value = " & cell.Value
For i = 1 To 1000000
dblval = dblval + 1
Next
Next
Next
Application.StatusBar = False
End Sub


the loop For i = 1 to 1000000
represents the processing you would do on each cell. Right now, it just
slows the macro down, otherwise, it runs so fast you can't see the results.
So, you would replace that portion with the processing you are performing.
 
the following procedure gives a simple demo...

Sub demo()

Dim rSource As Range ' the area to be "viewed"
Dim rCell As Range ' for looping through the view
Dim msg As String ' body of the message
Dim title As String 'tile of the message

Set rSource = Range("D5:H15")

For Each rCell In rSource

' initialise message variables
title = "row: %R"
msg = "Address: %A " & Chr(10) & "Value: %V"

' populate message variables
title = Replace(title, "%R", rCell.Row)
msg = Replace(msg, "%A", rCell.Address)
msg = Replace(msg, "%V", rCell.Value)

' show message & test for user cancel
If MsgBox(msg, vbOKCancel, title) = _
vbCancel Then
Exit For
End If

Next


End Sub

Patrick Molloy
Microsoft Excel MVP
 
Back
Top