PDA

View Full Version : Solved: Vlookup you helped me with yesterday not working again!!!



itipu
06-17-2007, 11:21 PM
Gents, you helped me yesterday with Vlookup formula, unfortunately I can not seem to get it working any more... I attached a workbook again.

I have a sheet called Table in that workbook, and basically I need to get some values out of it using vlookup...

the issues are here...


'Get length of RecordSet
columnLength = 'Length of Column D...

'For Column A Get Sales Organization out of the Table
ActiveSheet.Range("A2:A$" & columnLength).Formula = _
"=IF(ISERROR(VLOOKUP(D1,Sheet7!$A:$B,2,FALSE)),""0"", VLOOKUP(D1,Sheet7!$A:$B,2,FALSE))"

'For Column E Get Plant out of the Table
ActiveSheet.Range("A2:A$" & columnLength).Formula = _
"=IF(ISERROR(VLOOKUP(D1,Sheet7!$A:$B,2,FALSE)),""0"", VLOOKUP(D1,Sheet7!$A:$B,2,FALSE))"

'For Column L Get Currency out of the Table
ActiveSheet.Range("A2:A$" & columnLength).Formula = _
"=IF(ISERROR(VLOOKUP(D1,Sheet7!$A:$B,2,FALSE)),""0"", VLOOKUP(D1,Sheet7!$A:$B,2,FALSE))"

Don't get anything... also can't seem to get columnLength to the length of Column D...

Sample of what I got is included...


I know my VBA skills are a nightmare.. but I am trying to learn :)

Thanks a lot again!!!!

Mike

rbrhodes
06-17-2007, 11:31 PM
VBA is locked....

itipu
06-17-2007, 11:34 PM
Not sure what you mean but there is no password... If you just say no to Update all links it will work fine!

Thanks a lot

mdmackillop
06-18-2007, 12:08 AM
Hi itipu,
If you are going to make any progress, you have to use Option Explicit and declare your variables. I don't know if your problem is related to this as I can't compile your code.

itipu
06-18-2007, 12:12 AM
ActiveSheet.Range("A2:A$" & columnLength).Formula = _
"=IF(ISERROR(VLOOKUP(D1,Sheet7!$A:$B,2,FALSE)),""0"", VLOOKUP(D1,Sheet7!$A:$B,2,FALSE))"

So "A2"A$" specifies that cells in Column A2 onwards will be set to this formula.

D1 specifies which item will be compared with my Table? not sure what are $A:$B,2,?

Thanks a lot

Mike

mdmackillop
06-18-2007, 12:17 AM
Check VLOOKUP in Excel Help; also, enter a VLOOKUP function in your worksheet using Insert/Function. These will explain the parameters as well as I could.

itipu
06-18-2007, 01:25 AM
Cheers, fixed that one it works great now...!

Do you by any chance know how to format a Column into text...

Once I do my Vlookup for some reason all values (and they are numbers) in Column L become integers, while I need them as text... I used before "'" to get conversion to text but this no longer works with Vlookup...

ActiveSheet.Range("E2:E$" & columnLength).Formula = _
"=IF(ISERROR(VLOOKUP(D2,Table!$A:$C,3,FALSE)),""0"", VLOOKUP(D2,Table!$A:$C,3,FALSE))"

This populates Column L but with integeres instead of text... I can celect Column and use Format Cells Category Text... but sure there is another way...

Thanks a million

Mike

itipu
06-18-2007, 01:38 AM
Resolved it by editing table values as '123