mercredi 7 janvier 2015

If/ElseIf statement giving wrong output

I need to get 4 counts based on my conditions given. But I get same answer for all the cases. Please verify the following code. I'm new to VBA and just started learning



Dim cCount As Long, ciyr1 As Long, ciyr2 As Long, csyr2 As Long
Dim i As Long, inputString As String, group As String
Dim yr1 As Long, yr2 As Long, year As Long
Dim inc As String, sr As String
inputString = InputBox("Enter Application Name:", "Input Box Text")
group = "gbl cecc sustainment"
yr1 = 2013
yr2 = 2014
inc = "Incident"
sr = "Request"
cCount = 0
ciyr1 = 0
ciyr2 = 0
csyr1 = 0
csyr2 = 0 'initialisation and declaration over
For i = 2 To 15505
If InStr(Cells(i, 1).Value, group) > 0 And _
InStr(Cells(i, 2).Value, inc) > 0 And _
InStr(Cells(i, 3).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 And _
InStr(Cells(i, 14).Value, yr1) > 0 Then '
ciyr1 = ciyr1 + 1 ' I want to count the number of rows where it exactly matches the group name, inc value and year2013

ElseIf InStr(Cells(i, 1).Value, group) > 0 And _
InStr(Cells(i, 2).Value, inc) > 0 And _
InStr(Cells(i, 3).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 And _
InStr(Cells(i, 14).Value, yr2) > 0 Then
ciyr2 = ciyr2 + 1 ' I want to count the number of rows where it exactly matches the group name, inc value and year2014
ElseIf InStr(Cells(i, 1).Value, group) > 0 And _
InStr(Cells(i, 2).Value, sr) > 0 And _
InStr(Cells(i, 3).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 And _
InStr(Cells(i, 14).Value, yr1) > 0 Then
csyr1 = csyr1 + 1 '' I want to count the number of rows where it exactly matches the group name, sr value and year2013
ElseIf InStr(Cells(i, 1).Value, group) > 0 And _
InStr(Cells(i, 2).Value, sr) > 0 And _
InStr(Cells(i, 3).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 And _
InStr(Cells(i, 14).Value, yr2) > 0 Then
csyr2 = csyr2 + 1 ' ' I want to count the number of rows where it exactly matches the group name, sr value and year2014
End If
Next i


i'm getting the same value when trying to print all the summed up values



If ciyr1 > 0 Or _
ciyr2 > 0 Or _
csyr1 > 0 Or _
csyr2 > 0 Then
'MsgBox "year" & inputString & ciyr1
MsgBox "Number of matches for Inc 2013 and for " & inputString & ciyr1
MsgBox "Number of matches for Inc 2014 and for " & inputString & ciyr2
MsgBox "Number of matches for SR 2013 and for " & inputString & csyr1
MsgBox "Number of matches for SR 2014 and for " & inputString & csyr2
Else
MsgBox "No matches found!!"


Please help me End If


Aucun commentaire:

Enregistrer un commentaire