Struggling with Loops

kevinh2320

New Member
Joined
May 13, 2016
Messages
30
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>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,780
Office Version
365
Platform
Windows
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
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
837
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,780
Office Version
365
Platform
Windows
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.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
837
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.
 

Forum statistics

Threads
1,078,137
Messages
5,338,458
Members
399,234
Latest member
WaddoAU

Some videos you may like

This Week's Hot Topics

Top