VBA Syntax Error

[BenGee]

Hi - I have this code but it keeps chucking up a syntax error at this part;

Range("E20").Formula = "IF(AND(D20=1,COUNTIF($C$4:$C$6,">="&1)=3),"Yes",IF(AND(OR(D20=2,D20="3A",D20="3B",D20=4,D20=5),COUNTIF($C$4:$C$6,">="&1)>=1),"Yes","No"))"

Full Code;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D3")) Is Nothing Then
    Exit Sub
    Else
        Range("$D$4").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B4,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$5").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B5,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$6").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B6,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$7").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B7,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$8").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B8,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$9").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B9,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$10").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B10,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$11").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B11,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$12").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B12,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$13").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B13,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$14").FormulaArray = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B14,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
    End If
    If Intersect(Target, Range("D20")) Is Nothing Then
    Exit Sub
        Range("E20").Formula = "IF(AND(D20=1,COUNTIF($C$4:$C$6,">="&1)=3),"Yes",IF(AND(OR(D20=2,D20="3A",D20="3B",D20=4,D20=5),COUNTIF($C$4:$C$6,">="&1)>=1),"Yes","No"))"
    End If
End Sub

Any idea why this might be?

Thank you

 

[alansidman]

I believe that you will need to identify each of the cells in your IF formula as a range, ie. Range("D1") instead of D1.

 

[RoryA]

You need to double up any quotes that are inside the formula string itself - like this:

Range("E20").Formula = "=IF(AND(D20=1,COUNTIF($C$4:$C$6,"">=""&1)=3),""Yes"",IF(AND(OR(D20=2,D20=""3A"",D20=""3B"",D20=4,D20=5),COUNTIF($C$4:$C$6,"">=""&1)>=1),""Yes"",""No""))"

 

[BenGee]

Perfect, thanks Rory!

It's got rid of the error however nothing happens when I enter a value into D20 whereas having the formula in the cell, it would count as appropriate?

 

[Fluff]

I think you're missing an =

Range("E20").Formula = "[COLOR=#ff0000]=[/COLOR]IF(AND(D20=1,COUNTIF($C$4:$C$6,"">=""&1)=3),""Yes"",IF(AND(OR(D20=2,D20=""3A"",D20=""3B"",D20=4,D20=5),COUNTIF($C$4:$C$6,"">=""&1)>=1),""Yes"",""No""))"

 

[RoryA]

What are you entering in D20 and what is showing in E20?

 

[BenGee]

D20 will be either 1, 2, 3A, 3B, 4 or 5
E20 is supposed to return "Yes" or "No" dependent on which IF condition is met. Currently though, when I enter a value into E20, it's just blank!

 

[RoryA]

There are two problems. One is the missing = that Fluff mentioned and the other is that your code exits if you didn't change D3. Change the code to this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D3")) Is Nothing Then
        Range("$D$4").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B4,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$5").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B5,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$6").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B6,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$7").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B7,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$8").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B8,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$9").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B9,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$10").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B10,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$11").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B11,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$12").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B12,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$13").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B13,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
        Range("$D$14").Value2 = Evaluate("INDEX(Roles!$B$2:$AH$12,MATCH('Maps'!B14,Roles!$A$2:$A$12,0),MATCH('Maps'!D3,Roles!$B$1:$AH$1,0))")
    End If
    If Not Intersect(Target, Range("D20")) Is Nothing Then
        Range("E20").Formula = "=IF(AND(D20=1,COUNTIF($C$4:$C$6,"">=""&1)=3),""Yes"",IF(AND(OR(D20=2,D20=""3A"",D20=""3B"",D20=4,D20=5),COUNTIF($C$4:$C$6,"">=""&1)>=1),""Yes"",""No""))"
    End If
End Sub

 

[BenGee]

This is perfect, thank you for all your help Rory