Excel VBA: Using Variables in CountIf function

Question

I can't overcome this seemingly simple issue involving the use of a variable in a countif function - hoping you guys can help. I'm looping through a list of data that spans 2006 through 2024, using the if statements to determine the beginning and the end of my search range, which will be used in the countif function at the end of the code. The do/loop section appropriately defines the ranges, however I receive errors when the macro attempts to place the countif function in the designated cell using the variable containing the search range. Here's my code:

    Dim Year As Integer
    Dim Month As Integer

Year = InputBox("Enter the Current Year", "Choose Year for Analysis", "Type your desired year here")
If Len(Year) = 0 Then
    MsgBox "No year chosen, this macro will now end)"
    Exit Sub
End If
    Month = InputBox("Enter the first # that corresponds with the first month you would like to review", "Starting Month", "Enter the # that corresponds with your desired month here")
If Len(Year) = 0 Then
    MsgBox "No month chosen, this macro will now end)"
    Exit Sub
End If




    Dim SearchStart As Range
    Dim SearchEnd As Range
    Dim searchrange As Range



    'standard
Range("L2").Select
Do Until ActiveCell.Value = Year And ActiveCell.Offset(0, 1).Value > Month + 1
If ActiveCell.Value = Year And ActiveCell.Offset(-1, 0).Value = Year And ActiveCell.Offset(0, 1).Value = Month And ActiveCell.Offset(-1, 1).Value = Month Then
    ActiveCell.Offset(1, 0).Select
Else
    If ActiveCell.Value = Year And ActiveCell.Offset(-1, 0).Value = Year And ActiveCell.Offset(0, 1).Value = Month + 1 And ActiveCell.Offset(-1, 1).Value = Month + 1 Then
        ActiveCell.Offset(1, 0).Select
    Else
        If ActiveCell.Value = Year And ActiveCell.Offset(-1, 0).Value = Year And ActiveCell.Offset(0, 1).Value = Month + 1 And ActiveCell.Offset(-1, 1).Value = Month Then
            ActiveCell.Offset(1, 0).Select
        Else
            If Not ActiveCell.Value = Year And ActiveCell.Offset(0, 1).Value = Month And SearchStart Is Nothing Then
                ActiveCell.Offset(1, 0).Select
            Else
                If ActiveCell.Value = Year And ActiveCell.Offset(0, 1).Value = Month And Not IsEmpty(SearchStart) Then
                    ActiveCell.Offset(0, -1).Select
                    Set SearchStart = Selection
                    ActiveCell.Offset(1, 1).Select
                End If
            End If
        End If
    End If
End If
If ActiveCell.Value < Year Then
    ActiveCell.Offset(1, 0).Select
Else
    If ActiveCell.Value < Year And ActiveCell.Offset(0, 1).Value < Month Then ActiveCell.Offset(1, 0).Select
    If ActiveCell.Value = Year And ActiveCell.Offset(0, 1).Value < Month Then ActiveCell.Offset(1, 0).Select
End If

Loop

ActiveCell.Offset(-1, -1).Select
Set SearchEnd = ActiveCell
Range(SearchStart.Address, SearchEnd.Address).Select

Set searchrange = Selection

    'formula to find: Current month QTY & next month QTY
Range("z2").Select
Selection.FormulaR1C1 = "=COUNTIF(" & searchrange.Address & ",RC[-1])"

Not 100% but you probaly cant use an address with a R1C1 formula. Selection.Formula = "=COUNTIF(" & searchrange.Address & "," & selection.offset(0,-1).Address & ")" might work — 99moorem
– 99moorem, Commented Jul 9, 2015 at 15:34
You can use formulas with R1C1 formulas, but everything must be typed correctly - ensure that the text block you want to insert as a formula is resolving the way you expect. For example I believe you need 'R[0]C[-1]', I don't think you can just say 'RC[-1]' — Grade 'Eh' Bacon
– Grade 'Eh' Bacon, Commented Jul 9, 2015 at 15:38

ChipsLetten · Accepted Answer · 2015-07-09 15:38:47Z

3

In this block of code you are mixing how the ranges are addressed:

Selection.FormulaR1C1 = "=COUNTIF(" & searchrange.Address & ",RC[-1])"

You need to use the R1C1 syntax:

Selection.FormulaR1C1 = "=COUNTIF(" & searchrange.Address(ReferenceStyle:=xlR1C1) & ",RC[-1])"

FYI, you should avoid using Select, so you can replace:

Range("z2").Select
Selection.FormulaR1C1 = "=COUNTIF(" & searchrange.(AddressReferenceStyle:=xlR1C1) & ",RC[-1])"

With:

Range("z2").FormulaR1C1 = "=COUNTIF(" & searchrange.Address(ReferenceStyle:=xlR1C1) & ",RC[-1])"

answered Jul 9, 2015 at 15:38

ChipsLetten

2,9531 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jmurph Over a year ago

Thank you so much Chips! I never would have thought to resolve this issue like that - thank you for imparting your valuable knowledge!

Collectives™ on Stack Overflow

Excel VBA: Using Variables in CountIf function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related