If, Then, ElseIf doesn't return the right value

[Dustin S]

Sub ActiveMgmtT1()

Dim Multiplier As String
Dim Scenario As Integer
Dim ThisRateR1 As Variant
Dim ThisAmtA1, Initial As Long
Dim NewAmtM1, NewAmt2M1 As Long
Dim NewAmtM2, NewAmt2M2 As Long
Dim NewAmtM3, NewAmt2M3 As Long
Dim NewAmtM4, NewAmt2M4 As Long

Multiplier = Range("AA17").Value
Initial = Range("AB34").Value
Scenario = Range("AA18").Value
ThisAmtA1 = Range("AC34").Value
ThisRateR1 = Range("AD34").Value

NewAmtM1 = Initial - ThisAmtA1
NewAmt2M1 = ThisAmtA1 * ThisRateR1
NewAmtM2 = Initial - (Abs(ThisAmtA1) / ThisRateR1)
NewAmt2M2 = Initial - (Abs(ThisAmtA1) * ThisRateR1)
NewAmtM3 = Abs(Initial) - (ThisAmtA1 / ThisRateR1)
NewAmt2M3 = Initial - Abs(ThisAmtA1)
NewAmtM4 = Abs(Initial) - ThisAmtA1
NewAmt2M4 = Abs(ThisAmtA1) * ThisRateR1

If Multiplier = "M" And Scenario = 1 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM1 And Range("AC18").Value = NewAmt2M1
    
ElseIf Multiplier = "M" And Scenario = 2 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM2 And Range("AC18").Value = NewAmt2M2
    
ElseIf Multiplier = "M" And Scenario = 3 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM3 And Range("AC18").Value = NewAmt2M3
    
ElseIf Multiplier = "M" And Scenario = 4 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM4 And Range("AC18").Value = NewAmt2M4

Else
    Range("AB18").Value = 0 And Range("AC18").Value = 0

End If

End Sub

This is probably an easy fix, but every time the macro is run it returns 0, 0 in cells AB18 and AC18.

 

[VoG]

For one thing use Double not Long

Dim ThisAmtA1 As Double, Initial As Double

I imagine what you intended was

If Multiplier = "M" And Scenario = 1 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM1
    Range("AC18").Value = NewAmt2M1
End If

 

[Bagharmin]

Might also be a good idea to make sure that Multiplier is, in fact, equal to "M" and that ThisAmtA1 is > 0. Otherwise you're going to get 0 for both values every time.

 

[Dustin S]

Thanks VoG! That fixed it.

 

[JackDanIce]

Try:

Sub ActiveMgmtT1()
 
Dim Multiplier As String
Dim Scenario As Integer
Dim ThisRateR1 As Variant
Dim ThisAmtA1, Initial As Long
Dim NewAmtM1, NewAmt2M1 As Long
Dim NewAmtM2, NewAmt2M2 As Long
Dim NewAmtM3, NewAmt2M3 As Long
Dim NewAmtM4, NewAmt2M4 As Long
 
Application.ScreenUpdating = False
 
Multiplier = Range("AA17").Value
Initial = Range("AB34").Value
Scenario = Range("AA18").Value
ThisAmtA1 = Range("AC34").Value
ThisRateR1 = Range("AD34").Value
NewAmtM1 = Initial - ThisAmtA1
NewAmt2M1 = ThisAmtA1 * ThisRateR1
NewAmtM2 = Initial - (Abs(ThisAmtA1) / ThisRateR1)
NewAmt2M2 = Initial - (Abs(ThisAmtA1) * ThisRateR1)
NewAmtM3 = Abs(Initial) - (ThisAmtA1 / ThisRateR1)
NewAmt2M3 = Initial - Abs(ThisAmtA1)
NewAmtM4 = Abs(Initial) - ThisAmtA1
NewAmt2M4 = Abs(ThisAmtA1) * ThisRateR1
 
If Multiplier = "M" And ThisAmtA1 > 0 Then
 
    Select Case Scenario
        Case 1
            Range("AB18").Value = NewAmtM1
            Range("AC18").Value = NewAmt2M1
        Case 2
            Range("AB18").Value = NewAmtM2
            Range("AC18").Value = NewAmt2M2
        Case 3
            Range("AB18").Value = NewAmtM3
            Range("AC18").Value = NewAmt2M3
        Case 4
            Range("AB18").Value = NewAmtM4
            Range("AC18").Value = NewAmt2M4
        End Select
 
Else
 
    Range("AB18").Value = 0
    Range("AC18").Value = 0
 
End If
 
Application.ScreenUpdating = True
End Sub

 

[VoG]

Just for info, if you want both outcomes of an If in one line of code you can use

If Multiplier = "M" And Scenario = 1 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM1: Range("AC18").Value = NewAmt2M1

but this syntax is not always clear. I use it very sparingly in specific circumstances (e.g. Select Case statements).

 

[Dustin S]

Sub ActiveMgmtT1()

Dim Multiplier As String
Dim Scenario As Integer
Dim ThisRateR1 As Variant
Dim ThisAmtA1, Initial As Double
Dim NewAmtM1, NewAmt2M1 As Long
Dim NewAmtM2, NewAmt2M2 As Long
Dim NewAmtM3, NewAmt2M3 As Long
Dim NewAmtM4, NewAmt2M4 As Long
Dim NewAmtN, NewAmt2N As Variant

Multiplier = Range("AA17").Value
Initial = Range("AB34").Value
Scenario = Range("AA18").Value
ThisAmtA1 = Range("AC34").Value
ThisRateR1 = Range("AD34").Value

NewAmtM1 = Initial - ThisAmtA1
NewAmt2M1 = ThisAmtA1 * ThisRateR1
[COLOR="Blue"]NewAmtM2 = Initial - (Abs(ThisAmtA1) / ThisRateR1)[/COLOR]
NewAmt2M2 = Initial - (Abs(ThisAmtA1) * ThisRateR1)
NewAmtM3 = Abs(Initial) - (ThisAmtA1 / ThisRateR1)
NewAmt2M3 = Initial - Abs(ThisAmtA1)
NewAmtM4 = Abs(Initial) - ThisAmtA1
NewAmt2M4 = Abs(ThisAmtA1) * ThisRateR1
NewAmtN = Initial
NewAmt2N = Initial

If ThisAmtA1 = 0 Then
    Range("AB18").Value = NewAmtN
    Range("AC18").Value = NewAmt2N

ElseIf Multiplier = "M" And Scenario = 1 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM1
    Range("AC18").Value = NewAmt2M1
    
ElseIf Multiplier = "M" And Scenario = 2 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM2
    Range("AC18").Value = NewAmt2M2
    
ElseIf Multiplier = "M" And Scenario = 3 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM3
    Range("AC18").Value = NewAmt2M3
    
ElseIf Multiplier = "M" And Scenario = 4 And ThisAmtA1 > 0 Then
    Range("AB18").Value = NewAmtM4
    Range("AC18").Value = NewAmt2M4
    
End If

End Sub

I did some other adjustments, but now I get an Overflow error from the highlighted line above.

 

[Bagharmin]

It's possible, in that case, that ThisRateR1 might be 0.

 

[Dustin S]

Adding And ThisRateR1 = 0 to the first If condition returned the same error. Unless I'm missing something else.

 

[Bagharmin]

You have ThisRateR1 dim'd as Variant. Is it possible that ThisRateR1 is text? That would certainly cause issues if you were trying to divide by that. What exactly is in cell AD34?