PDA

View Full Version : Equalities



iWiishh
05-14-2012, 10:38 AM
Hi, i'm kinda facing some problem

The actual coding is alot more complicated, but i kinda narrowed down my problem to this.

I've got lots of conditions and like

If 2 - 4, then
else if 5-6 then

But the problem now is,

Say i've got a cell in excel B2 value is 2000 which means cells(2,2) is 2000 right?

But when i tried to trouble shoot using msgbox, I did msgbox(200<= cells(2,2) <=300), why does it give me a true? it should be a false righT? since 2000 is no where in between 200 and 300.


Thanks!

Kenneth Hobs
05-14-2012, 11:44 AM
Since operation is left to right and True = -1 then -1 is True for 200 <= Range("B2").Value. The 2nd operation results in True as well since -1 is then less than 300.

Use AND just as you would with formulas.

MsgBox 200 <= Cells(2, 2) And Cells(2, 2) <= 300

Paul_Hossler
05-15-2012, 06:31 PM
Say i've got a cell in excel B2 value is 2000 which means cells(2,2) is 2000 right?


... probably, but Cells used without a 'dot' referance refers to the activesheet, which might not be what you were expecting.


The third example below I've always found tricky since it is relative to a range, and not a worksheet



Option Explicit
Sub test()
Dim r As Range

'active sheet
Worksheets("sheet1").Select
MsgBox Cells(2, 2).Address(True, True, xlA1, True)

'not active sheet
MsgBox Worksheets("Sheet2").Cells(2, 2).Address(True, True, xlA1, True)


'active relative to range
Set r = Worksheets("Sheet2").Range("H5:Z26")
MsgBox r.Cells(2, 2).Address(True, True, xlA1, True)

End Sub


Paul