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
--
AP


"JasonK" <JasonK@aol.com> a écrit dans le message de
news:8cvc121hiojjb6723c739oecu5hqjo8upl@4ax.com...
> 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

[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







On Tue, 14 Mar 2006 09:56:55 +0100, "Ardus Petus"
<ardus.petus@laposte.net> wrote:

>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

[Ardus Petus]

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

HTH
--
AP

"JasonK" <JasonK@aol.com> a écrit dans le message de
news:tphd121e5libeivtaubrsoo7ag32snrco3@4ax.com...
> 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
>
>
>
>
>
>
>
> On Tue, 14 Mar 2006 09:56:55 +0100, "Ardus Petus"
> <ardus.petus@laposte.net> wrote:
>
> >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]

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



On Tue, 14 Mar 2006 15:28:01 +0100, "Ardus Petus"
<ardus.petus@laposte.net> wrote:

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

[Diva]

JasonK wrote:
> 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
>
>
>
> On Tue, 14 Mar 2006 15:28:01 +0100, "Ardus Petus"
> <ardus.petus@laposte.net> wrote:
>
> >foud.offet(-5,0) references cell 5 rows above found
> >
> >HTH


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