Repeat: Active Cell's Address

[Milind]

Hello All
Your answers are right, but I think I could not put it right.
How to get the Activecell's address? Then how to use it in conjunction with
Range, Address etc.? Also how can I use the MsgBox to show it so that I know
the program flow is correct? Also, when I say
For each c in Worksheets("sheet1").Rows("5:5").Cells
if c.value = 50 then
....
what should I write in for c.range, c.address ? [I was wondering when
c.value works why not c.range or c.address?]
Thanks
Milind

 

[George Nicholson]

c is not the ActiveCell. You would have to c.Activate c to be the
ActiveCell. Acivate is rarely necessary and ruins performance, so avoid it.

Address of ActiveCell = ActiveCell.Address
Address of c (within your loop) = c.Address
(why do you say c.address doesn't work? Keep in mind that Address returns
"$A$1", not "A1" if you are doing a string comparison)

HTH,

 

[Tom Ogilvy]

You don't actually need the address and c is a range reference, so you don't
need c.range

Sub String_Find_and_Paste()
Dim s_text As String
Dim r As Range
Dim s As Range

For Each c In Worksheets("Sheet1").Rows("5:5").Cells
s_text = c.Value
If InStr(1, s_text, "India", vbTextCompare) > 0 Then
'Get the activeCell's address, offset it & paste
cell.copy ActiveCell.Offset(10,0)
ActiveCell.Offset(10,0).Select
msgbox ActiveCell.Address
end if
Next
End Sub