Syntax Error Adding Vlookup Formula To Worksheet

Hello Everyone,

Can somebody help me with this? Functionally speaking, I think it works but I believe there is a syntax problem.

Dim MyName As String
MyName = ThisWorkbook.Name
ActiveWorkbook.Names.Add Name:="MainRange", RefersToR1C1:=MainRange
MainWorkBook.Activate
Sheets("Main Worksheet").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = _
              "=VLOOKUP(RC[-8],MyName!MainRange,2,FALSE)"

The error is around the MyName!rRange part. I am unsure of why this is not working, as MyName is the opened file name and MainRange is a defined range in MyName. Please advise. Thanks.

Re: Syntax Error

Hello Jim,

Thanks for the quick reply. I actually don't receive an error in the result; rather in the execution of the Macro (run-time error).

The following error is received:

Run-time error '-2147417847 (80010108)':
Method 'FormulaR1C1' of object 'Range' failed

When I try to debug it, I am brought into Microsoft Visual Basic and the following lines are highlighted yellow:

ActiveCell.FormulaR1C1 = _
              "=VLOOKUP(RC[-8],MyName!MainRange,2,FALSE)"

Re: Syntax Error

Quote from norie

Where are you defining MainRange used here in the RefersTo argument?

Code

ActiveWorkbook.Names.Add Name:="MainRange", RefersToR1C1:=MainRange

MainRange is used in a prompt before the above code. Sorry that I forgot to add that.

Set MainRange = Application.InputBox(Prompt:= _
                "Please select a range.", _
                    Title:="SPECIFY RANGE", Type:=8)

Re: Syntax Error

Sorry Jim, it is the latter one. I originally copied the wrong formula and then I went and made the revisions to my post.

Re: Syntax Error

Hi Jim,

Not sure why but it seems to work now after I copied and pasted the entire code into another file..

Thanks anyways though.

Josh