VBA code to dynamically search worksheet and add up cells

Question

Sub CountLarge()
    Sheets("Data").Select
    Dim myNum As Integer
    Dim cell As Range
    Dim rngFullRange As Range
    Dim nLarge As Integer

    Do
        myNum = Application.InputBox("Enter a number")
        If myNum = "" Then Exit Sub
    Loop Until myNum > 0 And myNum < 210

    With Range(“A1”)
        Range(.Offset(1, 0), .End(xlDown).End(xlToRight)).Name = 
“dynamicRange”
    End With

    Set rngFullRange = wsData.Range(“dynamicRange”)

    For Each cell In rngFullRange
        If cell.Value > myNum Then
            nLarge = nLarge + cell.Value
        End If
    Next

    MsgBox (nLarge)
End Sub

This is my code I need it to go through each value in my worksheet (data starts at A2 and spans the whole worksheet so I set a dynamic range) and adds up all the values greater than the user input and displays it in a message box. When I run the program I am able to enter a number but I don't get a message box at the end.

**Note: I've edited the code using your feedback, currently the error is in the line With Range("A1"). The range fits the worksheet so I'm confused as to why, the data starts at A2 and fills the rest of the sheet which is why I used this range.

If you add the line debug.print rngFullRange.Address before the For Loop, what does it say in the Immediate Window (Ctrl+G)? — Profex
. . . MsgBox WorksheetFunction.SumIf(wsData.Cells, ">" & InputBox("Enter A Number"))? — Chronocidal
Where is myValue defined? I think that should be myNum. Using Option Explicit will alert you to errors like this because it forces you to define variables. — Marc
Step through with F8. Does it get caught in a loop? Does it bypass anything? Also, technically, it looks like you're mixing " and “. Make sure all quotes are " and not the other ones (such as you have around dynamicRange). I'm betting that's the reason for your error on With Range("A1")...because the double quote characters used aren't the correct ones for VBA. — BruceWayne

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-10-18T16:22:47

As @Chronocidal said:

=SUMIF(Sheet1!$A$1:$HA$609,">50",Sheet1!$A$1:$HA$609)

Or the long way around:

Use FIND to find all values on the sheet and add up those that are numbers less than the required amount.

The function would be as below, although you may want to change With wrkSht.Cells to only look at the range of cells that are populated:

Public Function CountLarge(StartNumber As Double, Target As Range) As Double

    Dim wrkSht As Worksheet
    Dim myNum As Double
    Dim sFirstAddress As String
    Dim rFound As Range
    Dim dSum As Double

    Set wrkSht = Target.Parent

    With wrkSht.Cells
        Set rFound = .Find("*", , xlValues, xlWhole, , xlNext)
        If Not rFound Is Nothing Then
            sFirstAddress = rFound.Address
            Do
                If IsNumeric(rFound) Then
                    If rFound >= StartNumber Then
                        dSum = dSum + rFound
                    End If
                End If

                'Set rFound = .FindNext(rFound) 'Doesn't work if in a UDF.
                Set rFound = .Find("*", rFound, xlValues, xlWhole, , xlNext) 'Works in a UDF.

            Loop While rFound.Address <> sFirstAddress
        End If
    End With

    CountLarge = dSum

End Function

You can then call this directly in a cell with something like:

=CountLarge(50,Sheet1!$A$1)

or you can call it in another sub using:

Public Sub Test()

    Dim myNum As Double

    myNum = Application.InputBox("Enter a number:", Type:=1)

    MsgBox "Final sum: " & CountLarge(myNum, Sheet1.Range("A1"))

End Sub

You could change the sheet reference to just a text string:

MsgBox "Final sum: " & CountLarge(myNum, "Sheet1")

For this to work you'd have to change the function arguments to:

Public Function CountLarge(StartNumber As Double, SheetName as String) As Double

And set the worksheet as:

Set wrkSht = ThisWorkbook.Worksheets(SheetName)

VBA code to dynamically search worksheet and add up cells

2 Answers