I am trying to create a vba routine to read a text string in a particular
cell, then search for that text string in a range of cells and then copy and
paste another range into a location starting one cell to the right of the
found text string. I also need to do the reverse in retrieving the data
previously pasted, into the location of the original range. Basically like a
database operation of fetching and updating records. I can't use a lookup on
the pasted data because I have to modify it and then update the 'database'. I
have been trying doing this with a €˜find procedure but it seems like the
find will not get the search string from a referenced cell, only data
programmed in the code. By the way, the search string is coming from
selection from a validation drop down list placed in a particular cell. Does
anyone have any good ideas how to do this?
Thanks in advance,
RD Wirr

Worksheets("Sheet1").Activate
set rng = range("B1:B30").find(What:=Range("J10").value, _
After:=Range("B30"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

if not rng is nothing then
worksheets("Sheet2").Range("F11").copy rng.offset(0,1)
else
msgbox "Target not found"
End if


should work fine. So your premise that it won't use a value stored in a
range is flawed.

--
Regards,
Tom Ogilvy



"RD Wirr" wrote in message
...
I am trying to create a vba routine to read a text string in a particular
cell, then search for that text string in a range of cells and then copy
and
paste another range into a location starting one cell to the right of the
found text string. I also need to do the reverse in retrieving the data
previously pasted, into the location of the original range. Basically like
a
database operation of fetching and updating records. I can't use a lookup
on
the pasted data because I have to modify it and then update the
'database'. I
have been trying doing this with a 'find' procedure but it seems like the
find will not get the search string from a referenced cell, only data
programmed in the code. By the way, the search string is coming from
selection from a validation drop down list placed in a particular cell.
Does
anyone have any good ideas how to do this?
Thanks in advance,
RD Wirr

That worked well Tom. Thanks.

RDW

"Tom Ogilvy" wrote:

Worksheets("Sheet1").Activate
set rng = range("B1:B30").find(What:=Range("J10").value, _
After:=Range("B30"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

if not rng is nothing then
worksheets("Sheet2").Range("F11").copy rng.offset(0,1)
else
msgbox "Target not found"
End if


should work fine. So your premise that it won't use a value stored in a
range is flawed.

--
Regards,
Tom Ogilvy



"RD Wirr" wrote in message
...
I am trying to create a vba routine to read a text string in a particular
cell, then search for that text string in a range of cells and then copy
and
paste another range into a location starting one cell to the right of the
found text string. I also need to do the reverse in retrieving the data
previously pasted, into the location of the original range. Basically like
a
database operation of fetching and updating records. I can't use a lookup
on
the pasted data because I have to modify it and then update the
'database'. I
have been trying doing this with a 'find' procedure but it seems like the
find will not get the search string from a referenced cell, only data
programmed in the code. By the way, the search string is coming from
selection from a validation drop down list placed in a particular cell.
Does
anyone have any good ideas how to do this?
Thanks in advance,
RD Wirr