Vlookup using VBA Constants

seahawk56

New Member
Joined
Jun 23, 2008
Messages
30
I want to use a VLOOKUP formula to pull data from additional workbooks. I would like to use constants/dimensions in VBA to reference the “table_array” in the formula. The pathways will always stay the same but the name will change to the date. The formula will bring back the data but I have to select the workbook each time. Is there any helpful hints to make it work? Or is this a limitation of the VLOOKUP?
Formula:
"=IF(ISERROR(VLOOKUP($P120,MyStaff1!MonProd,3,FALSE)),0,VLOOKUP($P120,MyStaff1!MonProd,3,FALSE))"

Code:
Public Const MyFile As String = "OPLdailyworkplanning"
Public Const MyLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\OPL\Planned Work\"
Public Const MyProdFile As String = "ProdTrack"
Public Const MyProdLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\OPL\Production Tracking\"
Public Const MyTempLoc As String = "G:\ROC-CLAIMS\Clms Proc-Model Line\DMS\OPL\Production Tracking\Templates"
Public Const MyTempFile As String = "ProdTrack_Template.xls"
StaffName1 = ActiveSheet.Range("A3").Value
MyWeek = " " & MyProdFile & "_" & ConvertToJulian(ActiveSheet.Range("D1").Value) & "_" & ConvertToJulian(ActiveSheet.Range("E1").Value) & ".xls"
MyStaff1 = MyProdLoc & StaffName1 & "\" & StaffName1 & MyWeek

 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
I don't know what that code has to do with VLOOKUP, but it is straightforward, use

Code:
    If IsError Application.VLOOKUP(Range("P120").Value,Workbooks("thatbook.xls").Worksheets("thatSheet").Range("M1:P50"),3,False) Then

        myVal = ""
Else

        myVal = Application.VLOOKUP(Range("P120").Value,Workbooks("thatbook.xls").Worksheets("thatSheet").Range("M1:P50"),3,False)
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,785
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top