0

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.

enter image description here

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
Improve this question
1
  • 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. Commented Dec 4, 2017 at 5:48

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
Uh, I don't think StringContains2() is a valid function?
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
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.

Improve this answer

6 Comments

write count_of_all = 0 after row_number = row_number + 1. You shoudl do this with other counters too
I tried it but it's still giving the wrong results.
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
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
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.
|
0

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

Improve this answer

1 Comment

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.