Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Is If-Then Else limited to seven Elseif's??
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default 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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default 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.




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default 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.





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default 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.


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default 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.





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default 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.


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default 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.






Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default 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.






Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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.




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP is very limited Bobby Excel Worksheet Functions 7 October 18th 05 08:20 PM
Date limited Michael Excel Programming 2 July 10th 05 08:16 PM
Limited logins Bruise Excel Worksheet Functions 0 May 24th 05 12:51 AM
Limited Sumif Maarten Excel Discussion (Misc queries) 2 May 3rd 05 01:11 PM
Dir() limited to 48 files? Claude Excel Programming 2 October 30th 03 10:20 AM


All times are GMT +1. The time now is 11:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement