Syntax Error with Formula

[GreenyMcDuff]

Hi all,

I have staired and staired and staired at this for ages and can't find a problem

Can you:

Range("C8").Resize(LastRow - 7).Formula = "=vlookup(" & Cells(8, 2).Address(False, True) & ",'N:\xxx\xxx\[Name.xls]Sheet Name'!$A:$D,4,0)"

Bearing in mind that Lastrow is an integer variable and I've checked it is returns a variable.

Thanks

 

[Jonmo1]

It works for me..

In what way does it not work for you?
do you get an error, what error?

 

[GreenyMcDuff]

Hi Jonmo,

I get the following error:

"Application defined or object defined error"

Thanks

 

[Jonmo1]

I can only duplicate the error if the variable LastRow doesn't have a value..
OR The variable = 7 or less (LastRow - 7 resulting in 0 or negative #)

Does it work without the variable, just plug in some random number..

Range("C8").Resize(50 - 7).Formula = "=vlookup(" & Cells(8, 2).Address(False, True) & ",'N:\xxx\xxx\[Name.xls]Sheet Name'!$A:$D,4,0)"

 

[GreenyMcDuff]

No it doesn't work without the variable. Just put 30 into it and got the same error message :S

This is very strange.

I have double checked the path and the sheet name as well. Not sure what else to try

 

[Jonmo1]

Where is the code?
Is it in a standard module, or a sheet module, or the ThisWorkbook module, or a Form Module?

It will happen if the code is in the ThisWorkbook or a Form's Module, because there is no active sheet in those modules..

Try specifying the sheet to put the formula on

Try

With Sheets("Sheet1") 'Sheet you are entering the formula on.
    .Range("C8").Resize(LastRow - 7).Formula = "=vlookup(" & .Cells(8, 2).Address(False, True) & ",'N:\xxx\xxx\[Name.xls]Sheet Name'!$A:$D,4,0)"
End With

 

[GreenyMcDuff]

Unfortunately, I think I have narrowed it down to aproblem with the path.

At least I know what it is now

Is there a limit to the number of folder you can drill down through in VBA (2007)

Thanks for your help

 

[Jonmo1]

Are you sure it's the path?

I've been using your code EXACTLY as you posted it...bogus path and filename included.
And it still works.
It prompts me with a Browse for File window, and I click cancel.
The formulas still appears in the cells with the invalid path and all.
But more importantly, it does NOT cause the error you've posted.

How did you narrow it down to the path?

Does it work without the path\filename?

Range("C8").Resize(LastRow - 7).Formula = "=vlookup(" & Cells(8, 2).Address(False, True) & ",'Sheet Name'!$A:$D,4,0)"