|
Is If-Then Else limited to seven Elseif's??
I seem to be running into the same limit as I would with a function of nested
ifs. |
Is If-Then Else limited to seven Elseif's??
Perhaps it would help to post your code.
-- Don Guillett SalesAid Software "rudawg" wrote in message ... I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
Seven is the limit... This link might be able to help you...
http://www.cpearson.com/excel/nested.htm -- HTH... Jim Thomlinson "rudawg" wrote: I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
Thanks Don,
I have 10 conditions. The code is long and probably inefficient. But here goes..... Private Sub Worksheet_SelectionChange2(ByVal Target As Range) Dim Left_Value, Right_Value As Variant Set Left_Value = Nothing Set Right_Value = Nothing If Target.Column = 13 Then '<----M If Target.Row 8 Then If Target.Row < 70 Then Left_Value = ActiveCell.Offset([-1], [-1]).Text Right_Value = ActiveCell.Offset([1], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 14 Then '<----N If Target.Row 10 Then If Target.Row < 68 Then Left_Value = ActiveCell.Offset([-2], [-1]).Text Right_Value = ActiveCell.Offset([2], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 15 Then '<----O If Target.Row 14 Then If Target.Row < 64 Then Left_Value = ActiveCell.Offset([-4], [-1]).Text Right_Value = ActiveCell.Offset([4], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 16 Then '<----P If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [-1]).Text Right_Value = ActiveCell.Offset([8], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 18 Then '<----R If Target.Row 29 Then If Target.Row < 31 Then Left_Value = ActiveCell.Offset([-7], [-2]).Text Right_Value = ActiveCell.Offset([25], [-2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 20 Then '<----T If Target.Row 47 Then If Target.Row < 49 Then Left_Value = ActiveCell.Offset([-25], [2]).Text Right_Value = ActiveCell.Offset([7], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 22 Then '<----V If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [2]).Text Right_Value = ActiveCell.Offset([8], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If 'ElseIf Target.Column = 24 Then '<----X 'If Target.Row 14 Then 'If Target.Row < 64 Then 'Left_Value = ActiveCell.Offset([-4], [1]).Text 'Right_Value = ActiveCell.Offset([4], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 25 Then '<----Y 'If Target.Row 10 Then 'If Target.Row < 68 Then 'Left_Value = ActiveCell.Offset([-2], [1]).Text 'Right_Value = ActiveCell.Offset([2], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 26 Then '<----Z 'If Target.Row 8 Then 'If Target.Row < 70 Then 'Left_Value = ActiveCell.Offset([-1], [1]).Text 'Right_Value = ActiveCell.Offset([1], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If End If End Sub "Don Guillett" wrote: Perhaps it would help to post your code. -- Don Guillett SalesAid Software "rudawg" wrote in message ... I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
No - as an example
Sub ABCD() v = Int(Rnd() * 12 + 1) If v = 1 Then MsgBox 1 ElseIf v = 2 Then MsgBox 2 ElseIf v = 3 Then MsgBox 3 ElseIf v = 4 Then MsgBox 4 ElseIf v = 5 Then MsgBox 5 ElseIf v = 6 Then MsgBox 6 ElseIf v = 7 Then MsgBox 7 ElseIf v = 8 Then MsgBox 8 ElseIf v = 9 Then MsgBox 9 ElseIf v = 10 Then MsgBox 10 Else MsgBox "10" End If End Sub works fine. -- Regards, Tom Ogilvy "rudawg" wrote: I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
"Tom Ogilvy" wrote: No - as an example Sub ABCD() v = Int(Rnd() * 12 + 1) If v = 1 Then MsgBox 1 ElseIf v = 2 Then MsgBox 2 ElseIf v = 3 Then MsgBox 3 ElseIf v = 4 Then MsgBox 4 ElseIf v = 5 Then MsgBox 5 ElseIf v = 6 Then MsgBox 6 ElseIf v = 7 Then MsgBox 7 ElseIf v = 8 Then MsgBox 8 ElseIf v = 9 Then MsgBox 9 ElseIf v = 10 Then MsgBox 10 Else MsgBox "10" End If End Sub works fine. -- Regards, Tom Ogilvy "rudawg" wrote: I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
I have no trouble getting more than 7 If...Then...Else options. Without
knowing exactly what you're doing, here's one alternative: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim vArray As Variant Dim i As Long Dim nBase As Long vArray = Array(Array(13, 8, 70, -1, -1, 1, -1), _ Array(14, 10, 68, -2, -1, 2, -1), _ Array(15, 14, 64, -4, -1, 4, -1), _ Array(16, 22, 56, -8, -1, 8, -1), _ Array(18, 29, 31, -7, -2, 25, -2), _ Array(20, 47, 49, -25, 2, 7, 2), _ Array(22, 22, 56, -8, 2, 8, 2), _ Array(24, 14, 64, -4, 1, 4, 1), _ Array(25, 10, 68, -2, 1, 2, 1), _ Array(26, 8, 70, -1, 1, 1, 1)) nBase = LBound(vArray) With Target For i = nBase To UBound(vArray) If .Column = vArray(i)(nBase) Then If .Row vArray(i)(nBase + 1) Then If .Row < vArray(i)(nBase + 2) Then Range("B2").Value = _ .Offset(vArray(i)(nBase + 3), _ vArray(i)(nBase + 4)).Text Range("AF2").Value = _ .Offset(vArray(i)(nBase + 5), _ vArray(i)(nBase + 6)).Text Exit For End If End If End If Next i End With End Sub You can extend this to include more columns if desired by adding additional Array() statements inside the enclosing Array(...). A couple of notes about your macro: 1) Left_Value will be declared as a Variant, but only because you didn't provide an explicit type. Unlike C, for example, each variable on a line has to have a type declaration or it's declared variant. For example: Dim Left_Value, Right_Value As Double declares Right_Value to be a Double, but Left_Value to be a Variant. Instead, Dim Left_Value As Double, Right_Value As Double declares both to be Doubles. 2) Using the Evaluate method (i.e., [B2]) is slower than using the Range Method (i.e., Range("B2")). 3) There's never a reason to use the evaluate method with constants, e.g., [-1]. 4) Use "Set" only with object variables (or variants which will refer to objects). In this case Left_Value and Right_Value will be assigned Text values (so you could type both with Dim Left_Value As String, Right_Value As String Also, since you're assigning values to the variables in the loop, there's no real reason to clear them before assignment. In article , rudawg wrote: Thanks Don, I have 10 conditions. The code is long and probably inefficient. But here goes..... Private Sub Worksheet_SelectionChange2(ByVal Target As Range) Dim Left_Value, Right_Value As Variant Set Left_Value = Nothing Set Right_Value = Nothing If Target.Column = 13 Then '<----M If Target.Row 8 Then If Target.Row < 70 Then Left_Value = ActiveCell.Offset([-1], [-1]).Text Right_Value = ActiveCell.Offset([1], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 14 Then '<----N If Target.Row 10 Then If Target.Row < 68 Then Left_Value = ActiveCell.Offset([-2], [-1]).Text Right_Value = ActiveCell.Offset([2], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 15 Then '<----O If Target.Row 14 Then If Target.Row < 64 Then Left_Value = ActiveCell.Offset([-4], [-1]).Text Right_Value = ActiveCell.Offset([4], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 16 Then '<----P If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [-1]).Text Right_Value = ActiveCell.Offset([8], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 18 Then '<----R If Target.Row 29 Then If Target.Row < 31 Then Left_Value = ActiveCell.Offset([-7], [-2]).Text Right_Value = ActiveCell.Offset([25], [-2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 20 Then '<----T If Target.Row 47 Then If Target.Row < 49 Then Left_Value = ActiveCell.Offset([-25], [2]).Text Right_Value = ActiveCell.Offset([7], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 22 Then '<----V If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [2]).Text Right_Value = ActiveCell.Offset([8], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If 'ElseIf Target.Column = 24 Then '<----X 'If Target.Row 14 Then 'If Target.Row < 64 Then 'Left_Value = ActiveCell.Offset([-4], [1]).Text 'Right_Value = ActiveCell.Offset([4], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 25 Then '<----Y 'If Target.Row 10 Then 'If Target.Row < 68 Then 'Left_Value = ActiveCell.Offset([-2], [1]).Text 'Right_Value = ActiveCell.Offset([2], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 26 Then '<----Z 'If Target.Row 8 Then 'If Target.Row < 70 Then 'Left_Value = ActiveCell.Offset([-1], [1]).Text 'Right_Value = ActiveCell.Offset([1], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If End If End Sub "Don Guillett" wrote: Perhaps it would help to post your code. -- Don Guillett SalesAid Software "rudawg" wrote in message ... I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
Tom,
It seems my last reply got lost. What I can't figure out is why the following code does not work. Can you see anything obvious that would be giving me trouble? I am using windows XP and Excel 2003. The error I get is "Type mismatch (Error 13)" I have re-posted my code for better visibility. I am sure it is not the most efficient or elegant approach but I am very new to VBA. Can you offer any suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Left_Value, Right_Value As Variant Set Left_Value = Nothing Set Right_Value = Nothing If Target.Column = 13 Then '<----M Left_Value = ActiveCell.Offset([-1], [-1]).Text Right_Value = ActiveCell.Offset([+1], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 14 Then '<----N Left_Value = ActiveCell.Offset([-2], [-1]).Text Right_Value = ActiveCell.Offset([+2], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 15 Then '<----O Left_Value = ActiveCell.Offset([-4], [-1]).Text Right_Value = ActiveCell.Offset([+4], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 16 Then '<----P Left_Value = ActiveCell.Offset([-8], [-1]).Text Right_Value = ActiveCell.Offset([+8], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 18 Then '<----R Left_Value = ActiveCell.Offset([-7], [-2]).Text Right_Value = ActiveCell.Offset([+25], [-2]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 20 Then '<----T Left_Value = ActiveCell.Offset([-25], [2]).Text Right_Value = ActiveCell.Offset([+7], [2]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 22 Then '<----V Left_Value = ActiveCell.Offset([-8], [2]).Text Right_Value = ActiveCell.Offset([+8], [2]).Text [b2] = Left_Value [af2] = Right_Value ElseIf Target.Column = 24 Then '<----X Left_Value = ActiveCell.Offset([-4], [1]).Text Right_Value = ActiveCell.Offset([+4], [1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 25 Then '<----Y Left_Value = ActiveCell.Offset([-2], [1]).Text Right_Value = ActiveCell.Offset([2], [1]).Text [b2] = Left_Value [af2] = Right_Value ElseIf Target.Column = 26 Then '<----Z Left_Value = ActiveCell.Offset([-1], [1]).Text Right_Value = ActiveCell.Offset([1], [1]).Text [b2] = Left_Value [af2] = Right_Value End If End Sub Thanks, "Tom Ogilvy" wrote: No - as an example Sub ABCD() v = Int(Rnd() * 12 + 1) If v = 1 Then MsgBox 1 ElseIf v = 2 Then MsgBox 2 ElseIf v = 3 Then MsgBox 3 ElseIf v = 4 Then MsgBox 4 ElseIf v = 5 Then MsgBox 5 ElseIf v = 6 Then MsgBox 6 ElseIf v = 7 Then MsgBox 7 ElseIf v = 8 Then MsgBox 8 ElseIf v = 9 Then MsgBox 9 ElseIf v = 10 Then MsgBox 10 Else MsgBox "10" End If End Sub works fine. -- Regards, Tom Ogilvy "rudawg" wrote: I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
Thanks JE. I will try to incorporate your advice and post back. It might
take a bit of time because it looks like a lot of greek to me now but please stay tuned. I would appreciate your opinion on how I've used what you suggest. Thanks "JE McGimpsey" wrote: I have no trouble getting more than 7 If...Then...Else options. Without knowing exactly what you're doing, here's one alternative: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim vArray As Variant Dim i As Long Dim nBase As Long vArray = Array(Array(13, 8, 70, -1, -1, 1, -1), _ Array(14, 10, 68, -2, -1, 2, -1), _ Array(15, 14, 64, -4, -1, 4, -1), _ Array(16, 22, 56, -8, -1, 8, -1), _ Array(18, 29, 31, -7, -2, 25, -2), _ Array(20, 47, 49, -25, 2, 7, 2), _ Array(22, 22, 56, -8, 2, 8, 2), _ Array(24, 14, 64, -4, 1, 4, 1), _ Array(25, 10, 68, -2, 1, 2, 1), _ Array(26, 8, 70, -1, 1, 1, 1)) nBase = LBound(vArray) With Target For i = nBase To UBound(vArray) If .Column = vArray(i)(nBase) Then If .Row vArray(i)(nBase + 1) Then If .Row < vArray(i)(nBase + 2) Then Range("B2").Value = _ .Offset(vArray(i)(nBase + 3), _ vArray(i)(nBase + 4)).Text Range("AF2").Value = _ .Offset(vArray(i)(nBase + 5), _ vArray(i)(nBase + 6)).Text Exit For End If End If End If Next i End With End Sub You can extend this to include more columns if desired by adding additional Array() statements inside the enclosing Array(...). A couple of notes about your macro: 1) Left_Value will be declared as a Variant, but only because you didn't provide an explicit type. Unlike C, for example, each variable on a line has to have a type declaration or it's declared variant. For example: Dim Left_Value, Right_Value As Double declares Right_Value to be a Double, but Left_Value to be a Variant. Instead, Dim Left_Value As Double, Right_Value As Double declares both to be Doubles. 2) Using the Evaluate method (i.e., [B2]) is slower than using the Range Method (i.e., Range("B2")). 3) There's never a reason to use the evaluate method with constants, e.g., [-1]. 4) Use "Set" only with object variables (or variants which will refer to objects). In this case Left_Value and Right_Value will be assigned Text values (so you could type both with Dim Left_Value As String, Right_Value As String Also, since you're assigning values to the variables in the loop, there's no real reason to clear them before assignment. In article , rudawg wrote: Thanks Don, I have 10 conditions. The code is long and probably inefficient. But here goes..... Private Sub Worksheet_SelectionChange2(ByVal Target As Range) Dim Left_Value, Right_Value As Variant Set Left_Value = Nothing Set Right_Value = Nothing If Target.Column = 13 Then '<----M If Target.Row 8 Then If Target.Row < 70 Then Left_Value = ActiveCell.Offset([-1], [-1]).Text Right_Value = ActiveCell.Offset([1], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 14 Then '<----N If Target.Row 10 Then If Target.Row < 68 Then Left_Value = ActiveCell.Offset([-2], [-1]).Text Right_Value = ActiveCell.Offset([2], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 15 Then '<----O If Target.Row 14 Then If Target.Row < 64 Then Left_Value = ActiveCell.Offset([-4], [-1]).Text Right_Value = ActiveCell.Offset([4], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 16 Then '<----P If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [-1]).Text Right_Value = ActiveCell.Offset([8], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 18 Then '<----R If Target.Row 29 Then If Target.Row < 31 Then Left_Value = ActiveCell.Offset([-7], [-2]).Text Right_Value = ActiveCell.Offset([25], [-2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 20 Then '<----T If Target.Row 47 Then If Target.Row < 49 Then Left_Value = ActiveCell.Offset([-25], [2]).Text Right_Value = ActiveCell.Offset([7], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 22 Then '<----V If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [2]).Text Right_Value = ActiveCell.Offset([8], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If 'ElseIf Target.Column = 24 Then '<----X 'If Target.Row 14 Then 'If Target.Row < 64 Then 'Left_Value = ActiveCell.Offset([-4], [1]).Text 'Right_Value = ActiveCell.Offset([4], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 25 Then '<----Y 'If Target.Row 10 Then 'If Target.Row < 68 Then 'Left_Value = ActiveCell.Offset([-2], [1]).Text 'Right_Value = ActiveCell.Offset([2], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 26 Then '<----Z 'If Target.Row 8 Then 'If Target.Row < 70 Then 'Left_Value = ActiveCell.Offset([-1], [1]).Text 'Right_Value = ActiveCell.Offset([1], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If End If End Sub "Don Guillett" wrote: Perhaps it would help to post your code. -- Don Guillett SalesAid Software "rudawg" wrote in message ... I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
JE,
Thank you. It works like a charm. All I had to do was past it into the VB editor. I don't yet understand why it works but I'll try to reverse engineer it and maybe learn something. Thanks again Rudy "JE McGimpsey" wrote: I have no trouble getting more than 7 If...Then...Else options. Without knowing exactly what you're doing, here's one alternative: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim vArray As Variant Dim i As Long Dim nBase As Long vArray = Array(Array(13, 8, 70, -1, -1, 1, -1), _ Array(14, 10, 68, -2, -1, 2, -1), _ Array(15, 14, 64, -4, -1, 4, -1), _ Array(16, 22, 56, -8, -1, 8, -1), _ Array(18, 29, 31, -7, -2, 25, -2), _ Array(20, 47, 49, -25, 2, 7, 2), _ Array(22, 22, 56, -8, 2, 8, 2), _ Array(24, 14, 64, -4, 1, 4, 1), _ Array(25, 10, 68, -2, 1, 2, 1), _ Array(26, 8, 70, -1, 1, 1, 1)) nBase = LBound(vArray) With Target For i = nBase To UBound(vArray) If .Column = vArray(i)(nBase) Then If .Row vArray(i)(nBase + 1) Then If .Row < vArray(i)(nBase + 2) Then Range("B2").Value = _ .Offset(vArray(i)(nBase + 3), _ vArray(i)(nBase + 4)).Text Range("AF2").Value = _ .Offset(vArray(i)(nBase + 5), _ vArray(i)(nBase + 6)).Text Exit For End If End If End If Next i End With End Sub You can extend this to include more columns if desired by adding additional Array() statements inside the enclosing Array(...). A couple of notes about your macro: 1) Left_Value will be declared as a Variant, but only because you didn't provide an explicit type. Unlike C, for example, each variable on a line has to have a type declaration or it's declared variant. For example: Dim Left_Value, Right_Value As Double declares Right_Value to be a Double, but Left_Value to be a Variant. Instead, Dim Left_Value As Double, Right_Value As Double declares both to be Doubles. 2) Using the Evaluate method (i.e., [B2]) is slower than using the Range Method (i.e., Range("B2")). 3) There's never a reason to use the evaluate method with constants, e.g., [-1]. 4) Use "Set" only with object variables (or variants which will refer to objects). In this case Left_Value and Right_Value will be assigned Text values (so you could type both with Dim Left_Value As String, Right_Value As String Also, since you're assigning values to the variables in the loop, there's no real reason to clear them before assignment. In article , rudawg wrote: Thanks Don, I have 10 conditions. The code is long and probably inefficient. But here goes..... Private Sub Worksheet_SelectionChange2(ByVal Target As Range) Dim Left_Value, Right_Value As Variant Set Left_Value = Nothing Set Right_Value = Nothing If Target.Column = 13 Then '<----M If Target.Row 8 Then If Target.Row < 70 Then Left_Value = ActiveCell.Offset([-1], [-1]).Text Right_Value = ActiveCell.Offset([1], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 14 Then '<----N If Target.Row 10 Then If Target.Row < 68 Then Left_Value = ActiveCell.Offset([-2], [-1]).Text Right_Value = ActiveCell.Offset([2], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 15 Then '<----O If Target.Row 14 Then If Target.Row < 64 Then Left_Value = ActiveCell.Offset([-4], [-1]).Text Right_Value = ActiveCell.Offset([4], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 16 Then '<----P If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [-1]).Text Right_Value = ActiveCell.Offset([8], [-1]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 18 Then '<----R If Target.Row 29 Then If Target.Row < 31 Then Left_Value = ActiveCell.Offset([-7], [-2]).Text Right_Value = ActiveCell.Offset([25], [-2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 20 Then '<----T If Target.Row 47 Then If Target.Row < 49 Then Left_Value = ActiveCell.Offset([-25], [2]).Text Right_Value = ActiveCell.Offset([7], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If ElseIf Target.Column = 22 Then '<----V If Target.Row 22 Then If Target.Row < 56 Then Left_Value = ActiveCell.Offset([-8], [2]).Text Right_Value = ActiveCell.Offset([8], [2]).Text [b2] = Left_Value [af2] = Right_Value End If End If 'ElseIf Target.Column = 24 Then '<----X 'If Target.Row 14 Then 'If Target.Row < 64 Then 'Left_Value = ActiveCell.Offset([-4], [1]).Text 'Right_Value = ActiveCell.Offset([4], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 25 Then '<----Y 'If Target.Row 10 Then 'If Target.Row < 68 Then 'Left_Value = ActiveCell.Offset([-2], [1]).Text 'Right_Value = ActiveCell.Offset([2], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If 'ElseIf Target.Column = 26 Then '<----Z 'If Target.Row 8 Then 'If Target.Row < 70 Then 'Left_Value = ActiveCell.Offset([-1], [1]).Text 'Right_Value = ActiveCell.Offset([1], [1]).Text '[b2] = Left_Value '[af2] = Right_Value 'End If 'End If End If End Sub "Don Guillett" wrote: Perhaps it would help to post your code. -- Don Guillett SalesAid Software "rudawg" wrote in message ... I seem to be running into the same limit as I would with a function of nested ifs. |
Is If-Then Else limited to seven Elseif's??
I would eliminate the lines indicated
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Left_Value as Variant, Right_Value As Variant 'Set Left_Value = Nothing '<= eliminate 'Set Right_Value = Nothing '<= eliminate set should only be used with object and you are not using Left_Value and Right_Value as Objects. -- Regards, Tom Ogilvy "rudawg" wrote in message ... Tom, It seems my last reply got lost. What I can't figure out is why the following code does not work. Can you see anything obvious that would be giving me trouble? I am using windows XP and Excel 2003. The error I get is "Type mismatch (Error 13)" I have re-posted my code for better visibility. I am sure it is not the most efficient or elegant approach but I am very new to VBA. Can you offer any suggestions? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Left_Value, Right_Value As Variant Set Left_Value = Nothing Set Right_Value = Nothing If Target.Column = 13 Then '<----M Left_Value = ActiveCell.Offset([-1], [-1]).Text Right_Value = ActiveCell.Offset([+1], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 14 Then '<----N Left_Value = ActiveCell.Offset([-2], [-1]).Text Right_Value = ActiveCell.Offset([+2], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 15 Then '<----O Left_Value = ActiveCell.Offset([-4], [-1]).Text Right_Value = ActiveCell.Offset([+4], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 16 Then '<----P Left_Value = ActiveCell.Offset([-8], [-1]).Text Right_Value = ActiveCell.Offset([+8], [-1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 18 Then '<----R Left_Value = ActiveCell.Offset([-7], [-2]).Text Right_Value = ActiveCell.Offset([+25], [-2]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 20 Then '<----T Left_Value = ActiveCell.Offset([-25], [2]).Text Right_Value = ActiveCell.Offset([+7], [2]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 22 Then '<----V Left_Value = ActiveCell.Offset([-8], [2]).Text Right_Value = ActiveCell.Offset([+8], [2]).Text [b2] = Left_Value [af2] = Right_Value ElseIf Target.Column = 24 Then '<----X Left_Value = ActiveCell.Offset([-4], [1]).Text Right_Value = ActiveCell.Offset([+4], [1]).Text [b2] = Left_Value [af2] = Right_Value ' ElseIf Target.Column = 25 Then '<----Y Left_Value = ActiveCell.Offset([-2], [1]).Text Right_Value = ActiveCell.Offset([2], [1]).Text [b2] = Left_Value [af2] = Right_Value ElseIf Target.Column = 26 Then '<----Z Left_Value = ActiveCell.Offset([-1], [1]).Text Right_Value = ActiveCell.Offset([1], [1]).Text [b2] = Left_Value [af2] = Right_Value End If End Sub Thanks, "Tom Ogilvy" wrote: No - as an example Sub ABCD() v = Int(Rnd() * 12 + 1) If v = 1 Then MsgBox 1 ElseIf v = 2 Then MsgBox 2 ElseIf v = 3 Then MsgBox 3 ElseIf v = 4 Then MsgBox 4 ElseIf v = 5 Then MsgBox 5 ElseIf v = 6 Then MsgBox 6 ElseIf v = 7 Then MsgBox 7 ElseIf v = 8 Then MsgBox 8 ElseIf v = 9 Then MsgBox 9 ElseIf v = 10 Then MsgBox 10 Else MsgBox "10" End If End Sub works fine. -- Regards, Tom Ogilvy "rudawg" wrote: I seem to be running into the same limit as I would with a function of nested ifs. |
| All times are GMT +1. The time now is 08:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com