How MsgBox can show address?

[Milind]

Hi
For each c in worksheets("sheet1").Rows("3:3").Cells
if c.value = 50 then
....
MsgBox c.address
(I need this "c" address. When I use ActiveCell.Offset(5), to paste the
value, the result is 30 rows, then 35 rows ... I want to see what address
the program is referring to. When comparison is being done (c.value = 50)
how to get this cell's address and how to put it for others including
MsgBox. Please help
Milind

 

[Rowan Drummond]

When you loop through the cells in a range in this manner the activecell
does not change (unless you have explicitly activate each cell - which
you probably don't want to do). Select cell A1 then run this as an
example (you should have at least one cell in row 3 with a value of 50).

Sub Test()
Dim c As Range
For Each c In Rows(3).Cells
If c.Value = 50 Then
MsgBox "Activecell is " & ActiveCell.Address & Chr(10) _
& "Address of c is " & c.Address
End If
Next c
End Sub

Hope this helps
Rowan