pasting the result of a search

[JasonK]

TIA once again.


I have a macro that will search a wide range of cells to find a
particular piece of data.

when it finds that data, i need the macro to copy that data exactly 5
cells to the right of where it finds it.

i can find the data, copy the data, (so that the little ants are
running around the cell) but i do not know how to paste it to a
different location that is based upon the original location.

i tried to use "offset" but it seems like it requires a starting
range, and the starting range will be different each time the macro
selects a different piece of data. Can I use offset with the current
range? Do I need something else?

thanks again,
Jason K

 

[Ardus Petus]

dim found as range

set found = Range(A2:A100).Find("blah")

if not found is nothing then
found.Copy destination:=found.offset(0,5)
end if

HTH

 

[JasonK]

Ardus,
thanks for the help.

it's not working for me because some of the cells are merged.
i have the value named as a variable, and i have the value in the
second found cell. how do i tell the macro to just select the cell 5
cells over?

JasonK

 

[Ardus Petus]

foud.offet(-5,0) references cell 5 rows above found

HTH

 

[JasonK]

I'm dense I guess.

I don't just want to reference it, i need to select it.
i tried multiple lines using the work select and copy, and i can't get
it to just select the new cell and paste the clipboard or variable to
it.

i bought the for dummies book, but it has nothing about that. it talks
about offset, but it never mentions pasting a variable or clipboard to
the offset cell. it only mentions copying a specific range offset.
that doesn't work for me.

thanks again Ardus, i really appreciate your help.

JasonK

 

[Diva]

I'm dense I guess.

I don't just want to reference it, i need to select it.
i tried multiple lines using the work select and copy, and i can't get
it to just select the new cell and paste the clipboard or variable to
it.

i bought the for dummies book, but it has nothing about that. it talks
about offset, but it never mentions pasting a variable or clipboard to
the offset cell. it only mentions copying a specific range offset.
that doesn't work for me.

thanks again Ardus, i really appreciate your help.

JasonK

Use the following link
http://www.cpearson.com/excel/clipboar.htm