mardi 6 janvier 2015

Error with ElseIf Statement

I need to extract values from a particular column line by line from TAB2 and then search for that value in 3 different columns and some more contraints in TAB1. I need to count based on the constraints specified and autofill 4columns next to the corresponding value in TAB2.


I have the following code where I get the value when I click a button and then perform the operation, search, and count based on the condition specified.:



Option Compare Text
Sub SO()
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 String, yr2 As String
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
ThisWorkbook.Activate
Set ws = Worksheets("extract")
With ws
For i = 2 To 15505
If InStr(Cells(i, 1).Value, group) > 0 And _
InStr(Cells(i, 2).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 Or _
InStr(Cells(i, 3).Value, inc) > 0 And _
InStr(Cells(i, 14).Value, yr1) > 0 Then ciyr1 = ciyr1 + 1 'to check if value at cells(i,14) is anything that starts with "2013". (the value being present are 2013/01, 2013/02, etc..)
ElseIf InStr(Cells(i, 1).Value, group) > 0 And _ 'I get a error with a message: else without If
InStr(Cells(i, 2).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 Or _
InStr(Cells(i, 3).Value, inc) > 0 And _
InStr(Cells(i, 14).Value, yr2) > 0 Then ciyr2 = ciyr2 + 1
ElseIf InStr(Cells(i, 1).Value, group) > 0 And _
InStr(Cells(i, 2).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 Or _
InStr(Cells(i, 3).Value, sr) > 0 And _
InStr(Cells(i, 14).Value, yr1) > 0 Then csyr1 = csyr1 + 1
ElseIf InStr(Cells(i, 1).Value, group) > 0 And _
InStr(Cells(i, 2).Value, inputString) > 0 Or _
InStr(Cells(i, 10).Value, inputString) > 0 Or _
InStr(Cells(i, 11).Value, inputString) > 0 Or _
InStr(Cells(i, 3).Value, sr) > 0 And _
InStr(Cells(i, 14).Value, yr2) > 0 Then csyr2 = csyr2 + 1
Else:
MsgBox "No matches found!!"
End If
Next i
End With

If ciyr1 < 0 Or _
ciyr2 < 0 Or _
csyr1 < 0 Or _
csyr2 < 0 Then

MsgBox "Number of matches for Inc 2013" & inputString & ciyr1
MsgBox "Number of matches for Inc 2014" & inputString & ciyr2
MsgBox "Number of matches for SR 2013" & inputString & csyr1
MsgBox "Number of matches for SR 2014" & inputString & csyr2

End If
End Sub


Can someone please rectify the mistakes I have done in the above code


Also can anyone tell me how to take values from the one tab and then autofill the values after performing the loop and conditions


Aucun commentaire:

Enregistrer un commentaire