Filter function; copy values to another sheet

[Gert-Jan]

Hi,

I'm looking for a way to do the following:

I have a sheet with (>100) rows; in colom A there are values that are not
unique. The word "apple" kan appear three times, for example. In colom B
there are also values, as much as there are in colom A.
What I want is the following: a macro that searches for the word (for
example) "apple" and copies the values from the row in colom B to another
sheet, range D1.

Any help would be appriciated.

[Tom Ogilvy]

why not use the built in Pivot Table capability found under the Data menu.

It will do what you want.

--
Regards,
Tom Ogilvy



"Gert-Jan" wrote:

> Hi,
>
> I'm looking for a way to do the following:
>
> I have a sheet with (>100) rows; in colom A there are values that are not
> unique. The word "apple" kan appear three times, for example. In colom B
> there are also values, as much as there are in colom A.
> What I want is the following: a macro that searches for the word (for
> example) "apple" and copies the values from the row in colom B to another
> sheet, range D1.
>
> Any help would be appriciated.
>
>
>

[jgmiddel]

I know that function, Tom. That is not a solution. I have to use the data in a VBA userform. The start should be something like this (I guess):

With Sheets("Sheet1")
Dim i As Long, sTargetValue As String
sTargetValue = Range("Sheet2!F1")
For i = 1000 To 1 Step -1
If Cells(i, "B").Text = sTargetValue Then
....

[Tom Ogilvy]

I guess when you said:

>and copies the values from the row in colom B to another
>sheet, range D1.

I missed the term Userform.

dim v() As Variant
dim rng as Range
Dim sAddr as String
redim v(0 to 0)
with Worksheets("Sheet1").columns(1)
set rng = .find("apples")
if not rng is nothing then
sAddr = rng.Address
do
v(ubound(v)) = rng.offset(0,1)
redim preserve v(0 to ubound(v) + 1)
set rng = .FindNext(rng)
Loop while rng.address <> sAddr
Redim preserve v(0 to ubound(v)-1)
Userform1.ListBox1.List = v
end if



--
Regards,
Tom Ogilvy



"jgmiddel" wrote:

>
> I know that function, Tom. That is not a solution. I have to use the
> data in a VBA userform.
>
>
> --
> jgmiddel
> ------------------------------------------------------------------------
> jgmiddel's Profile: http://www.excelforum.com/member.php...o&userid=32714
> View this thread: http://www.excelforum.com/showthread...hreadid=535590
>
>