Cell Address Help!

[jjsauer]

How can you obtain the cell address of the cell being used in a "For Each Cell in Range("A1:A10") VBA statement (For Next code)?

 

[vaskov17]

cell.address

 

[Jonmo1]

Try

For Each Cell in Range("A1:A10")
    Msgbox Cell.Address
Next Cell

 

[jjsauer]

How do I use cell.address?

I want to a.) us that to offset. Like cell.address.offset(0,1) = ABC
and b.) use that exact address on a different sheet, so if the cell happened to be A5, I want to set up in my macro to select A5 on sheet 2 for example.

Thanks!

 

[vaskov17]

cell.offset(0,1)=ABC

 

[jjsauer]

Thanks for the help, but I cannot figure this out...the line in red is where I am stuck

There is much more code, but this is the part that doesn't work. I have tried every variation I could think of.

For Each rCell In Range(Range1a, Range1b)
If rCell = "" Then
Else
With Workbooks("FY12-Q2 Data Tables.xlsx").Sheets("Total").Columns(1)
Set c = .Find("[" & rCell & "B" & "]", LookIn:=xlValues)
Set c = .Find(Cell.Offset(0, 1), After:=c)
Set c = .Find("Bottom 2 (NET)", After:=c)
End With
rCell.Offset(0, 14).Value = c.Offset(, 14).Value
rCell.Offset(0, 26).Value = c.Offset(, 3).Value
rCell.Offset(0, 27).Value = c.Offset(, 12).Value
rCell.Offset(0, 28).Value = c.Offset(, 15).Value

 

[Jonmo1]

so if the cell happened to be A5, I want to set up in my macro to select A5 on sheet 2 for example.

Thanks!

To offset the actual cell on the same sheet, do as Vaskov showed.

to use the same cell address on another sheet..do it this way

Sheets("Sheet2").Range(Cell.Address).Offset(0,1) = "ABC"

 

[jjsauer]

I cannot believe it...I got it to work, but I swear I have tried this before.....

Set c = .Find(rCell.Offset(0, 1), After:=c)

Thanks for all the help, I will certainly use your advice for the multiple sheets thing.