Perform action only if mutiple criteria is met

[xlUser]

Hi

I have the simple macro below that executes the remaining code if cell p4 is greater than 1.9, else it does nothing.

I however to need to include additional criteria in the if statement. As well the cell p4 check, I would like the code to run only if none of the cell values in the range ("D8:H8") are not less than 1.9 (point x in the code) i.e if cell E8 was 1.5 then the remaining code would not run:

[vba]
sub test
pvalue = Range("p4").Value
If Abs(pvalue) > 1.9 Then
'Point X - and cells d8:h8 do not contain any cells with the a number less than 1.9
Range("N14:N1000").Copy
Range("M14").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
end sub
[/vba]

I'm assuming this is relatively easy to do?

Also as an optional, it would be good to find out which of the columns/cell in the range ("D8:H8") has a value less than 1.9, e.g maybe as a message box.

Thanks,

XUser

[mikerickson]

You could use[VBA] If WorksheetFunction.CountIf(Range("D8:H8"),"<1.8") = 0 Then[/VBA]

[xlUser]

Thanks Mike.

This works in cases where all numbers in the range ("D8:H8") are postive. But I also have negative numbers in the range. Sorry I should have mentioned this before. So I guess I want to evaulate the absolute value of the number. So if F8 contains -2.7, in absolute terms abs(F8) it is greater than 1.9 and the rest of the code should run.

Is there a way to ammend the countif function to evaluate the absolute value of the number or any values between -1.9 and 1.9?

Also in order to develop my code further I have worked out I definately need to identify which cells/columns have absolute values less than 1.9 if they exist.

So for example if Range("H8") = 1.2, I'd like to assign to a variable

i.e

Col1 , Col2, Col3 would be my variables that would signify columns with values less than (abs) 1.9
So if only one column (h8) is less than 1.9 then Col1 = column(8), . So if I have "msgbox col1", the column address 8 would show up.

If two columns have values less than 1.9 then I would have two variables

e.g Col1 = Column 8
e.g Col2 = Column 6

Not sure if this is the best way to signify the columns with values less than 1.9 but it's the only way I can think of. But most of all I'm, struggling to work out how best to indentify these. Using find function, or looping through each cell etc.

Hope someone can help.

Many Thanks,

XlUser

[mikerickson]

[VBA]If WorksheetFunction.CountIf(Range("D8:H8"),"<1.8") + WorksheetFunction.CountIf(Range("D8:H8"),">1.8") = 0 Then[/VBA]

[xlUser]

Thanks Mike - Your formula here doesn't appear to work? So I am still using absolute function in the evaluation.

XlUser