PDA

View Full Version : Solved: VB code for greater than 1 or less than -1



truzilla
07-11-2008, 08:40 AM
Hey everyone, so I have some code that looks for a specific value and a message box comes up with the cells that is either: greater than 1 OR less than -1. Unfortunately I can't find out how to say both in the same line to make the macro work. I have copied the code below for your reference. I just need to know how to say: greater than 1 OR less than -1

' Message box showing all checks that are less than -1

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If LCase(.Cells(i, TEST_COLUMN).Value) = "check" Then

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = .Cells(i, TEST_COLUMN).Column + 4 To LastCol

' THIS IS WHERE I NEED TO EDIT THE NUMBER TO MAKE IT INCLUDE GREATER THAN 1 ALSO
If .Cells(i, j).Value < -1 Then

msg = msg & vbTab & .Cells(i, j).Address(False, False) & vbNewLine

End If
Next j
End If
Next i

If msg <> "" Then

MsgBox "The following CHECK cells are LESS than '-1': " & vbNewLine & msg
End If

End With

THANKS FOR ALL YOUR HELP! : pray2:

marshybid
07-11-2008, 08:59 AM
Hi there,

You could try


If .Cells(i, j).Value < -1 Or If .Cells(i, j).Value >1 Then


Marshybid

truzilla
07-11-2008, 09:04 AM
error, cant do that - compile error on the second if

marshybid
07-11-2008, 09:10 AM
error, cant do that - compile error on the second if

My mistake, remove the if


If .Cells(i, j).Value < -1 Or .Cells(i, j).Value >1 Then


I just copy and pasted your original line :rotlaugh:

Marshybid

Bob Phillips
07-11-2008, 09:23 AM
If Abs(.Cells(i, j).Value) > 1 Then

marshybid
07-11-2008, 09:39 AM
If Abs(.Cells(i, j).Value) > 1 Then


Wouldn't this return a positive value for any number?

example;

=Abs(-1) returns 1

Marshybid

Bob Phillips
07-11-2008, 10:15 AM
Yes it does, but it is not setting that value, just comparing it against 1, so itis one test rather than 2.

marshybid
07-11-2008, 10:21 AM
Yes it does, but it is not setting that value, just comparing it against 1, so itis one test rather than 2.

Very nice use of that function. I defintely would not have thought of it.

Have a good week end

Marshybid :hi:

truzilla
07-11-2008, 11:11 AM
works super, thanks guys!