Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get address of found range to use in a formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get address of found range to use in a formula
Hi Keri,
Try it this way: rngcopyto.Formula = "=LEFT(" & RNgFOUND.ADDRESS & ", 3)" -- Hope that helps. Vergel Adriano "keri" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the contents of a ADDRESS formula as the cell range in a for | Excel Discussion (Misc queries) | |||
using the address properties from range objects in a sum formula | Excel Discussion (Misc queries) | |||
How to get the address of the cell found our by vlookup | Excel Programming | |||
Find text String and select cell address where it is found? | Excel Programming | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |