Get address of found range to use in a formula

[keri]

Hi,

I want my VB to find a value, and then put the left 3 characters of
the value into another cell. However all I am achieving is putting the
formula

=left((rngfound.address),3)

into a cell (which obviously causes an error) as I do not know how to
"retrieve" the cell address (eg C4) from the rngFound in my code.

My code;

Set rngToSearch =
ActiveSheet.Range("12:12,15:15,18:18,21:21,24:24").EntireRow
For Each cel In rngToSearch
Set rngFound = rngToSearch.find("G*KT")
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
Set rngcopyto = rngFound.Offset(2, 4)
rngcopyto.Formula = "=LEFT((RNgFOUND.ADDRESS),3)"
Set rngFound = rngToSearch.FindNext(rngFound)
Loop While Not rngFound Is Nothing And rngFound.Address <>
strFirstAddress
End If
Next cel

 

[Guest]

Hi Keri,

Try it this way:

rngcopyto.Formula = "=LEFT(" & RNgFOUND.ADDRESS & ", 3)"