VBA Relative Reference Formula Help

[rilzniak]

I'm trying to copy a formula which refers to another file on my computer but I can't drag with the INDIRECT formula because I get an error (#REF) when the file is closed. I knew this so I was trying to find a solution to the problem and came across the PULL VBA function that was created a while ago. The problem with that solution is that it is impossibly slow for the amount of data I have (I tested on a small sample).

So...I've come up with the idea of writing the formula in each cell using a macro, although I'm not sure how to do it. Hoping someone here can help.

I want to have a formula written in column E which takes part of the name from columns C and D. Here's what I've got:

Sub formula()
Range("E3:E694").formula = _
    "=VLOOKUP(DATE(year-1,12,31),'M:\Ryan\[State_" & [$C3] & "_2014.xls]" & [$D3] & "'!$C$5:$W$370,20,FALSE)"
End Sub

The problem is that it prints the same thing down the column. I want the formula to grab the city [$C3] and the neighborhood name [$D3] and then do the same for the next row in that column filling the whole range. I've read about incorporating the OFFSET formula as well but am not sure if that's the right thing to do.

What am I doing wrong? Any suggestions?

 

[rilzniak]

I think I've gotten a little closer to solving the problem. Here's where my code is at right now:

Sub Formula()

For I = 3 To 15
 Cells(I, "F").Formula = "=(VLOOKUP(DATE(year-1,12,31),'M:\" & Cells(I, "D") & "\[State_" & Cells(I, "C").Value & "_2014.xlsm]" & Cells(I, "E").Value & "'!$C$5:$W$370,20,FALSE)"
Next I


End Sub

My problem is that in column E some of the sheets do not exist in the target workbook the formula is looking up. I want to have it skip those rows and just leave that cell blank. I've tried to solve that problem by entering the IFERROR argument into the cell but that seems to cause problems. I'm a novice with VBA so I could use some advice.

Does anyone have some insight or advice?

 

[Jonmo1]

You can do a test if the sheet exists...

Try something like

Sub Formula()
Dim tstname As String
For I = 3 To 15
    On Error Resume Next
    tstname = Sheets(Cells(I, "E").Value).Name
    On Error GoTo 0
    If tstname = Cells(I, "E").Value Then
        Cells(I, "F").Formula = "=(VLOOKUP(DATE(year-1,12,31),'M:\" & Cells(I, "D") & "\[State_" & Cells(I, "C").Value & "_2014.xlsm]" & Cells(I, "E").Value & "'!$C$5:$W$370,20,FALSE)"
    End If
Next I


End Sub

 

[rilzniak]

Thanks Jonmo1, but when I run this nothing happens. Even the original data (the stuff before the blank cells) doesn't populate. What you're saying makes sense and I'd like to use your suggestion although I don't know enough to figure out why it isn't working.

Do I need to specify the ".Value" after the Cells(I,"D")? Or what else am I missing?

 

[rilzniak]

I figured this one out. Thanks for your help Jonmo1.