Select case help

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I'm trying to convert the following to Select Case. As I have 8 tests in
this section alone and more in various other places, I feel to change to
Select Case will speed up the procedures. Please tell me if that's not so.

If ListBox1.ListIndex = 0 Then Range("N5") = True
If ListBox1.ListIndex = 1 Then Range("N6") = True
If ListBox1.ListIndex = 2 Then Range("N7") = True

This is my effort but it doesn't work. Can someone please tell me what's
wrong here?
(I've also tried Select Case ListBox1.ListIndex.Value without success)

Select Case ListBox1.ListIndex
Case ListBox1.ListIndex = 0
Range("N5") = True
Case ListBox1.ListIndex = 1
Range("N6") = True
Case ListBox1.ListIndex = 2
Range("N7") = True
End Select

I also have these which I probably want to convert to Select Case method as
well.
If Range("N5") = True Then Range("O5") = L
If Range("N6") = True Then Range("O6") = L
If Range("N7") = True Then Range("O7") = L

Can you also suggest how to do that, please?
Rob
 
On Fri, 6 Feb 2004 11:03:23 +1030, "rob nobel"

Try this syntax:

=================
Select Case ListBox1.ListIndex
Case 0
Range("N5") = True
Case 1
Range("N6") = True
Case 2
Range("N7") = True
End Select
================

--ron
 
1st Question
Range("N5").Offset(Listbox1.ListIndex,0).Value = True

2nd Question
for i = 5 to 10
If Cells(i,"N").Value then Cells(i,"O").Value = L
Next

Assume L is a variable containing a value.
 
Thanks Ron.
I had to put "ActiveSheet." before each range to make it work but I don't
understand why??
Rob
 
Thanks Tom but I was really after a procedure that would use Select Case and
what you have suggested seems to be something different to that. I already
have the code working for both my questions. All I wanted was to convert
them to a Select case option to try and speed it up. The procedure that Ron
suggested for my first question worked (with a slight modification).
If you or some one else can help to use the Select Case method for the
following code, that would be appreciated.

If Range("N5") = True Then Range("O5") = L
If Range("N6") = True Then Range("O6") = L
If Range("N7") = True Then Range("O7") = L

Rob
 
Thanks Ron.
I had to put "ActiveSheet." before each range to make it work but I don't
understand why??
Rob
Click to expand...

There may be something else going on.

Using Range alone without an object qualifier is a shortcut for
ActiveSheet.Range (it returns a range from the active sheet; if the active
sheet isn’t a worksheet, the property fails).

You could probably also reference the worksheet explicitly.

Worksheets("Sheet1").Range("a1")


--ron
 
Thanks again Ron. That all makes sense but the reason still alludes me
other than maybe it's because the code is attached to a userform which is
called from another procedure on that worksheet. Maybe Excel's memory is
not as good as it thinks.
Any way, at least it works.
Rob
 
Back
Top