VBA - Check if cell contains certain texts. True/False. Fill Down

Question

I'm trying to write a macro that checks if Column C contains text. If it does then it will add a formula to Column A that checks if Column F contains either "V:" or "EC:", if so then returns True, otherwise False.

Sub FillDown()    
    Dim LastRowC As Long
    Dim VECFormula As String
    LastRowC = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
    VECFormula = "=IF(SUM(COUNTIF($F2, {"*V:*","*EC:*"})), "Yes", "No")"
    ActiveSheet.Range("A2").Formula = VECFormula
    ActiveSheet.Range("A2").AutoFill Destination:=ActiveSheet.Range("A2:A" & LastRowC)
End Sub

That looks like it would work except `{"*V:*","*EC:*"}`... never tried that. — findwindow, Jul 27 '22 at 15:38
What's the specific problem you're having? BTW you can't put " inside a string in VBA without doubling it up to escape it, so `VECFormula = "=IF(SUM(COUNTIF($F2, {""*V:*"",""*EC:*""})), ""Yes"", ""No"")"` — Tim Williams, Jul 27 '22 at 15:39

score 0 · Accepted Answer · answered Jul 27 '22 at 16:09

0

Solved by Tim Williams:

Sub FillDown()    
    Dim LastRowC As Long
    Dim VECFormula As String
    LastRowC = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
    VECFormula = "=IF(SUM(COUNTIF($F2, {""*V:*"",""*EC:*""})), ""Yes"", ""No"")"
    ActiveSheet.Range("A2").Formula = VECFormula
    ActiveSheet.Range("A2").AutoFill Destination:=ActiveSheet.Range("A2:A" & LastRowC)
End Sub

answered Jul 27 '22 at 16:09

Tobias.Husbands

11
4

Note you can skip the `AutoFill` and just use `ActiveSheet.Range("A2:A" & LastRowC).Formula = "=IF(SUM(COUNTIF($F2, {""*V:*"",""*EC:*""})), ""Yes"", ""No"")"` - the formula will auto-adjust to the range you place it in. – Tim Williams Jul 27 '22 at 18:29

VBA - Check if cell contains certain texts. True/False. Fill Down

1 Answers1