Problem Implementing ActiveCell.FormulaR1C1 in VBA (with string)

[MickFlanagen]

Good Morning,

I've done all kinds of searching, around this message board and other places, and I cannot figure out why I am getting an error here:

 ActiveCell.FormulaR1C1 = "=(IF(ISERROR(VLOOKUP($B6," & [I]Qtwo[/I] & "$C$2:$BE$92,54,FALSE)),""No Data"",VLOOKUP($B6," & [I]Qtwo[/I] & "!$C$2:$BE$92,54,FALSE)))"

For the record qtwo is defined using a text box much earlier in the code:

    Dim qone As String
    Dim qtwo As String
    
    qone = InputBox("Enter the factor name", "factor name")
    qtwo = InputBox("Enter Shortname", "short name")
           
    Selection.AutoFilter Field:=1, Criteria1:=qone
          
    Sheets.Add(Type:="C:\Documents and Settings\rutadav\Application Data\Microsoft\Templates\WB Data Template").Name = qtwo

The error I am getting is "Run-timer error '1004': Application-defined or object defined error"

Any help is appreciated.

Thanks,

Mick

 

[AlphaFrog]

1.) Missing an exclamation mark !

ActiveCell.FormulaR1C1 = "=(IF(ISERROR(VLOOKUP($B6," & Qtwo & "[COLOR="Red"]![/COLOR]$C$2:$BE$92,54,FALSE)),""No Data"",VLOOKUP($B6," & Qtwo & "!$C$2:$BE$92,54,FALSE)))"

2.) If Qtwo contains a space within the string, the sheet name within the formula has to be surrounded by single quotes e.g.
'My Sheet'!$C$2:$BE$92