Struggling with Loops

kevinh2320

Board Regular
Joined
May 13, 2016
Messages
61
I have two worksheets in my workbook. Sheet1 contains customer order information with column A holding their account number. Sheet2 contains customers name & address information with column A also holding their account number. I need VBA code to start at cell A2 of sheet1 and check the account number. Then move to Sheet2's column A to see if there is a matching account number. If there is a match copy cells "B" through "F" of that row, then return to that accounts row in Sheet1 and paste the Company Name, Address, City, State, ZipCode in cells F through J of that row. Code needs to loop through the entire column A of sheet1 until complete. If there are account numbers that don't match code should just move to the next row until complete.

Hope this make sense. Example sheets 1 & 2 shown below. Thanks for any help.

Sheet1 Example
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Acct#Part#QtyUnitPriceTtl
12345654415$175.00$2,625.00
13398744335$100.00$3,500.00
23939543466$5.00$330.00
10210244433$55.00$1,815.00
2393955449$85.00$765.00
43320213294$35.00$3,290.00
103111444150$55.00$8,250.00
24436533350$100.00$5,000.00
41155544417$55.00$935.00
244987-A2331543$1.50$2,314.50

<colgroup><col style="width: 107px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

Sheet2 Example
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Acct#CompanyNameAddressCityStateZipCode
123456ABC Company123 Lost LnSeatteWA98555
102102Hawk Industries3600 W. 2nd AveKentWA98366
103111J&J Co.P.O. Box 10KirklandWA98111
133987Snip & ClipP.O. Box 25TacomaWA98232
244365Performance Max434 Anywhere PlSometownWA98765
239395Joe Joe's765 Where St.Anytown WA98767
239395Automax WestP.O. Box 15776Lost CityCA92888
244987-ATudle Row6434 Salem St.TacomaWA98232
433202Brewmiesters Cache101 Whiskey Pl.RedmondWA98664
411555J. Co.888 Maple St.TukwillaWA98334
388998BnB TradersP.O. Box 7AnchorageAK99510
325463CC Co.1243 Made Up Ln.SeatteWA98765
333321Music World555 5th Ave.EdmondsWA98768
383009Pets and Pamper4787 3rd St.KentWA98366
520020Industrial Supply Inc.88457 54th Ave.BallardWA98444
500011Recreational Adventures232 Easy St.SeatteWA98437
655837Zip Deliveries343 4th AveMt. VernonWA98876
299534-BGreen Thumb Landscaping400 Cherry Dr.LynnwoodWA98554

<colgroup><col style="width: 107px"><col width="163"><col width="132"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Code needs to loop through the entire column A of sheet1 until complete.
Is there a particular reason the code must use a loop procedure?
If not, you could try this with a copy of your workbook.

Code:
Sub Lookup_Data()
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim lr2 As Long
  
  Set ws1 = Sheets("Sheet1")
  Set ws2 = Sheets("Sheet2")
  lr2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
  With ws1.Range("F2:F" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row)
    .Formula = Replace(Replace("=IFERROR(INDEX('#'!B$2:B$%,MATCH($A2,'#'!$A$2:$A$%,0)),"""")", "#", ws2.Name), "%", lr2)
    .Copy Destination:=.Resize(, 5)
    .Resize(, 5).Value = .Resize(, 5).Value
  End With
End Sub
 
Upvote 0
Hello Kevin,

Here's another option:-
Code:
Sub Test()

Dim lr As Long
Dim fValue As Range, c As Range, x As Integer

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

For Each c In Sheet1.Range("A2:A" & lr)
Set fValue = Sheet2.Columns("A:A").Find(c.Value)
           If fValue Is Nothing Then GoTo NextC
           If c.Value = fValue.Value Then
           x = fValue.Row
           Sheet2.Range(("B" & x), ("F" & x)).Copy Sheet1.Range("F" & Rows.Count).End(3)(2)
     End If
NextC:
Next c

Application.ScreenUpdating = True

End Sub

I've noticed that in your sample data you have the same account number for two different customers which will affect both of the supplied codes above. You may want to check this.

I hope that this helps.

Cheerio,
vcoolio.
 
Upvote 0
I've noticed that in your sample data you have the same account number for two different customers
Good spotting, I hadn't noticed that.

My code will return the data from the first row in Sheet2 that meets the criteria. So for that repeated one it will return Joe Joe's on both occasions.
 
Upvote 0
Good afternoon Peter,

The same will happen with my code. I'm sure its just a typo on the OP's part.

I like your formula idea BTW.

Cheerio,
vcoolio.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top