Countif Synthax

[Ramo964516]

Hi everybody,

I was coding a loop, and i was wondering why my macro doesn't work. The idea of the macro is to determine the month's number of weeks. With knowing the name of the fisrt workday of a month i can countif the number of this occurence in a range of week's day. Her it is :


Dim dbNbSemaines As Double
Dim strAbrevJourMois As String

strAbrevJourMois = Range("E4").Value

If strAbrevJourMois = "lun" Then
Range("A404").FormulaR1C1 = "=COUNTIF(E4:AB4,""lun"")"
dbNbSemaines = Range("A404").Value

ElseIf strAbrevJourMois = "mar" Then
Range("A404").FormulaR1C1 = "=COUNTIF(E4:AB4,""mar"")"
dbNbSemaines = Range("A404").Value

ElseIf strAbrevJourMois = "mer" Then
Range("A404").FormulaR1C1 = "=COUNTIF(E4:AB4,""mer"")"
dbNbSemaines = Range("A404").Value

ElseIf strAbrevJourMois = "jeu" Then
Range("A404").FormulaR1C1 = "=COUNTIF(E4:AB4,""jeu"")"
dbNbSemaines = Range("A404").Value

ElseIf strAbrevJourMois = "ven" Then
Range("A404").FormulaR1C1 = "=COUNTIF(E4:AB4,""ven"")"
dbNbSemaines = Range("A404").Value


End If

When i go to the A404 cell that contains the countif formula it returns a #NOM? and it contains a =NB.SI('E4':'AB4';"lun"). How can i write it in vba to insert the range E4:AB4 in the COUNTIF formula ?

Thanks for your help

[Paul_Hossler]

Just use .Formula, not .FormulaR1C1

As an aside, you can get rid of all the If/Then 's if you want




Sub xxx()
Dim dbNbSemaines As Double
Dim strAbrevJourMois As String
Dim strFormula As String

' strAbrevJourMois = Range("E4").Value
strAbrevJourMois = "ven"

strFormula = "=COUNTIF(E4:AB4," & """" & strAbrevJourMois & """)"
Range("A404").Formula = strFormula
dbNbSemaines = Range("A404").Value
End Sub

[mancubus]

if the values in row 4 don't change, that is, if no recalculation is required, you dont need a formula.



Sub vbax_55301()

Dim dbNbSemaines As Double

With Range("A404")
.Value = Application.CountIf(Range("E4:AB4"), Range("E4").Value)
dbNbSemaines = .Value
End With

End Sub

[mancubus]

...................................................

duplicate post.