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]

Perhaps events have been disabled. In the Visual Basic Editor press CTRL + G to open the Immediate window, type

Application.EnableEvents=True

and press Enter. Then try again.

 

[adityaroongta]

Perhaps events have been disabled. In the Visual Basic Editor press CTRL + G to open the Immediate window, type

Application.EnableEvents=True

and press Enter. Then try again.

Still doesn't work . The error has stopped but the trigger doesn't work.

 

[VoG]

Try this - do you see a message?

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

 

[adityaroongta]

Try this - do you see a message?

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

Still no luck. I even tried putting the macro (MySort) and the above trigger in the same module (earlier they were in different modules)...though I don't know if it makes a difference. It still doesn't work!

 

[VoG]

The Worksheet_Change code has to go in the sheet's code module. Right click the sheet tab, select View Code and paste in the code.

The MySort macro has to go in a regular module.

 

[adityaroongta]

The Worksheet_Change code has to go in the sheet's code module. Right click the sheet tab, select View Code and paste in the code.

The MySort macro has to go in a regular module.

Did the above. The trigger refuses to run.

 

[VoG]

Try saving the file, close Excel then restart it.

 

[andrewkard]

Hello, try change $Q$30"

 

[adityaroongta]

Try saving the file, close Excel then restart it.

It doesn't help. There is something which is being missed. Don't know what!

 

[VoG]

Are you manually changing the value of Q30 to greater than 1?