Struggling with Loops


New Member
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>

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

Sheet2 Example
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
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>


MrExcel MVP, Moderator
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.

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


Well-known Member
Hello Kevin,

Here's another option:-
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
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.



MrExcel MVP, Moderator
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.


Well-known Member
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.


Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...