Countifs in Macros

user_8070746 0
Viewed 71

I'm trying to update an old macro that needs to validate two conditions, rather than one.

Here is the current code:

Columns("G:G").Select
TempString = "IF(RC[4]>0,""1,"","""")&IF(RC[7]>0,""2,"","""")&IF(RC[10]>0,""3,"","""")&IF(RC[13]>0,""4,"","""")"
Selection.FormulaR1C1 = "=IF(LEN(" + TempString + ") > 0, LEFT( " + TempString + ", LEN( " + TempString + " ) - 1 ), " + TempString + " )"
Columns("G:G").EntireColumn.AutoFit
Range("G1").Select
ActiveCell.FormulaR1C1 = "Map"

Here is the code I'm trying to use. I want it to validate two conditions, rather than the current one now. It should validate the column name, as well as the cell that's four immediate spaces to the right (If there's a number greater than 0 in the cell, it's true):

Columns("G:G").Select
TempString = "IF(COUNTIFS(R[-1]C[4],"*MAP*", RC[4], ">0"),""1,"","""")&IF(COUNTIFS(R[-1]C[7],"*MAP*", RC[7], ">0"),""1,"","""")"
Selection.FormulaR1C1 = "=IF(LEN(" + TempString + ") > 0, LEFT( " + TempString + ", LEN( " + TempString + " ) - 1 ), " + TempString + " )"
Columns("G:G").EntireColumn.AutoFit
Range("G1").Select
ActiveCell.FormulaR1C1 = "Map"

When I try to use the updated code, I get "Expected: End of Statement." I understand it's because of the commas, but I don't know to pull it in to a macro. Any help would be appreciated. Thank you.

excel-2016 excel-formula vba excel
1 Answers

TempString = "IF(COUNTIFS(R[-1]C[4],""*MAP*"", RC[4], "">0""),""1,"","""")&" & _
              "IF(COUNTIFS(R[-1]C[7],""*MAP*"", RC[7], "">0""),""1,"","""")"

With Columns("G:G")
    .FormulaR1C1 = Replace("=IF(LEN(<ts>)>0, LEFT(<ts>, LEN(<ts>)-1),<ts>)", _
                           "<ts>", TempString)
    .EntireColumn.AutoFit
End With
Range("G1").Value = "Map"
user_4788840
Related