Nested VBA IF statements

S

scoha

Active Member
Joined
Jun 15, 2005
Messages
428
I cannot get the following nested IF statements to work and get the "Else without If" error for the first Else If statement. I had assumed the first "If" would apply to this

Code: 
Private Sub ReconcileNow()

If txtTValue = "" Then
Exit Sub
End If

If C1 = 1 Then
    If Minus = 1 Then
        txtTaC1 = CDbl(txtTpC1) - CDbl(txtTValue)
        txtTaC2 = ""
    Else
    If Minus = 0 Then
        txtTaC1 = CDbl(txtTpC1) + CDbl(txtTValue)
        txtTaC2 = ""
    End If
ElseIf C2 = 1 Then
    If Minus = 1 Then
        txtTaC2 = CDbl(txtTpC2) - CDbl(txtTValue)
        txtTaC1 = ""
    Else
    If Minus = 0 Then
        txtTaC2 = CDbl(txtTpC2) + CDbl(txtTValue)
        txtTaC1 = ""
    End If
Else
If Round = 1 Then
    If Minus = 1 Then
        txtTaR = CDbl(txtTaR) - CDbl(txtTValue)
    Else
    If Minus = 0 Then
        txtTaR = CDbl(txtTaR) + CDbl(txtTValue)
    End If
End If

End Sub

Can anyone spot where I got the code wrong?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Nice to see you indenting your IF statements (makes it way easier to read). Looks to me like you were missing a couple of End If statements. How does this work for you?
Code: 
Private Sub ReconcileNow()

If txtTValue = "" Then
Exit Sub
End If

If C1 = 1 Then
    If Minus = 1 Then
        txtTaC1 = CDbl(txtTpC1) - CDbl(txtTValue)
        txtTaC2 = ""
    Else
        If Minus = 0 Then
            txtTaC1 = CDbl(txtTpC1) + CDbl(txtTValue)
            txtTaC2 = ""
        End If
    End If
ElseIf C2 = 1 Then
    If Minus = 1 Then
        txtTaC2 = CDbl(txtTpC2) - CDbl(txtTValue)
        txtTaC1 = ""
    Else
        If Minus = 0 Then
            txtTaC2 = CDbl(txtTpC2) + CDbl(txtTValue)
            txtTaC1 = ""
        End If
    End If
End If
If Round = 1 Then
    If Minus = 1 Then
        txtTaR = CDbl(txtTaR) - CDbl(txtTValue)
    Else
        If Minus = 0 Then
            txtTaR = CDbl(txtTaR) + CDbl(txtTValue)
        End If
    End If
End If

End Sub

Note that the line that reads
Code: 
If Round = 1 Then
will cause you an error as Round is an Excel function.
 
Upvote 0
If you're If's after your Else's are on a separate line they are their own If-End blocks, not ElseIf blocks. Sounds confusing just trying to say it...

Code: 
If C1 = 1 Then
    If Minus = 1 Then
        txtTaC1 = CDbl(txtTpC1) - CDbl(txtTValue)
        txtTaC2 = ""
    Else
        If Minus = 0 Then
            txtTaC1 = CDbl(txtTpC1) + CDbl(txtTValue)
            txtTaC2 = ""
        End If
    End If
        
ElseIf C2 = 1 Then
    If Minus = 1 Then
        txtTaC2 = CDbl(txtTpC2) - CDbl(txtTValue)
        txtTaC1 = ""
    Else
        If Minus = 0 Then
            txtTaC2 = CDbl(txtTpC2) + CDbl(txtTValue)
            txtTaC1 = ""
        End If
    End If
Else
    If Round = 1 Then
        If Minus = 1 Then
            txtTaR = CDbl(txtTaR) - CDbl(txtTValue)
        Else
            If Minus = 0 Then
                txtTaR = CDbl(txtTaR) + CDbl(txtTValue)
            End If
        End If
    End If
End If
 
Upvote 0
Looks like you need to turn your

Else
If

into

ElseIf

something like
Code: 
If ... Then

ElseIf ... Then

ElseIf ... then

Else

End If
 
Last edited:
Upvote 0
using Select Case and ElseIfs can break up the monotony ;) - perhaps improving readability but I'm not sure as I'm halfway through a porter ...

