• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vba code for vlookup function to multiple sheets

Dabba

New Member
Hi Chandoo,

i need an help with vba code for vlookup to multiple sheets.

Please find the below vba code which i have applied for 1 sheet & i need to apply the same to code to multiple sheets with output to different ranges.

For Eg: Column A:A i have multiple containor Id's & i need to put vlookup for 4 sheets which have containor id's in column M:M & out put of the vlookup has to appear in column B:B, C:C, D:D respectivelly untill column A:A is blank.

Kindly help with this.
Code:
Sub Addf()
Application.ScreenUpdating = False
Mano = ActiveWorkbook.Name
ActiveSheet.Next.Select
A = ActiveWorkbook.Name
Workbooks(Mano).Activate
Range("A2").Select
Workbooks(A).Activate
Cells.Select
Range("M1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set Manoj = Selection
ActiveSheet.Previous.Select

Range("B2").Select




C = 2

Do Until Cells(C, 1).Value = ""
TOID = Cells(C, 1).Value
If IsError(Application.VLookup(TOID, Manoj, 1, 0)) Then
D = "N/A"
Else
D = Application.WorksheetFunction.VLookup(TOID, Manoj, 1, 0)
Cells(C, 2).Value = D

End If

C = C + 1
Loop


Cells.Select
Columns.AutoFit
Application.ScreenUpdating = True
Range("D1").Select
MsgBox ("Request Done")

End Sub

----------------------------------------------------------------------------
Mod Edit: Code Tags added!
 

Attachments

  • Container Stauts Analysis_HYD.xlsx
    292.7 KB · Views: 44
Last edited by a moderator:
Hi !

You should crystal clear explain columns of which worksheet …

And where code is located ?
 
Hi,

Worksheet "Master" is the data we need to put vlookup for the different sheets.

For Eg: Column A:A have multiple containor ID's & some of the ID's are appeared in different sheets under column M:M if those ID's are not appeared then macro should fill the range with N/A for the respective containor id's.

The data which we extract from different sheet should come under column B:B for sheet name 30.01.2016 & C:C for 29.01.2016 & D:D for 28.01.2016. So vlookup function should work untill worksheet is blank.

Hope I am clear now.

Kindly request you to help me out this.

Thanks,
Manoj
 
Code:
Sub Addf()
Application.ScreenUpdating = False
Mano = ActiveWorkbook.Name
ActiveSheet.Next.Select
A = ActiveWorkbook.Name
Workbooks(Mano).Activate
Range("A2").Select
Workbooks(A).Activate
Cells.Select
Range("M1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set Manoj = Selection
ActiveSheet.Previous.Select

Range("B2").Select




C = 2

DoUntil Cells(C, 1).Value = ""
TOID = Cells(C, 1).Value
If IsError(Application.VLookup(TOID, Manoj, 1, 0)) Then
D = "N/A"
Else
D = Application.WorksheetFunction.VLookup(TOID, Manoj, 1, 0)
Cells(C, 2).Value = D

EndIf
 
Last edited by a moderator:

And where is your actual code ? (which workbook and module)

As it can't be in your xlsx attached file …
 
Please find the attached file.
Master sheet is the working sheet
 

Attachments

  • Container Stauts Analysis_HYD.xlsm
    296.2 KB · Views: 56
Try this demonstration :​
Code:
Sub DemoA()
    Application.ScreenUpdating = False
    R& = Sheet1.UsedRange.Rows.Count - 1
For C& = 2 To Worksheets.Count
    With Sheet1.Cells(2, C).Resize(R)
        .Formula = "=VLOOKUP($A2," & Worksheets(C).Cells(1).CurrentRegion.Columns(13).Address(, , , True) & ",1,FALSE)"
        .Formula = .Value
    End With
Next
    Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Macrc, Its working.

I need few more addition in the macro.

1. After the lookup function is completed i need the count of numbers immediate next column.
Formula=COUNTIF(B2:E2,"*")= No. of days

2. Need to preapre Pivot table with below deatils.

Location ID: Row label
No.of Days: Column label
Container ID(Master): Values (Count)


Pease do the needful.

Thanks in advance.
 

I do not visualize your need but as Pivot is not my cup of tea,
I let other to help you …
 
Please find the attached file where i posted the formula (Column E) for your reference.

this formula should work after the lookup has been completed.
 
Hi Marc,

I have some concerns on the above codes.

I am not able to use the codes to different workbooks.

Can you please help me on this.

Thanks,
Manoj
 
Hi Marc,

I wanted to use the same codes to mutiple workbook without saving the file macro enabled.

If i run macro a should work for active work book, since the macro is working only for macro enabled workbook.

Since i need to run the macro for multiple files, please suggest me how to run the macro to multiple file just by opening the macro enabled file.

Please help me out this.

Hope it is cleat now.

Plesae do the needful.

Thanks,
Manoj
 
A code must be run from a macro enable workbook
and can alter any workbook, opened like closed, not a concern …

Maybe you should use GetOpenFilename method (just see VBA inner help)
to select some files and work with them via a For Each loop …
 
Back
Top