lundi 26 janvier 2015

VBA in Excel - If statement Counter wont work

I have been trying to get this VBA script to work to automate a task, but I cannot get it to work.


Basically, I have a big task list in excel with multiple columns and over 1000 Rows. It contains the task, who it is assigned to, and if it is open or closed.


In column H is who it assigned to and column N is whether the task is opened or closed.


I am trying to search by last name and if it is OPEN to add one to the counter. The end goal is to get a total count of how many open tasks a person has. Also, some of the cells in column N (task status) has extra text like comments, etc. I am sure that a InStr Function to search for the one word within the Cell would work better, but I cannot figure it out...


here is my code



Sub statuscount()

Dim tasksheet As Worksheet
Dim simons_count As Integer

Set tasksheet = ThisWorkbook.Sheets("tasks")

lr = tasksheet.Cells(Rows.Count, 1).End(xlUp).Row

For x = 5 to lr
If tasksheet.Cells(x, 8) = "Simons" And tasksheet.Cells(x, 14) = "OPEN" Then
simons_count = simons_count + 1
End If

Next x

tasksheet.Range("$O$5").Value = simons_count

End Sub


Thanks for the help!


Aucun commentaire:

Enregistrer un commentaire