Code: 
Select Case C1

    Case 1
        If Minus = 1 Then
            txtTaC1 = CDbl(txtTpC1) - CDbl(txtTValue)
            txtTaC2 = ""
        ElseIf Minus = 0 Then
            txtTaC1 = CDbl(txtTpC1) + CDbl(txtTValue)
            txtTaC2 = ""
        End If
            
    Case 2
        If Minus = 1 Then
            txtTaC2 = CDbl(txtTpC2) - CDbl(txtTValue)
            txtTaC1 = ""
        ElseIf Minus = 0 Then
            txtTaC2 = CDbl(txtTpC2) + CDbl(txtTValue)
            txtTaC1 = ""
        End If
        
    Case Else
        If Round = 1 Then
            If Minus = 1 Then
                txtTaR = CDbl(txtTaR) - CDbl(txtTValue)
            ElseIf Minus = 0 Then
                txtTaR = CDbl(txtTaR) + CDbl(txtTValue)
            End If
        End If

End Select

Edit - note - yes, with Peter just changing the Else's to ElseIfs (and moving the expression to be evaluated to the same line) should also work in the original code, I think too.
 
Upvote 0
Will Minus ALWAYS be a 1 or a 0??
If so you can do this:

Code: 
Private Sub ReconcileNow()
If txtTValue = "" Then
Exit Sub
End If
If C1 = 1 Then
    If Minus = 1 Then txtTaC1 = CDbl(txtTpC1) - CDbl(txtTValue)
    If Minus = 0 Then txtTaC1 = CDbl(txtTpC1) + CDbl(txtTValue)
    txtTaC2 = ""
ElseIf C2 = 1 Then
    If Minus = 1 Then txtTaC2 = CDbl(txtTpC2) - CDbl(txtTValue)
    If Minus = 0 Then txtTaC2 = CDbl(txtTpC2) + CDbl(txtTValue)
    txtTaC1 = ""
Else
If Round = 1 Then
    If Minus = 1 Then txtTaR = CDbl(txtTaR) - CDbl(txtTValue)
    If Minus = 0 Then txtTaR = CDbl(txtTaR) + CDbl(txtTValue)
End If
End Sub

If not you could do this:

Code: 
Private Sub ReconcileNow()
If txtTValue = "" Then
Exit Sub
End If
If C1 = 1 And Minus = 1 Then
    txtTaC1 = CDbl(txtTpC1) - CDbl(txtTValue)
    txtTaC2 = ""
ElseIf C1 = 1 And Minus = 0 Then
    txtTaC1 = CDbl(txtTpC1) + CDbl(txtTValue)
    txtTaC2 = ""
ElseIf C2 = 1 And Minus = 1 Then
    txtTaC2 = CDbl(txtTpC2) - CDbl(txtTValue)
    txtTaC1 = ""
ElseIf C2 = 1 And Minus = 0 Then
    txtTaC2 = CDbl(txtTpC2) + CDbl(txtTValue)
    txtTaC1 = ""
Else
    If Round = 1 And Minus = 1 Then
        txtTaR = CDbl(txtTaR) - CDbl(txtTValue)
    ElseIf Round = 1 And Minus = 0 Then
        txtTaR = CDbl(txtTaR) + CDbl(txtTValue)
    End If
End If
End Sub
 
Upvote 0
Even shorter if you are guaranteed that Minus will be 1 or 0 ;)
Code: 
Private Sub ReconcileNow()
If txtTValue = "" Then Exit Sub
multiplier = 1
If Minus = 1 Then multiplier = -1
If C1 = 1 Then
    txtTaC1 = CDbl(txtTpC1) + (CDbl(txtTValue) * multiplier)
    txtTaC2 = ""
ElseIf C2 = 1 Then
    txtTaC2 = CDbl(txtTpC2) + (CDbl(txtTValue) * multiplier)
    txtTaC1 = ""
ElseIf Round = 1 Then
    txtTaR = CDbl(txtTaR) + (CDbl(txtTValue) * multiplier)
End If
End Sub
 
Last edited:
Upvote 0
Thank you all for your prompt and informative responses - great suggestions all.

cheers
 
Upvote 0
A few comments:

1. 'Round' should not be used as a variable name since it is itself a function. (Edit: Sorry, just noticed Barrie mentioned this earlier)

2. First code in post #6 is missing an 'End If' just before the End Sub.

3. Not much different, but multiplier (good idea - post #7) could be set in one line:
Code: 
multiplier = IIf(Minus = 1, -1, 1)
 
Upvote 0
Thanks again PeterSSs - love the one liner improvement - I went with code in #7 which is very elegant
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Checking if any text has been entered
Replies
2
Views
204
Pacman52
P
D
  • Solved
Cdbl textBox showing as Text Error in Worksheet
Replies
4
Views
593
RoryA
RoryA
H
  • Question
VBA to Create a speaker object
Replies
0
Views
108
hsandeep
H
D
  • Question
UserForm_Initialize not being recognized in Commands
Replies
0
Views
470
Denny57
D
M
  • Solved
Sum range composing of text and numbers
Replies
2
Views
494
Z51
Z

Forum statistics

Threads
1,214,920
Messages
6,122,267
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top