CODEWITHSUNDEEP
excelvbavariablesrangecountif
jmurph
jmurph

Reputation: 13

Excel VBA: Using Variables in CountIf function

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])"

Upvotes: 1

Views: 3717

Answers (1)

ChipsLetten
ChipsLetten

Reputation: 2953

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])"

Upvotes: 3

Related Questions