Hello,
I have a source worksheet (SourceWs) with data I am trying to populate on a second tab (BanksWs), using a unique identifier common to both tabs (colums S and A. I am able to create multiple index match subs, but I would like these to operate one after the other without being separated by end prompts. Is there an easy way to do this with the current structure? Full disclosure, I copied this code from a youtube video, then replicated it for each column I needed the function. Many thanks in advance for any help!!
Excel VBA - INDEX and MATCH Functions in Another Sheet - Bing video
I have a source worksheet (SourceWs) with data I am trying to populate on a second tab (BanksWs), using a unique identifier common to both tabs (colums S and A. I am able to create multiple index match subs, but I would like these to operate one after the other without being separated by end prompts. Is there an easy way to do this with the current structure? Full disclosure, I copied this code from a youtube video, then replicated it for each column I needed the function. Many thanks in advance for any help!!
Excel VBA - INDEX and MATCH Functions in Another Sheet - Bing video
Sub IndexMatchBanksName() |
Dim BanksWs As Worksheet, SourceWs As Worksheet |
Dim BanksLastRow As Long, SourceLastRow As Long, x As Long |
Dim IndexRng As Range, MatchRng As Range |
Set BanksWs = ThisWorkbook.Worksheets("Banks") |
Set SourceWs = ThisWorkbook.Worksheets("Regions, LEs, Bank Accts, Scope") |
BanksLastRow = BanksWs.Range("A" & Rows.Count).End(xlUp).Row |
SourceLastRow = SourceWs.Range("S" & Rows.Count).End(xlUp).Row |
Set IndexRng = SourceWs.Range("J3:J" & SourceLastRow) |
'Set MatchRng = SourceWs.Range("S3:S" & SourceLastRow) |
Set MatchRng = IndexRng.Offset(0, 9) |
For x = 2 To BanksLastRow |
On Error Resume Next |
BanksWs.Range("F" & x).Value = Application.WorksheetFunction.Index( _ |
IndexRng, _ |
Application.WorksheetFunction.Match(BanksWs.Range("A" & x).Value, MatchRng, 0)) |
Next x |
End Sub |
Sub IndexMatchBanksCode() |
Dim BanksWs As Worksheet, SourceWs As Worksheet |
Dim BanksLastRow As Long, SourceLastRow As Long, x As Long |
Dim IndexRng As Range, MatchRng As Range |
Set BanksWs = ThisWorkbook.Worksheets("Banks") |
Set SourceWs = ThisWorkbook.Worksheets("Regions, LEs, Bank Accts, Scope") |
BanksLastRow = BanksWs.Range("A" & Rows.Count).End(xlUp).Row |
SourceLastRow = SourceWs.Range("S" & Rows.Count).End(xlUp).Row |
Set IndexRng = SourceWs.Range("Q3:Q" & SourceLastRow) |
'Set MatchRng = SourceWs.Range("S3:S" & SourceLastRow) |
Set MatchRng = IndexRng.Offset(0, 2) |
For x = 2 To BanksLastRow |
On Error Resume Next |
BanksWs.Range("D" & x).Value = Application.WorksheetFunction.Index( _ |
IndexRng, _ |
Application.WorksheetFunction.Match(BanksWs.Range("A" & x).Value, MatchRng, 0)) |
Next x |
End Sub |
Sub IndexMatchBanksBranchCode() |
Dim BanksWs As Worksheet, SourceWs As Worksheet |
Dim BanksLastRow As Long, SourceLastRow As Long, x As Long |
Dim IndexRng As Range, MatchRng As Range |
Set BanksWs = ThisWorkbook.Worksheets("Banks") |
Set SourceWs = ThisWorkbook.Worksheets("Regions, LEs, Bank Accts, Scope") |
BanksLastRow = BanksWs.Range("A" & Rows.Count).End(xlUp).Row |
SourceLastRow = SourceWs.Range("S" & Rows.Count).End(xlUp).Row |
Set IndexRng = SourceWs.Range("R3:R" & SourceLastRow) |
'Set MatchRng = SourceWs.Range("S3:S" & SourceLastRow) |
Set MatchRng = IndexRng.Offset(0, 1) |
For x = 2 To BanksLastRow |
On Error Resume Next |
BanksWs.Range("E" & x).Value = Application.WorksheetFunction.Index( _ |
IndexRng, _ |
Application.WorksheetFunction.Match(BanksWs.Range("A" & x).Value, MatchRng, 0)) |
Next x |
End Sub |
Sub IndexMatchBanksCity() |
Dim BanksWs As Worksheet, SourceWs As Worksheet |
Dim BanksLastRow As Long, SourceLastRow As Long, x As Long |
Dim IndexRng As Range, MatchRng As Range |
Set BanksWs = ThisWorkbook.Worksheets("Banks") |
Set SourceWs = ThisWorkbook.Worksheets("Regions, LEs, Bank Accts, Scope") |
BanksLastRow = BanksWs.Range("A" & Rows.Count).End(xlUp).Row |
SourceLastRow = SourceWs.Range("S" & Rows.Count).End(xlUp).Row |
Set IndexRng = SourceWs.Range("N3:N" & SourceLastRow) |
'Set MatchRng = SourceWs.Range("S3:S" & SourceLastRow) |
Set MatchRng = IndexRng.Offset(0, 5) |
For x = 2 To BanksLastRow |
On Error Resume Next |
BanksWs.Range("G" & x).Value = Application.WorksheetFunction.Index( _ |
IndexRng, _ |
Application.WorksheetFunction.Match(BanksWs.Range("A" & x).Value, MatchRng, 0)) |
Next x |
End Sub |
Sub IndexMatchBanksCountry() |
Dim BanksWs As Worksheet, SourceWs As Worksheet |
Dim BanksLastRow As Long, SourceLastRow As Long, x As Long |
Dim IndexRng As Range, MatchRng As Range |
Set BanksWs = ThisWorkbook.Worksheets("Banks") |
Set SourceWs = ThisWorkbook.Worksheets("Regions, LEs, Bank Accts, Scope") |
BanksLastRow = BanksWs.Range("A" & Rows.Count).End(xlUp).Row |
SourceLastRow = SourceWs.Range("S" & Rows.Count).End(xlUp).Row |
Set IndexRng = SourceWs.Range("L3:L" & SourceLastRow) |
'Set MatchRng = SourceWs.Range("S3:S" & SourceLastRow) |
Set MatchRng = IndexRng.Offset(0, 7) |
For x = 2 To BanksLastRow |
On Error Resume Next |
BanksWs.Range("H" & x).Value = Application.WorksheetFunction.Index( _ |
IndexRng, _ |
Application.WorksheetFunction.Match(BanksWs.Range("A" & x).Value, MatchRng, 0)) |
Next x |
End Sub |
Sub IndexMatchBanksAddress() |
Dim BanksWs As Worksheet, SourceWs As Worksheet |
Dim BanksLastRow As Long, SourceLastRow As Long, x As Long |
Dim IndexRng As Range, MatchRng As Range |
Set BanksWs = ThisWorkbook.Worksheets("Banks") |
Set SourceWs = ThisWorkbook.Worksheets("Regions, LEs, Bank Accts, Scope") |
BanksLastRow = BanksWs.Range("A" & Rows.Count).End(xlUp).Row |
SourceLastRow = SourceWs.Range("S" & Rows.Count).End(xlUp).Row |
Set IndexRng = SourceWs.Range("O3:O" & SourceLastRow) |
'Set MatchRng = SourceWs.Range("S3:S" & SourceLastRow) |
Set MatchRng = IndexRng.Offset(0, 4) |
For x = 2 To BanksLastRow |
On Error Resume Next |
BanksWs.Range("I" & x).Value = Application.WorksheetFunction.Index( _ |
IndexRng, _ |
Application.WorksheetFunction.Match(BanksWs.Range("A" & x).Value, MatchRng, 0)) |
Next x |
End Sub |
Sub IndexMatchBanksPostalCode() |
Dim BanksWs As Worksheet, SourceWs As Worksheet |
Dim BanksLastRow As Long, SourceLastRow As Long, x As Long |
Dim IndexRng As Range, MatchRng As Range |
Set BanksWs = ThisWorkbook.Worksheets("Banks") |
Set SourceWs = ThisWorkbook.Worksheets("Regions, LEs, Bank Accts, Scope") |
BanksLastRow = BanksWs.Range("A" & Rows.Count).End(xlUp).Row |
SourceLastRow = SourceWs.Range("S" & Rows.Count).End(xlUp).Row |
Set IndexRng = SourceWs.Range("P3:P" & SourceLastRow) |
'Set MatchRng = SourceWs.Range("S3:S" & SourceLastRow) |
Set MatchRng = IndexRng.Offset(0, 3) |
For x = 2 To BanksLastRow |
On Error Resume Next |
BanksWs.Range("J" & x).Value = Application.WorksheetFunction.Index( _ |
IndexRng, _ |
Application.WorksheetFunction.Match(BanksWs.Range("A" & x).Value, MatchRng, 0)) |
Next x |
End Sub |