number not a number?
 

Wrote some test code to try to solve a problem in another post.

Sub RangeNumber()
Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

Row = rstartcell.Row
rnum = Left(Cells(Row, 1), 2)
Select Case rnum

Case rnum = 11

But the code skips over Case rnum=11 and goes on to the next case, even
though rnum is 11. I also tried putting the 11 in quotes to tell XL
that it's text, but it didn't help. Any ideas why?

Thanks as always.

number not a number?
 

Because rnum is a variant type string because you performed a string
function on it.

Try

Case rnum "11"

or

rnum= INT(Cells(Row, 1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"davegb" wrote in message
ps.com...
Wrote some test code to try to solve a problem in another post.

Sub RangeNumber()
Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

Row = rstartcell.Row
rnum = Left(Cells(Row, 1), 2)
Select Case rnum

Case rnum = 11

But the code skips over Case rnum=11 and goes on to the next case, even
though rnum is 11. I also tried putting the 11 in quotes to tell XL
that it's text, but it didn't help. Any ideas why?

Thanks as always.

number not a number?
 

The function left returns a string. You need to convert that to a number or
change your case statement to look at strings... Also you should not use Row
as a variable since it is a reserved word in VBA. Finally your case statment
needs to be tweaked. Try this...

Sub RangeNumber()
dim ws as worksheet
dim rstartcell as range
dim rNumber as range
dim lRow as long
dim rNum as string

Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

lRow = rstartcell.Row
rnum = Left(Cells(lRow , 1), 2)
Select Case rnum
Case "11"

Let me know how that goes...


--
HTH...

Jim Thomlinson


"davegb" wrote:

Wrote some test code to try to solve a problem in another post.

Sub RangeNumber()
Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

Row = rstartcell.Row
rnum = Left(Cells(Row, 1), 2)
Select Case rnum

Case rnum = 11

But the code skips over Case rnum=11 and goes on to the next case, even
though rnum is 11. I also tried putting the 11 in quotes to tell XL
that it's text, but it didn't help. Any ideas why?

Thanks as always.

number not a number?
 

I just tested this

Sub numval()
rnum = Left(Cells(5, 3), 2)
Select Case rnum
Case Is = 11: MsgBox "hi"
Case Is = 12: MsgBox "low"
End Select
End Sub

--
Don Guillett
SalesAid Software

"davegb" wrote in message
ps.com...
Wrote some test code to try to solve a problem in another post.

Sub RangeNumber()
Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

Row = rstartcell.Row
rnum = Left(Cells(Row, 1), 2)
Select Case rnum

Case rnum = 11

But the code skips over Case rnum=11 and goes on to the next case, even
though rnum is 11. I also tried putting the 11 in quotes to tell XL
that it's text, but it didn't help. Any ideas why?

Thanks as always.

number not a number?
 

Your Case evaluates the expression (rnum = 11) which inevitably will return
False, and False < 11

Select Case rnum
Case 11 ' etc
End Select

In passing this line
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))
has no relevance to your sample of code as posted, rnum will always be the
value in A2

also, unless you are sure the variant rnum will never be string that cannot
be coerced to a number -
Case 11 will error

also, suggest don't use the keyword 'Row' as an undeclared variable

Regards,
Peter T


"davegb" wrote in message
ps.com...
Wrote some test code to try to solve a problem in another post.

Sub RangeNumber()
Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

Row = rstartcell.Row
rnum = Left(Cells(Row, 1), 2)
Select Case rnum

Case rnum = 11

But the code skips over Case rnum=11 and goes on to the next case, even
though rnum is 11. I also tried putting the 11 in quotes to tell XL
that it's text, but it didn't help. Any ideas why?

Thanks as always.

number not a number?
 

I didn't see you already have three abswers when I posted!

slight correction -

Your Case evaluates the expression (rnum = 11) which inevitably will
return
False, and False < 11

not 'inevitably', but a boolean or error

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Your Case evaluates the expression (rnum = 11) which inevitably will
return
False, and False < 11

Select Case rnum
Case 11 ' etc
End Select

In passing this line
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))
has no relevance to your sample of code as posted, rnum will always be the
value in A2

also, unless you are sure the variant rnum will never be string that
cannot
be coerced to a number -
Case 11 will error

also, suggest don't use the keyword 'Row' as an undeclared variable

Regards,
Peter T


"davegb" wrote in message
ps.com...
Wrote some test code to try to solve a problem in another post.

Sub RangeNumber()
Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

Row = rstartcell.Row
rnum = Left(Cells(Row, 1), 2)
Select Case rnum

Case rnum = 11

But the code skips over Case rnum=11 and goes on to the next case, even
though rnum is 11. I also tried putting the 11 in quotes to tell XL
that it's text, but it didn't help. Any ideas why?

Thanks as always.

number not a number?
 

Peter T wrote:
I didn't see you already have three abswers when I posted!

slight correction -

Your Case evaluates the expression (rnum = 11) which inevitably will
return
False, and False < 11

not 'inevitably', but a boolean or error

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Your Case evaluates the expression (rnum = 11) which inevitably will
return
False, and False < 11

Select Case rnum
Case 11 ' etc
End Select

In passing this line
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))
has no relevance to your sample of code as posted, rnum will always be the
value in A2

also, unless you are sure the variant rnum will never be string that
cannot
be coerced to a number -
Case 11 will error

also, suggest don't use the keyword 'Row' as an undeclared variable

Regards,
Peter T


"davegb" wrote in message
ps.com...
Wrote some test code to try to solve a problem in another post.

Sub RangeNumber()
Set ws = ActiveSheet

Set rstartcell = ws.Range("A2")
Set rNumber = ws.Range(rstartcell, rstartcell.End(xlDown))

Row = rstartcell.Row
rnum = Left(Cells(Row, 1), 2)
Select Case rnum

Case rnum = 11

But the code skips over Case rnum=11 and goes on to the next case, even
though rnum is 11. I also tried putting the 11 in quotes to tell XL
that it's text, but it didn't help. Any ideas why?

Thanks as always.




Thanks everybody for your replies! I'm still working on getting it all
straightened out, but I'm on track again.