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

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
>