Using a Dimmed Range & Cell.Row in COUNTIF Formula-CodePudding

Afternoon all. Got a really simple one for you, that's been bugging me for about 2 hours now. Just can't seem to crack it :D

I have a ForEach going through a range of cells and setting the Formula to a COUNTIF formula... problem is, the COUNTIF needs the criteria of a dynamic range (which is set as rng2) and also the cell from the current Cel.Row located in the M column. Struggling to get my formula to work... will post a my idea that I've tried (been jiggling with the same formula for hours now) but the text still stays red haha

    pr = WC.Cells(Rows.Count, "A").End(xlUp).Row
    Dim rng As Range
    Set rng = Application.Range("YMS!U2:U" & pr)
    
    For Each cel In rng.Cells
    cel.Formula = "CountIf(Range("rng2"), " & "M" & cel.Row)"
    Next cel
    On Error Resume Next

Any ideas on how to get my COUNTIF formula to work with all cells in the range?

CodePudding user response：

You are missing the equal-sign - furthermore you have to put the address of rng2 to the formula.

    For Each cel In rng.Cells
       cel.Formula = "= CountIf(" & rng2.address & ", M" & cel.Row & ")"
    Next cel

CodePudding user response：

VBA Write COUNTIF Formula

Option Explicit

Sub WriteFormula()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Unknown") ' adjust!
    Dim srg As Range: Set srg = sws.Range("Unknown") ' adjust!
    
    Dim dws As Worksheet: Set dws = wb.Sheets("YMS")
    Dim drg As Range
    Set drg = dws.Range("U2:U" & dws.Cells(dws.Rows.Count, "A").End(xlUp).Row)
    
    Dim dFormula As String: dFormula _
        = "=COUNTIF('" & sws.Name & "'!" & srg.Address & ",M" & drg.Row & ")"
    'Debug.Print dFormula
    
    drg.Formula = dFormula

End Sub