Strings and Cells

[bountiful]

Help!

I'm trying to simply count up the number of opened and closed tickets to tally them up and I can't seem to get my cells referenced correctly.

P.S. I've only been coding vba for two days so cut me some slack xD i'm sure it's easy.
the sheet is called data and the ticket status is in column 2

Dim ClosedCount As Integer, Tcount As Range
ClosedCount = 0
Set Tcount = Sheets("Data").UsedRange
Set Tcount = Tcount.Columns(2)
For x = 1 To 29563 ' I need a dynamic refference (cClosed.End did not work)
If Cells(x, 2).Val = "Closed" Then
ClosedCount = ClosedCount + 1
Tcount = Tcount + 1
Else
Tcount = Tcount + 1
End If

MsgBox (cClosed.End(xlUp))
Next x

 

[strooman]

What column you want to count? If column B, use:
=COUNTIF($B$1:$B$29563,"Closed")

 

[V_Malkoti]

Modified classic Range.Find example a bit for your needs

Sub countClosed()
    Dim c As Range
    Dim firstAddress As String
    Dim count As Long
    
    count = 0
    With Sheets("Sheet5").Range("B:B")
        Set c = .Find("Closed", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                count = count + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
    Debug.Print count
End Sub

 

[bountiful]

this is quite close to being VERY helpful to me, I fon't understand the "If Not C" line and the ".Range("B:B")" Parts

Thanks for your help

 

[V_Malkoti]

If Not c is Nothing
checks if a cell was found and returned by Find.

Range("B:B")
means entire column B - all cells in that column.

 

[bountiful]

OK, I understand now, however i'm getting a runtime error "subsript out of range" on the "B:B" line

 

[V_Malkoti]

Probably because you don't have "Sheet5" in your file. Update highlighted with your sheet name

With Sheets("[COLOR=#ff0000]Sheet5[/COLOR]").Range("B:B")

 

[bountiful]

I switched it to sheet1 and tried using it's name "Data"

"Data" worked, thank you so much for the help. 5 points to V_domo.

 

[V_Malkoti]

You are welcome!