Excel VBA "else" statement counting wrongly

Question

I have created an "If ElseIf Else" statement that will count the strings in a strings. It does count the keywords/strings that I want to be counted but it is counting the "others"/ Else items wrongly. As shown below highlighted in red, I should only have 6 strings that should be labelled as "others" but it counted as 8. It is a total of 18 rows but on the total results it counted it as 20. I am newby in VBA and needing experts advise. Thank you.

Option Compare Text

Public Sub Keywords()

Dim row_number As Long
Dim count_of_corp_or_windows As Long
Dim count_of_mcafee As Long
Dim count_of_token As Long
Dim count_of_host_or_ipass As Long
Dim count_of_others As Long
Dim count_of_X As Long
Dim count_of_all As Long
Dim items As Variant

row_number = 0
count_of_corp_or_windows = 0
count_of_mcafee = 0
count_of_token = 0
count_of_host_or_ipass = 0
count_of_X = 0
count_of_others = 0
count_of_all = 0
Do

row_number = row_number + 1
items = Sheets("LoginPassword").Range("N" & row_number)
    If InStr(items, "corp") Or InStr(items, "windows") Then
        count_of_corp_or_windows = count_of_corp_or_windows + 1
    ElseIf InStr(items, "mcafee") Then
        count_of_mcafee = count_of_mcafee + 1
    ElseIf InStr(items, "token") Then
        count_of_token = count_of_token + 1
    ElseIf InStr(items, "host") Or InStr(items, "ipass") Then
        count_of_host_or_ipass = count_of_host_or_ipass + 1
    ElseIf InStr(items, "X A") Then
        count_of_X = count_of_X + 1
    Else:
        count_of_others = count_of_others + 1

    End If

Loop Until items = ""

count_of_all = count_of_corp_or_windows + count_of_mcafee + count_of_token + count_of_host_or_ipass + count_of_X + count_of_others

Range("N2").Select

Selection.End(xlDown).Select
lastCell = ActiveCell.Address

ActiveCell.Offset(3, 0).Value = "Count"
ActiveCell.Offset(4, 0).Value = count_of_corp_or_windows
ActiveCell.Offset(5, 0).Value = count_of_mcafee
ActiveCell.Offset(6, 0).Value = count_of_token
ActiveCell.Offset(7, 0).Value = count_of_host_or_ipass
ActiveCell.Offset(8, 0).Value = count_of_X
ActiveCell.Offset(9, 0).Value = count_of_others
ActiveCell.Offset(11, 0).Value = count_of_all
ActiveCell.Offset(3, 1).Value = "Keywords"
ActiveCell.Offset(4, 1).Value = "Corp or Windows"
ActiveCell.Offset(5, 1).Value = "Mcafee"
ActiveCell.Offset(6, 1).Value = "Token"
ActiveCell.Offset(7, 1).Value = "Host or ipass"
ActiveCell.Offset(8, 1).Value = "X accounts"
ActiveCell.Offset(9, 1).Value = "Others"
ActiveCell.Offset(11, 1).Value = "Total"
ActiveCell.Offset(3, -1).Value = "Percent"
ActiveCell.Offset(4, -1).Value = count_of_corp_or_windows / count_of_all
ActiveCell.Offset(5, -1).Value = count_of_mcafee / count_of_all
ActiveCell.Offset(6, -1).Value = count_of_token / count_of_all
ActiveCell.Offset(7, -1).Value = count_of_host_or_ipass / count_of_all
ActiveCell.Offset(8, -1).Value = count_of_X / count_of_all
ActiveCell.Offset(9, -1).Value = count_of_others / count_of_all
End Sub

It would be helpful if you could provide the text from your worksheet (not just a picture) so it could be copy-pasted for testing. — garbb, Dec 04 '17 at 05:48

score 2 · Accepted Answer · answered Dec 04 '17 at 06:04

2

You should start row_number at 2 instead of 1 because cell N1 contains "Short Description" which you probably do not want to match to anything?

Also you are looping Until a blank cell but the blank cell has already been counted as an "other" cell so this plus the "Short Description" cell probably accounts for the 2 unexpected other cells that are counted. So probably just change row_number = 0 to row_number = 1 and Else: to ElseIf items <> "" then

answered Dec 04 '17 at 06:04

garbb

679
5
9

Hi, I have encountered another scenario, I need to count the row if it matches the 2 strings I want, for example "follow" and req" I tried the this code but not working: `ElseIf StringContains2(items, "follow") And InStr(items, "req") Then` I changed the "and" to "&" "+" but none of them work. – Jonathan Dec 06 '17 at 00:00
Uh, I don't think `StringContains2()` is a valid function? – garbb Dec 06 '17 at 03:48
Oh yes it doesn't but `ElseIf InStr(items, "host") AND InStr(items, "ipass") Then` "and" to "&" "+" none of them worked. Tried `ElseIf Trim(items) Like "*follow*" And Trim(items) Like "*req*" ` but also not working – Jonathan Dec 06 '17 at 04:22

score 0 · Answer 2 · answered Dec 04 '17 at 05:21

0

count_of_others is never set to zero after an iteration since it is out of the loop. This should happen for other counter variables too.

answered Dec 04 '17 at 05:21

Ibo

4,081
6
45
65

write `count_of_all = 0` after `row_number = row_number + 1`. You shoudl do this with other counters too – Ibo Dec 04 '17 at 05:28
I tried it but it's still giving the wrong results. – Jonathan Dec 04 '17 at 05:33
1

I just noticed you are using an Excel table. It is a very good choice since you can use structural referencing instead of absolute referencing meaning that you addresses and indexes will be based on the table and if you move the table or expand it etc, there is no need to change your code. You can explore this more if you google it, but to give you a hint, to iterate through the rows of column "short description" you can do: `for each cell in worksheets("Sheet1").Listobjects("Table1").Listcolumns("short description").databodyrange.cells` and `next cell` where cell is a range object – Ibo Dec 04 '17 at 17:48
I prefer using a for loop through which gives you a lot more convenience to check your conditions etc: `for i=1 to tbl.listrows.count` where tbl is a `listobject` object – Ibo Dec 04 '17 at 17:50
Hi, I have encountered another scenario, I need to count the row if it matches the 2 strings I want, for example "follow" and req" I tried the this code but not working: `ElseIf StringContains2(items, "follow") And InStr(items, "req") Then` I changed the "and" to "&" "+" but none of them work. – Jonathan Dec 06 '17 at 00:00
Create another question for that – Ibo Dec 06 '17 at 02:53

score 0 · Answer 3 · answered Dec 04 '17 at 05:34

0

Delete the colon from after your Else and try again. See this answer for reference.

answered Dec 04 '17 at 05:34

garbb

679
5
9

hmm...I would recommend that you put some debug.print statements in your code which output the current cell's text and what it is counting it as and and see which ones it is counting incorrectly. Add `debug.print items` just after `items = Sheets("LoginPassword").Range("N" & row_number)` and add a `debug.print "name of case"` to each of your string matches in your If-then-elseif...etc Then you should be able to see in the lower part of the vba window what is going on – garbb Dec 04 '17 at 05:46

Excel VBA "else" statement counting wrongly

3 Answers3