CODEWITHSUNDEEP
vbaexcelif-statement
AnRu
AnRu

Reputation: 67

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

Upvotes: 0

Views: 54

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

By putting your "what to do after an IF" code of ciyr1 = ciyr1 + 1 on THE SAME LINE as the Then, you have effectively ENDED the IF block. This shortcut is useful if you don't have other conditions and allows you to skip writing End If. To rectify, simply add a return after the Then and move ciry1 = ciyr + 1 to a new line. You will need to do this for all your Then lines.

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

Upvotes: 1

Related Questions