PDA

View Full Version : [SOLVED] VLookup with VBA



Michael1974
03-26-2015, 04:16 AM
Hello,

I have the attached spreadsheet. My goal is to create a macro that will insert a column in Column A with a UniqueID that will be the concatenate operation between the item # and Location (column M).

Then I want this macro add up these unique IDs in column T. Finally, I want all of these operations to generate a lookup function that will show based on the UniqueID, info regarding the item # , OnHand and Location. Please note that we may have the same item # in different row that will show different OnHand Qty and locations.

I have already started to doing this but somehow there is something wrong with my code. Please let me know if you need further information.

Thanks in advance

Mike

Yongle
03-26-2015, 05:54 AM
Insert a column in Column A with a UniqueID - column T will then become U - is that what you want, or do you want to delete one of the current blank columns (P to S)?
Also is the concatenate item# followed by Location or Location followed by ItemNo?And do you want any separator?

Then I want this macro add up these unique IDs in column T. - what exactly is being added up here? I do not think that you want to add up unique IDs (an alphanumeric value), but that is what you have written

lookup function that will show based on the UniqueID, info regarding the item # , OnHand and Location - can you please explain what this means. If the UniqueID is unique then it will only have one item to lookup.

thanks

Michael1974
03-26-2015, 06:06 AM
- Yes I want Column T to become U

- I am adding a UniqueID which is basically the concatenation of the Item # and Location

- The UniqueID is needed because we have in our set of data some items with the same Item # but with different locations and different quantity.

With the UniqueID, I have to lookup Quantity on hand and location.

I hope that I was clear in my answers.

Thanks

Michael1974
03-26-2015, 06:10 AM
I have created the code below that produced an error message:"Run-Time error '1004' Unable to get the VLookup property of the worksheetFunction class". I would appreciate any help on this. Thanks


Sub ADDCLM()
Range("A1").EntireColumn.Insert
Range("U1").EntireColumn.Insert
Cells(1, 1) = "UniqueID"
Cells(1, 21) = Cells(1, 1)
Dim RowNum As Integer
RowNum = 2
Do Until IsEmpty(Cells(RowNum, 2))
Cells(RowNum, 1) = Cells(RowNum, 2) & "-" & Cells(RowNum, 14)
Cells(RowNum, 21) = Cells(RowNum, 1)
RowNum = RowNum + 1
Loop
Dim Dept_Row As Long
Dim Dept_Clm As Long
Table1 = Sheet1.Range("A2:A292") ' Employee_ID Column from Employee table
Table2 = Sheet1.Range("U2:X374") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("AA2").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("AA2").Column
For Each cl In Table1
Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup((cl), [Table2], 1, False)
Dept_Row = Dept_Row + 1
Next cl
MsgBox "Done"
End Sub

Yongle
03-26-2015, 06:41 AM
This may appear to be a strange question :confused::confused:
Does the macro in post#04 relate to the spreadsheet attached to post#01?
Sheet1 on the spreadsheet is nothing to do with employee tables

Most of my vlookup errors occur when a value cannot be found and that is probably the issue here. I cannot replicate due to above confusion

Michael1974
03-26-2015, 06:49 AM
Sorry about this. Don't pay attention to the comments. I have just found this macro and tried to apply it to the attached spreadsheet on post # 1. But yes this macro is related to the attached sheet.

Michael1974
03-26-2015, 07:50 AM
Yongle,

Any idea why this code does not work?

Thanks

Yongle
03-26-2015, 10:06 AM
Hi Mike
Yes - all down to your table ranges - but 2 problems
I have been distracted elsewhere - apologies for the delay. And (grrr!) your worksheet is protected so that tied me in handcuffs for a while - it does not always tell you that it is not letting you do things - they just don't happen!!
Anyway - to debug, I created a new sheet3 and copied the values there and so you will need to ignore references to that sheet.
I have only looked at what makes the vlookup not work, not at anything else. As I suspected, it could not find the values it was searching for, hence the error message.

You have a few things to think about:
If you keep inserting columns every time you run this, the code will keep going out of wack - hence the problem with the range for Table 2 now changed to ("T2:W374") ( I suspect you are already aware of this, because column Z had been correctly amended to AA between the 2 iterations of your code)
I generally use message boxes to help me debug and when I asked for the address of your 2 table ranges, the message box was blank.
Note the syntax when using ranges - the vlookup refused to work without the full address including "set". But vba had no problem with Dept_row and Dept_col using the shorter address - I tend to put in the full address, and it saves a lot of pain!

Rather than re-writing it for you, I will let you puzzle it out. The code was basically ok - just try not "shooting yourself in the foot" inserting columns without working out the impact on your code. VBA is not like Excel, it does not adjust to inserting columns and rows. If you tell it to put something in column B that is where it will put it next time, no matter how many columns you have inserted in the meantime. So you have to build dynamic references into the code to compensate.

Hopefully this will help you get sorted. If not, no doubt you will be in touch.
Yon


extract only

Set table1 = Worksheets("Sheet3").Range("A2:A292") '
Set table2 = Worksheets("Sheet3").Range("T2:W374") '


MsgBox table1.Address & " " & table2.Address


Dept_Row = Sheet3.Range("AA2").Row ' Change E3 with the cell from where you need to start populating the Department


Dept_Clm = Sheet3.Range("AA2").Column


For Each cl In table1


Worksheets("Sheet3").Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, table2, 3, False)
Dept_Row = Dept_Row + 1


Next cl
MsgBox "Done"

Yongle
03-26-2015, 10:18 AM
Oh I forgot to mention that I also had to change the column number to "3" inside vlookup to report the "On Hand" rather than the "Item#"

Worksheets("Sheet3").Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, table2, 3, False)

Michael1974
03-26-2015, 10:47 AM
Thank you Yongle. I will be working with this. I think this shoould fix the problem. I might need your help again soon. Because I have to retrieve 3 more columns using VLookup.