Compile Error: Expected Expression

[RockandGrohl]

Hi all, getting this expected expression error, it's highlighting the IF part, though I don't know why:

Sub FinalEnqCopydown()
'
' Copydown Macro
'


'


If ActiveCell.Row < 3 Then
MsgBox "Select Last Row in Data"


Else


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Do Until Cells(ActiveCell.Row, "A") = ""
Cells(ActiveCell.Row, "V").Formula =IF(Q3="","",IF(IF(COUNTIF(Q4:$Q$195850,Q3)>0,"","Y")="Y",VLOOKUP(Q3,Q:T,4,0),""))
Cells(ActiveCell.Row, "V").Value = Cells(ActiveCell.Row, "V").Value
ActiveCell.Offset(1, 0).Activate


Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic


End If
End Sub


Thanks

 

[Norie]

Try this.

Cells(ActiveCell.Row, "V").Formula = "=IF(Q3="""","""",IF(IF(COUNTIF(Q4:$Q$195850,Q3)>0,"""",""Y"")=""Y"",VLOOKUP(Q3,Q:T,4,0),""""))"

 

[Joe4]

The whole formula needs to be entered between double-quotes.
The double-quotes in your formula are going to confuse it too.

I find when you have a tricky formula like this, a good way to get the VBA code that is to use the Macro Recorder and record yourself entering the formula into a cell on your worksheet.
Then stop the Macro Recorder, and look at the code you just recorded.

 

[RockandGrohl]

Try this.

Cells(ActiveCell.Row, "V").Formula = "=IF(Q3="""","""",IF(IF(COUNTIF(Q4:$Q$195850,Q3)>0,"""",""Y"")=""Y"",VLOOKUP(Q3,Q:T,4,0),""""))"

That works, but I swear I tried wrapping the whole thing in quotes in the first place, can you explain if you changed anything else, and why?

 

[RockandGrohl]

The whole formula needs to be entered between double-quotes.
The double-quotes in your formula are going to confuse it too.

I find when you have a tricky formula like this, a good way to get the VBA code that is to use the Macro Recorder and record yourself entering the formula into a cell on your worksheet.
Then stop the Macro Recorder, and look at the code you just recorded.

That's how I got where I was in the first place, then I copied the code to another macro button and changed the formula, keeping the formatting the same, and here I am :P

 

[Norie]

As well as the formula being enclosed in quotes you need to 'double' up any quotes within the formula, so that's the other thing I changed.

 

[Joe4]

That's how I got where I was in the first place, then I copied the code to another macro button and changed the formula, keeping the formatting the same, and here I am :P

I think you misunderstood me.

You copied the formula directly from the workbook. That is NOT what I was saying to do.
I was saying turn on the Macro Recorder, and record yourself entering the formula into a cell on your workbook.
Then if you stop the Macro Recorder, and look at the code you just recorder, it will show you a way that you can enter it into VBA so that it will accept.

That code would look something like this:

Sub Macro1()
'
' Macro1 Macro
'

    Range("V3").Select
    ActiveCell[COLOR=#ff0000].FormulaR1C1 = _[/COLOR]
[COLOR=#ff0000]        "=IF(RC[-5]="""","""",IF(IF(COUNTIF(R[1]C[-5]:R195850C17,RC[-5])>0,"""",""Y"")=""Y"",VLOOKUP(RC[-5],C[-5]:C[-2],4,0),""""))"[/COLOR]

End Sub

The part in red is the part that you want.
Note that it looks a little different than Norie's code, as it uses R1C1 formula notation by default. Both work the same way, they are just presented a bit differently.

 

[RockandGrohl]

Nevermind

 

[Joe4]

Square brackets around number represent relative references, while numbers without brackets represent absolute brackets.
There is a grid/table on this link that shows the different options:
https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm

 

[RockandGrohl]

I think you misunderstood me.

You copied the formula directly from the workbook. That is NOT what I was saying to do.
I was saying turn on the Macro Recorder, and record yourself entering the formula into a cell on your workbook.
Then if you stop the Macro Recorder, and look at the code you just recorder, it will show you a way that you can enter it into VBA so that it will accept.

That code would look something like this:

Sub Macro1()
'
' Macro1 Macro
'

    Range("V3").Select
    ActiveCell[COLOR=#ff0000].FormulaR1C1 = _[/COLOR]
[COLOR=#ff0000]        "=IF(RC[-5]="""","""",IF(IF(COUNTIF(R[1]C[-5]:R195850C17,RC[-5])>0,"""",""Y"")=""Y"",VLOOKUP(RC[-5],C[-5]:C[-2],4,0),""""))"[/COLOR]

End Sub

The part in red is the part that you want.
Note that it looks a little different than Norie's code, as it uses R1C1 formula notation by default. Both work the same way, they are just presented a bit differently.

I did misunderstand you, and then I did what you suggested prior to reading your post and it worked. It's currently churning through the lines as we speak.

As ever, I really appreciate the help and guidance on this forum - you guys are angels.