Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is is possible to have a macro select a cell and return the address for use as a variable?
I need to run a macro that will search a range for a value. (I can do
that) and then return the cell within the range that the value appears in. I don't know how to do that. Then, I need to be able to use the letter and number for the cell as a value. Is this possible? It's going to search a range for a non blank value. When it finds it, based on that cell, it's going to search another range and delete that value in the new range. It will search and delete a different range based on the exact position of the cell where it finds the value. TIA I'm learning quick. Thanks for all your helps guys. JasonK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is is possible to have a macro select a cell and return the addres
One way to find things is to use the vba equivalent of the Find command under
the Edit menu. If that appears appropriate then it would be something like this: set rng = columns(1).Find("ABCD") if not rng is nothing then msgbox "abcd found at " & rng.Address else msgbox "abcd not found" End if The find command has some persistent arguments, so it is best to set them all when you use it. I have just used one for illustration. See the help on the find command or easiest is to turn on the macro recorder and do a find manually. Then turn off the macro recorder and view the code. Then modify the code so it operates as I have above - rather than activating the found cell. I also illustrated how you can use the address function to see the address, however, usually what is more useful is rng.row rng.column to get the row number and column number. In the first part of your description it sounds like you want to actually loop through a known range, then react to the values you find there. for each cell in Range("A1:A20") if not isempty(cell) then set rng = columns(5).Find(cell) if not rng is nothing then msgbox cell.value & " found at " & rng.address else msgbox cell.value & " not found" end if end if Next -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "JasonK" wrote: I need to run a macro that will search a range for a value. (I can do that) and then return the cell within the range that the value appears in. I don't know how to do that. Then, I need to be able to use the letter and number for the cell as a value. Is this possible? It's going to search a range for a non blank value. When it finds it, based on that cell, it's going to search another range and delete that value in the new range. It will search and delete a different range based on the exact position of the cell where it finds the value. TIA I'm learning quick. Thanks for all your helps guys. JasonK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
select range and put range address in variable | Excel Programming | |||
How can I use a variable as cell address? | Excel Programming | |||
Select Cell based on Variable address | Excel Programming | |||
Need VBA macro to return active cell address (R1C1) | Excel Programming |