Simple Vlookup Looping

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
 
J

JLatham

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
 
M

Mike Fogleman

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
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup Loop 1
Vlookup Function 1
Simple Vlookup Loop 0
Document Merge Formatting Issue 0
Vlookup error 5
Auto Merge text from multiple sources 0
Copy hyperlinks 6
Simple Macro to Auto fill Cells 1

Top