Compile Error Invalid use of property

[larrygreg]

I am in th process of switching over from a worksheet to vba code.

I am stuck on one are with table lookups.

Code in the workbook is;

=IFERROR(IF(XXX="XXX",VLOOKUP(XXXX,'Directory[EXCEL FILE]SHEET'!$A:$E,5,0))

In my VBA Code, I have;

If Selection.Value = "XXXX" Then
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 "=IFERROR(VLOOKUP(R[0]C[-1], '[File]Sheet'!$A:$E,5,0"""")"
End If
I get a compile error of Invalid use of property.

Any suggestions.

Thanks

 

[VoG]

Try

ActiveCell.FormulaR1C1 "=IFERROR(VLOOKUP(RC[-1], '[File]Sheet'!$A:$E,5,0,"""")"

 

[Firefly2012]

I think you'd need:

ActiveCell.FormulaR1C1 "=IFERROR(VLOOKUP(RC[-1], '[File]Sheet'!C1:C5,5,0),"""")"

But note that the '[File]Sheet' does need to be a valid file name and sheet name which I suspect they are not at the moment.

 

[larrygreg]

Thanks for the replies. I changed the file name, so I have a local table that is named. However my problem still exists.

The error is occuring at formular1c1

Is there another way to get the value in the cell?

Thanks for the help

 

[Colin Legg]

You also need an = between FormulaR1C1 and the start of the string.

ie

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1], '[File]Sheet'!$A:$E,5,0,"""")"

Adjust [File]Sheet accordingly.

 

[adityaroongta]

I am getting a 'Compile error - Invalid use of property' while using the following macro trigger: -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$Q$30" Then
If Target.Value > 1 Then
Sort
End If
End If
End Sub

Can anyone help me debug this?

 

[VoG]

What is Sort? If it is a subroutine you should rename it as Sort is a VBA keyword.

 

[adityaroongta]

What is Sort? If it is a subroutine you should rename it as Sort is a VBA keyword.

Sort is the name of a Macro which sorts a table in the active worksheet where I want to trigger this macro

 

[VoG]

Try changing its name to MySort then use

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$Q$30" Then
    If Target.Value > 1 Then
        Call MySort
    End If
End If
End Sub

 

[adityaroongta]

Try changing its name to MySort then use

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$Q$30" Then
    If Target.Value > 1 Then
        Call MySort
    End If
End If
End Sub

The above changes stop the error to appear, however the code doesn't trigger the macro anymore. Can't understand why . Could you help if I could mail you the spreadsheet. Please reply. Thanks!