Struggling with Loops

kevinh2320 · Sep 30, 2019

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

Peter_SSs · Sep 30, 2019

kevinh2320 said:
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 · Sep 30, 2019

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 · Sep 30, 2019

vcoolio said:
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 · Sep 30, 2019

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.

kevinh2320 · Sep 30, 2019

Thank you Peter that worked perfectly!

Peter_SSs · Oct 1, 2019

kevinh2320 said:
Thank you Peter that worked perfectly!

You're welcome.

Struggling with Loops

kevinh2320

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

vcoolio

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

vcoolio

Well-known Member

kevinh2320

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Struggling with Loops

kevinh2320

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

vcoolio

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

vcoolio

Well-known Member

kevinh2320

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock