Driving me mad...If..Then...Elseif issue

[Zakkala]

Hi there,

I've got the following code that works fine, other than one thing - the 'myadmintotalout' at the bottom does not calculate correctly. Instead of being a sum of 'myteamgout' and 'myteamhout' as shown, it always equals only the volume of 'myteamhout', even though the 'myteamgout' calculates correctly further up the code. This driving me batty - I really can't think what the problem is so any suggestions would be very helpful!

(ps - sorry, but I can't remember how to get the code to show in the usual code format!)

Sub out()

'calculate volumes

Windows("smboutgo.csv").Activate
Range("M1").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
Selection.AutoFill Destination:=Range("M1:M20"), Type:=xlFillDefault
Range("M1:M20").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

For i = 1 To 20

If Cells(i, "M") = "Team A" Then

myteamaout = Cells(i, "I").Value

ElseIf Cells(i, "M") = "Team B" Then

myteambout = Cells(i, "I").Value

ElseIf Cells(i, "M") = "Team C" Then

myteamcout = Cells(i, "I").Value

mymainout = myteamaout + myteambout + myteamcout

ElseIf Cells(i, "M") = "Team D" Then

myteamdout = Cells(i, "I").Value

ElseIf Cells(i, "M") = "Team E" Then

myteameout = Cells(i, "I").Value

ElseIf Cells(i, "M") = "Team F" Then

myteamfout = Cells(i, "I").Value

ElseIf Cells(i, "M") = "Team G" Then

myteamgout = Cells(i, "I").Value

ElseIf Cells(i, "M") = "Team H" Then

myteamhout = Cells(i, "I").Value

myadmintotalout = myteamgout + myteamhout

End If

Next i
End Sub

 

[xld]

Surely that is because the If ... Else loop only loads one of the variables. not both.

 

[Zakkala]

I really don't know! It works okay for the one further up, where it's adding 3 values to get an overall figure.

I couldn't work out how to do 'If .....=......OR......THEN SUM ......... so did it like this.

 

[jimrward]

try swapping the final total and the last end if so it looks like the following

ElseIf Cells(i, "M") = "Team H" Then

myteamhout = Cells(i, "I").Value

End if

myadmintotalout = myteamgout + myteamhout

Next i

 

[Zakkala]

Oh my goodness!

Thank you, that worked a treat. Why, oh why, do I never spot things like that!