Input Range.Address into a formula without ' '-CodePudding

Appreciate if anyone can help me out,

There are 2 Questions

Question1:

I have a VBA which should prompt an input box, allow user to select a cell and return the address into a vlookup formula. However, the cell will return the ' ' sign with the address

This is the actual result =IFNA(VLOOKUP('J1242','D:\Users\[file.xlsm]Working'!$A:$BG,15,0),"")

This is what I want =IFNA(VLOOKUP(J1242,'D:\Users\[file.xlsm]Working'!$A:$BG,15,0),"")

    Set myCell = Application.InputBox( _
        prompt:="Select a cell", Type:=8)
        
        MsgBox myCell.Address
        
        ActiveCell.FormulaR1C1 = _
            "=IFNA(VLOOKUP(" & myCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ",'D:\Users\[file.xlsm]Working'!C1:C59,15,0),"""")"
        ActiveCell.Offset(1, 0).Range("A1").Select

Question2:

Why does it return =IFNA(VLOOKUP('J1242','D:\Users\[file.xlsm]Working'!$A:$BG,15,0),"")

when my code shows:

"=IFNA(VLOOKUP(" & myCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ",'D:\Users\[file.xlsm]Working'!C1:C59,15,0),"""")"
    ActiveCell.Offset(1, 0).Range("A1").Select

I type in the formula using a macro recorder.

CodePudding user response：

The issue is this returns an A1 formatted address

myCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

but your formula .FormulaR1C1 is in R1C1 format.

So instead of .FormulaR1C1 you need to use .Formula which is A1 format. They both need to match in their format.