VBA Lookup Help

[saschmeling]

Can anyone tell me why this line of code is not working:

  ActiveCell.FormulaR1C1 = "=IF(RC[-5]>0,VLOOKUP(RC[-5],'C:\Users\Scott A. Schmeling\Google Drive\HCA Files\Projection Files\Projection Spreadsheets\Org 5110 Projections.xlsx'!Table2[#All],3),"")"

I keep getting an application defined or object defined error when I run it.

Here is the entire subroutine:

Sub Projections()

    Dim LR As Long
    Range("g7").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]>0,VLOOKUP(RC[-5],'C:\Users\Scott A. Schmeling\Google Drive\HCA Files\Projection Files\Projection Spreadsheets\Org 5110 Projections.xlsx'!Table2[#All],3),"")"
    LR = ActiveSheet.UsedRange.Rows.Count
    Range("G7").AutoFill Destination:=Range("G7:G" & LR)
    Columns("G:G").Select
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
 
End Sub

Basically, it should do the following.

Look at column A
If there is a number in column A
Look at the 0rg 5110 Projections Workbook Column 3
Return the appropriate number.


Open to any and all suggestions.

Thanks,

 

[didIDo_that]

First a question. If you have the Org 5110 Projections workbook open when you run the code do you still get the error? Second if you do not define the vlookup to find only an exact match (your code adjusted to find the exact match below) you may be leaving yourself open to bad data, but that isn't always the case.

le2[#All],3,False)</pre>

 

[saschmeling]

Thanks DidIDo_that,

Actually not being open was part of the issue. I have had to add a line to open the projections workbook. Then close it. Also I found out that once I added an extra set of "" at the end, it works.

The code is not perfect but it does get the job done right now.

If anyone has any cleanup suggestions would be appreciated!

Thanks,