VBA Run Time error 424 Object required

Question

This is my first time using VBA and I'm trying to see what I'm doing wrong here. Does anyone knows why I'm getting this error?

Sub CountHighSales()
     Dim i As Integer
     Dim j As Integer
     Dim nHigh As Integer
     Dim cutoff As Currency

     cutoff = InputBox("What sales value do you want to check for?")
     For j = 1 To 6
         nHigh = 0
         For i = 1 To 36
             If wsData.Range("Sales").Cells(i, j) >= cutoff Then _
                 nHigh = nHigh + 1
         Next i

         MsgBox ("For region " & j & ", sales were above " & Format(cutoff, "$0,000") & " on " & nHigh & " of the 36 months.")

     Next j
 End Sub

wsData is this the name of your sheet, or its CodeName ? — A.S.H
Once you define "wsData" and assuming its "Sales" named range has 36 rows and 6 columns, then you could avoid the loop and simply write: nHigh = WorksheetFunction.CountIf(wsData.Range("Sales"), ">=" & cutoff) — user3598756
I re-write the code to: Sub CountHighSales() Dim nHigh As Integer Dim cutoff As Currency cutoff = InputBox("What sales value do you want to check for?") nHigh = WorksheetFunction.CountIf(wsData.Range("Sales"), ">=" & cutoff) MsgBox ("For region " & j & ", sales were above " & Format(cutoff, "$0,000") & " on " & nHigh & " of the 36 months.") End Sub And Still not working. — Mary Ann Rodriguez
Which line are you getting the error on? When you get the error message popup window, click the Debug button and let us know which line is highlighted in yellow. — J. Garth

user3598756 user3598756 · Accepted Answer · 2017-03-14T08:10:40

try this (commented) code:

Option Explicit

Sub CountHighSales()
    Dim nHigh As Integer
    Dim cutoff As Currency
    Dim wsData As Worksheet
    Dim j As Long

    Set wsData = Worksheets("MyData") '<--| set your data worksheet (change "MyData" to your actual worksheet with data name)
    cutoff = Application.InputBox("What sales value do you want to check for?", "cutoff value", , , , , , 1) '<--| use 'Application.InputBox()' method with 'Type' parameter set to 1 to force a "numeric" input
    With wsData.Range("Sales") '<--| reference the named range of given worksheet
        For j = 1 To .columns.Count '<--| loop through referenced range columns
            nHigh = WorksheetFunction.CountIf(.columns(j), ">=" & cutoff) '<--| count current column cells above cutoff value
            MsgBox ("For region " & j & ", sales were above " & Format(cutoff, "$0,000") & " on " & nHigh & " of the 36 months.")
        Next j
    End With
End Sub

VBA Run Time error 424 Object required

1 Answers