• 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.

VLOOKUP function with 3 sheets

Daxy

New Member
Hi,

I have an Excel file with a three-sheet. In the first sheet I must apply VLOOKUP function twice in columns C and D.

Formula for column C is:

= VLOOKUP (A2 & "Count," Ulaz! A1: D10000, 2, FALSE)

Sheet "Ulaz" is a dynamic table where the preliminary counting how many PartNo entered for each PartNo.

Formula for column D is:

= VLOOKUP (A2 & "Total" Out! A1: D10000, 2, FALSE)

Sheet "Izlaz" is also a dynamic table where the preliminary sums Quantity for each PartNo.

When do VLOOKUP need to copy an existing file to a new grantee will specify the name.

How to automate this with the help of VBA.

Thank you.
 

Attachments

  • Pakeri-2016-05-20.xlsm
    53.1 KB · Views: 4
Hi:

Use the following code
Code:
Sub Button13_Click()
Application.ScreenUpdating = False

Dim Table1 As Range, Table2 As Range, Table3 As Range

c& = 2
Set Table1 = Sheet3.Range("PS_PartNo") 'PartNo ID kolona za prvi argument u VLOOKUP
Set Table2 = Sheet1.Range("A2:D15000")  ' Tabela ULAZ
Set Table3 = Sheet2.Range("Izlaz_Table") ' Tabela IZLAZ

For Each cl In Table1
   On Error Resume Next
   Sheet3.Cells(c, 3) = Application.WorksheetFunction.VLookup(cl & " Count", Table2, 2, False)
   On Error Resume Next
   Sheet3.Cells(c, 4) = Application.WorksheetFunction.VLookup(cl & " Total", Table3, 2, False)
   c = c + 1
Next cl

MsgBox "Done"

Application.ScreenUpdating = True
End Sub
 

Attachments

  • Pakeri-2016-05-20.xlsm
    49.5 KB · Views: 6
Hi Nebu,

thanks a lot for helping. I tried this code but something is wrong with range in Table2. Here is printscreen of that error. Can You help me with this, please.
 

Attachments

  • Range error.png
    Range error.png
    57 KB · Views: 3
Hi:

You have changed my code
I have given in my code sheet1 , sheet3 etc, but you have changed it your sheet name if you want to change it to sheet name the code should be Sheets("yoursheetname")

Thanks
 
Hi,

thanks again for helping. I tried with Your code (just as it is written) but I still have error like in printscreen. Thanks again.
 

Attachments

  • Range error.png
    Range error.png
    92.4 KB · Views: 3
Hi:

Again you have not used my exact code, each line we code have a reason, you are missing this line in your code

OnErrorResumeNext

Thanks
 
Back
Top