CODEWITHSUNDEEP
excelvbaif-statement
ladymrt
ladymrt

Reputation: 95

VBA If Statement keeps running through ifs

This if statement is supposed to be looking at a cell and performing an action based on what's in that cell. If I just keep the first line of the statement, the code works and it only does what I want it to do for that situation. However, if I add multiple "tasks" after the "Then", it just runs all the way through the whole program. I know I'm missing some syntax here that will make it do "If [this] Then [this, this and this] ElseIf [this] Then [this this and this].

Making the second-sixth "If"'s into "ElseIf" gives me the error "ElseIf without block If"

Let me know what I'm missing, I'm guessing some iteration of For, Next etc. but I'm not familiar with that function.

Sub trythis()



Dim tol As String
Dim formblah As String


tol = Range("I7").Value
formblah = "=IF(D21>C21+" & tol & ",""FAIL"",IF(D21<C21+" & tol & ",""PASS"",IF(D21=C21+" & tol & ",""PASS-BONUS"",""N/A"")))"

If Sheets("Caliper").Range("C5").Value = 1 Then Rows("21:26").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("E21:E26").Value = formblah
    Range("C21") = 1
    Range("C21").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
    Step:=1, Stop:=6, Trend:=False




ElseIf Sheets("Caliper").Range("C5").Value = 2 Then Rows("21:28").Insert 
Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("E21:E28").Value = formblah
    Range("C21") = 1
    Range("C21").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
    Step:=1, Stop:=8, Trend:=False

Upvotes: 1

Views: 466

Answers (2)

DisplayName
DisplayName

Reputation: 13386

as already answered, you have to keep all statements inside the If-Else and Else-End If blocks

but you could:

  • adopt a Select Case block, to handle more cases and achieve more readability

  • keep the real code outside the If-Then-Else-End If (or Select Case-End Select) block where to just set relevant changing variables

as follows:

Sub trythis()

    Dim tol As String
    Dim formblah As String

    tol = Range("I7").Value
    formblah = "=IF(D21>C21" & tol & ",""FAIL"",IF(D21<C21" & tol & ",""PASS"",IF(D21=C21" & tol & ",""PASS-BONUS"",""N/A"")))"

    Dim nRows As Long
    Select Case Sheets("Caliper").Range("C5").Value
        Case 1
            nRows = 6
        Case 2
            nRows = 8
        Case 3
            ' if needed, add you case handling
        Case Else
            ' if needed, add you case handling
    End Select

    Rows("21").Resize(nRows).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("E21").Resize(nRows).Formula = formblah
    Range("C21").Value = 1
    Range("C21").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Stop:=6, Trend:=False

End Sub

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56735

For multi-line IFs you cannot have a statement immediately after the Then, only inside the block. Also, you need an End If

Like this:

If Sheets("Caliper").Range("C5").Value = 1 Then 
    Rows("21:26").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("E21:E26").Value = formblah
    Range("C21") = 1
    Range("C21").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
    Step:=1, Stop:=6, Trend:=False

ElseIf Sheets("Caliper").Range("C5").Value = 2 Then 
    Rows("21:28").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("E21:E28").Value = formblah
    Range("C21") = 1
    Range("C21").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
    Step:=1, Stop:=8, Trend:=False
End If

Also, see her: https://www.techonthenet.com/excel/formulas/if_then.php

Upvotes: 4

Related Questions