Simple Vlookup Looping

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hi Everyone,

Cany anyone help me with writing a simple code to run every worksheet
in my workbook and do a same vlookup?


i.e.


- I have 10 different worksheets in same format (same number of
columns and rows as well)
- Lookup value is always A5 in each sheet
- Lookup range comes from different excel file called "finance.xls",
"Summary" tab column A to B


I am just trying to do 'Vlookup($A$5,'finance'A:B,2,false) in column
B
starting from row 1 to row 2200 for every worksheet.


Please help


Thank you for your help in advance.


Regards


James
 
Make a copy of your workbook to test with!

I think this should do it for you. Open the (test) workbook, press
[Alt]+[F11] to open the VB Editor, and use Insert --> Module to get a code
module to use. Copy the code below and paste it into the code module. Close
the VB Editor and then use Tools --> Macro --> Macros to [Run] it. If it
works as it did for me, then you can save the test workbook back over the
original if you want to.

Sub MakeVlookupFormulas()
Dim anySheet As Worksheet
Dim formulaRange As Range
Dim myFormula As String

myFormula = _
"=IF(ISNA(VLOOKUP($A$5,finance!$A:$B,2,FALSE))," & _
Chr$(34) & Chr$(34) & _
",VLOOKUP($A$5,finance!$A:$B,2,FALSE))"
'if you want to show NO MATCH situations, use this
'statement instead
'myFormula = _
"=IF(ISNA(VLOOKUP($A$5,finance!$A:$B,2,FALSE))," & _
Chr$(34) & "NO MATCH" & Chr$(34) & _
",VLOOKUP($A$5,finance!$A:$B,2,FALSE))"

For Each anySheet In ThisWorkbook.Worksheets
If UCase(Trim(anySheet.Name)) <> "FINANCE" Then
'do this to any sheet not named like "finance"
Set formulaRange = anySheet.Range("B1:B2200")
formulaRange.Formula = myFormula
End If
Next
Set formulaRange = Nothing ' good housekeeping
MsgBox "All done now", vbOKOnly, "Task Completed"
End Sub
 
This a quick way to get 22,000 formulas into a workbook, which seems to me
unnecessary bloat and calculation time. It appears that each worksheet only
has one answer each from the VLookup of cell $A$5. Why not find the answer
once for each sheet and assign the value to B1:B2200?

Mike F
JLatham said:
Make a copy of your workbook to test with!

I think this should do it for you. Open the (test) workbook, press
[Alt]+[F11] to open the VB Editor, and use Insert --> Module to get a code
module to use. Copy the code below and paste it into the code module.
Close
the VB Editor and then use Tools --> Macro --> Macros to [Run] it. If it
works as it did for me, then you can save the test workbook back over the
original if you want to.

Sub MakeVlookupFormulas()
Dim anySheet As Worksheet
Dim formulaRange As Range
Dim myFormula As String

myFormula = _
"=IF(ISNA(VLOOKUP($A$5,finance!$A:$B,2,FALSE))," & _
Chr$(34) & Chr$(34) & _
",VLOOKUP($A$5,finance!$A:$B,2,FALSE))"
'if you want to show NO MATCH situations, use this
'statement instead
'myFormula = _
"=IF(ISNA(VLOOKUP($A$5,finance!$A:$B,2,FALSE))," & _
Chr$(34) & "NO MATCH" & Chr$(34) & _
",VLOOKUP($A$5,finance!$A:$B,2,FALSE))"

For Each anySheet In ThisWorkbook.Worksheets
If UCase(Trim(anySheet.Name)) <> "FINANCE" Then
'do this to any sheet not named like "finance"
Set formulaRange = anySheet.Range("B1:B2200")
formulaRange.Formula = myFormula
End If
Next
Set formulaRange = Nothing ' good housekeeping
MsgBox "All done now", vbOKOnly, "Task Completed"
End Sub

James said:
Hi Everyone,

Cany anyone help me with writing a simple code to run every worksheet
in my workbook and do a same vlookup?


i.e.


- I have 10 different worksheets in same format (same number of
columns and rows as well)
- Lookup value is always A5 in each sheet
- Lookup range comes from different excel file called "finance.xls",
"Summary" tab column A to B


I am just trying to do 'Vlookup($A$5,'finance'A:B,2,false) in column
B
starting from row 1 to row 2200 for every worksheet.


Please help


Thank you for your help in advance.


Regards


James
.
Click to expand...
Click to expand...
 
Back
Top