VBA Cells(Rows.Count,"A").End(xlUp).Row & Other counting

[KaibleBasha]

Hi

I am trying to ascertain worker caseloads for workers within the service for which I work by using the below;
Sub CommandButton1_Click()
Dim a, b, c, d, e, f, g, h As Integer
Dim CC, LB, KC, TH As String
Dim LstRw, Rw As Long

Sheets("Recovery Caseload").Select

LstRw = Cells(Rows.Count, "C").End(xlUp).Row
CC = "Worker1"
LB = "Worker2"
KC = "Worker3"
TH = "Worker4"
a = 2
b = 2
c = 2
d = 2
e = 0
f = 0
g = 0
h = 0
Do While a <=LstRw
If Cells(a,"C") = CC Then e = e + 1
a = a + 1
Loop

CCCheck = MsgBox(LstRw & " " & e, vbOKOnly, "Caseloads - " & _
"Primary & Secondary worked")

End Sub

When I get the message box to display the result states "0 0" meaning there are 0 rows and 0 cells meeting my criteria, however the are about 370 rows, many of which match the criteria.

I have frequently used the LstRw as long and Cells(Rows.Count,"A") to find the last row of data on a given column, however this time is doesn't want to work. Though when I do this, it does (just curious why);
With Activesheet
LstRw = .Cells(.Rows.Count,"A").End(xlUp).Row
End With

After this I need to count the number of times a worker's name appears (e.g. Worker1 appearing 130 times), by totalling each instance and giving "e" a value. Then repeating this for workers2/3/4 and letters f,g & h.

Are you able to see what I'm doing wrong here? Is it the "Dim" at the top as this is the only thing I have really done differently compared to other macros

Thanks
Kai

 

[Takae]

• Dim a, b, c, d, e, f, g, h As Integer
  equal
  a ,b,c,d,e,f,g are variant,only h is numeric in VBA.
  I am not sure if the declaration is reason or not.
  Just try.
  

 

[KaibleBasha]

Ahh I thought that may be what'd happen to them I just thought I'd try somehting different.

Unfortunately the value of e still isn't increasing :/

Thanks
Kai

 

[RickXL]

Hi,

Yes, Takae is correct, VBA syntax is not always obvious so most of your variables will be dim'd as Variants.

Also, a personal choice, I rarely use a reference to a range without specifying which workbook and worksheet I am talking about. Usually, you can get away with it but just occasionally it will bite you. It gets me when I am debugging and I open another workbook to check something then my macro starts using that because it is not the active workbook.

So I have re-written your macro as if it was one of mine. This should force the macro to look at the right worksheet every time. Also, the variables are now dim'd as I would dim them. I prefer "Long" to "Integer" because you never run out of row numbers that way and I thing Excel uses Long internally any way.

Sub CommandButton1_Click()

    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim d As Long
    Dim e As Long
    Dim f As Long
    Dim g As Long
    Dim h  As Long
    Dim CC As String
    Dim LB As String
    Dim KC As String
    Dim TH As String
    Dim LstRw As Long
    Dim Rw As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Recovery Caseload")
    
    With ws
        LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row
        CC = "Worker1"
        LB = "Worker2"
        KC = "Worker3"
        TH = "Worker4"
        a = 2
        b = 2
        c = 2
        d = 2
        e = 0
        f = 0
        g = 0
        h = 0
        Do While a <= LstRw
            If .Cells(a, "C") = CC Then e = e + 1
            a = a + 1
        Loop
    End With
    
    MsgBox LstRw & " " & e, vbOKOnly, "Caseloads - " & "Primary & Secondary worked"

End Sub

 

[KaibleBasha]

Hi Rick,

Thanks for the code and the advice, will keep doing that in future just to be sure (think I got lazy).

This si working fine now

Thank you both for your help.
Kai

 

[RickXL]

No problem.

I am pleased that you got it working and thanks for letting me know.

Regards,