How to count cells based off of LEFT value of cell being between two values

Question

I have a spreadsheet with roughly 1500 lines, and I am trying to count the number of lines which have one cell value at 0 or lower, and then group them based off of the first 3 characters in another cell in that line.

This is my first attempt trying VBA, and I have done research on each section of this code but I've seen a fair number of contradicting answers for how to do each part of it. I based the code off of one I found to count the number of cells with a certain word in them, so it may be very far off from what I want.

For Each Cell in Range("S26:S1500")
Cell.Activate
If IsEmpty(Cell) Then Exit For
If Left(Cell.Value,3) >= 100 And Left(Cell.Value,3) <=150 And
If ActiveCell.Offset(0,-17) < 1 Then
MisProd = MisProd + 1

The result should be the total number of cells that start with 100 to 150 and have a cell 17 cells left as less than 1.

I instead receive a compile error: Expected: expression.

Why `.Activate`? That is extremely slow and unnecessary. You should also qualify your `Range("S26:S1500")` with your worksheet. — K.Dᴀᴠɪs, Jul 03 '19 at 21:59
[this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) should provide more details on avoiding activate and select in your code — cybernetic.nomad, Jul 03 '19 at 22:03

pgSystemTester · Accepted Answer · 2019-07-03T23:02:18.397

Updated First function had a couple type-o's. I tested and this one works.

This would probably be better as a custom function. Here's the syntax I think you're looking for.

Function MisProd (srchRNG As Range) As Long
Dim cell As Range

    For Each cell In Intersect(srchRNG, srchRNG.Worksheet.UsedRange).Cells
        'I use the intersection function so users can selet a whole column)

    If IsEmpty(cell) Then
        'probably do nothing if it's empty?
        'might also be better to use Isnumeric(Left(cell.value,3)) to avoid errors.

    ElseIf Left(cell.Value, 3) >= 100 And Left(cell.Value, 3) <= 150 Then
        'this is a range of 101 to 149...

        If cell.Offset(0, -17) < 1 Then
            'if column b for this row is less than 1
             MisProd = MisProd + 1
        End If

    End If

    Next Cell

End Function

You could use this in combination with a macro as shown here.

Sub nowAmacro()

    MsgBox "Your results are " & MisProd (Range("S26:S1500"))
    'better to specify the sheet such as Sheet1.Range("S26:S15000")


End Sub

score 0 · Answer 2 · answered Jul 04 '19 at 02:53

Firstly, the previous comments and answers are all good and will fix your VBA. Though if you're just trying to learn VBA then working out what your error is would be ideal. Put a break in your code and see if you can run through until your error occurs. It is probably something to do with an unusual row of data.

If it is definitely getting through your first half dozen rows, put a Debug.Print cell.row in and after it has errored, check your output window for the offending row number.

These methods will teach you to debug VBA code. And yours is mostly fine. Stop activating that cell as mentioned, and use "<= 0" not "< 1" for column A. Although that might be the same thing if they're all integers.

But finally, consider not using VBA for a logical task like this if you can. IF you have the ability to add columns to your sheet, add one to get the NUMBERVALUE of column S and then another to show a 1 or 0 if your conditions are met. Then sum the cells in your second column.

Sadly, I don't think adding a column to the spreadsheet is possible. The reason that I wanted to use VBA for this is that there is actually 11 different number ranges for values to fall into, so I need to be able to sum the number of overdue items in each of those different areas. Thank you very much for the advice on debugging though. — Eric Gilbey, Jul 04 '19 at 17:36

How to count cells based off of LEFT value of cell being between two values

2 Answers2