VBA Macro, Next not working

Closed
journey4life - Sep 16, 2011 at 03:02 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 16, 2011 at 09:06 AM
Hello, I would really appreciate some help please.

I am attempting to
- copy an entire row of data from sheet 1 to a new blank row in sheet 2
- when the data in Cell A of the source row in sheet 1 matches a list on sheet 3

This is my code:

Option Explicit
Sub FruitFinder()
Dim srchLen, fName, nxtRow
Dim f As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column A, copy it top the next row in Sheet2
With Sheets(1).Columns("A")
For fName = 2 To srchLen
Set f = .Find(Sheets(3).Range("A" & fName))
If Not f Is Nothing Then
nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
f.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRow)
End If
Next
End With
End Sub

Sheet 1 Data:
Fruit Quantity Price Farmer
Apples 8 $2.00 ABC
Bananas 3 $0.50 ABC
Cherries 5 $5.00 DEF
Grapes 12 $4.00 GHI
Pineapples 2 $5.00 DEF
Apples 15 $2.00 ABC
Plums 5 $3.00 JKL
Raspberries 8 $4.00 MNO
Strawberries 20 $1.00 DEF
Apples 15 $2.00 ABC
Bananas 12 $0.50 ABC

Sheet 2: Blank

Sheet 3: Dynamic List (sample below)
Fruit
Apples
Bananas
Grapes
Oranges
Raspberries

Currently when I run the macro it only gives me the first instance of each fruit, instead of giving me all instances of the desired fruit.

Outcome from sample above - Sheet 2 is populated with the following:
Fruit Quantity Price Farmer
Apples 8 $2.00 ABC
Bananas 3 $0.50 ABC
Grapes 12 $4.00 GHI
Raspberries 8 $4.00 MNO


My 'next' does not appear to be working. Desired outcome is that it lists all rows with fruit that matches the list on sheet 3 (I should have 3 rows of Apples, 2 rows of Bananas, 1 row or Raspberries, 1 row of Grapes). I would really appreciate if someone could help me fix this code, I've been struggling with it and research resources online for hours.

Thank you in advance for your time and consideration (need some sleep now, fresh eyes in the morning).
Wanda

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Sep 16, 2011 at 09:06 AM
You are not using FindNext. You are trying to execute search again. See the solution by Venkat here how to use find and findnext
https://ccm.net/forum/affich-613479-search-a-word-in-the-column#1

You can see in that how to use "FindNext".
0