How to get ActiveCell's address?

[Milind]

Hello
How to get activecell's address? I need to offset it to (10,0) and paste it.

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
Any ideas
Milind

 

[Norman Jones]

Hi Milind,

How to get activecell's address? I need to offset it to (10,0)

Try:

Activecell.Offset(10)

 

[Simon Murphy]

Milind
activecell.offset(10,0).value = activecell.value
or you may mean
c.offset(10,0).value = c.value
(Assumes you just want the value not any formatting or formulas - otherwise
use:
ActiveCell.Copy ActiveCell.Offset(10, 0)
)
cheers
Simon

 

[Jim Thomlinson]

depending on what you want to do there are a couple of options. Often when I
want to interact with the activecell I will set a range object equal to the
active cell. I can move that object around to my little hearts content
without worrying about any consequences to the active cell. Something like
this

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

set rngCurrent = Activecell.offset(10, 0)

For Each c In Worksheets("Sheet1").Rows("5:5").Cells
s_text = c.Value
If InStr(1, s_text, "India", vbTextCompare) > 0 Then
c.Copy rngCurrent
set rngcurrent = rngcurrent.offset(1,0) 'If you like