comparing cells in a column

[gosees]

Hi, im a relative noob to VBA. I am trying to compare two or more cells within a column. For example is any cell in Column A < 7 and is any of the other cells in Column A > 10.
If so I'd like to print a message. The thing is I have a code to do this but it is very long:


e.g

If Range("H5").Value < 10.5 And Range("H5").Value > 6 And Range("H6").Value > 15 And Range("H6").Value < 20 Then
Sheet1.Cells(5, 9) = "matching"
ElseIf Range("H5").Value < 10.5 And Range("H5").Value > 6 And Range("H7").Value > 15 And Range("H7").Value < 20 Then
Sheet1.Cells(5, 9) = "matching"
ElseIf Range("H5").Value < 10.5 And Range("H5").Value > 6 And Range("H8").Value > 15 And Range("H8").Value < 20 Then
Sheet1.Cells(5, 9) = "matching"
ElseIf Range("H5").Value < 10.5 And Range("H5").Value > 6 And Range("H9").Value > 15 And Range("H9").Value < 20 Then
Sheet1.Cells(5, 9) = "matching"
ElseIf Range("H5").Value < 10.5 And Range("H5").Value > 6 And Range("H10").Value > 15 And Range("H10").Value < 20 Then
Sheet1.Cells(5, 9) = "matching"
Else
Sheet1.Cells(5, 9) = ""
End If

If Range("H6").Value < 10.5 And Range("H6").Value > 6 And Range("H5").Value > 15 And Range("H5").Value < 20 Then
Sheet1.Cells(6, 9) = "matching"
ElseIf Range("H6").Value < 10.5 And Range("H6").Value > 6 And Range("H7").Value >15 And Range("H7").Value < 20 Then
Sheet1.Cells(6, 9) = "matching"
ElseIf Range("H6").Value < 10.5 And Range("H6").Value > 6 And Range("H8").Value >15 And Range("H8").Value < 20 Then
Sheet1.Cells(6, 9) = "matching"
ElseIf Range("H6").Value < 10.5 And Range("H6").Value > 6 And Range("H9").Value > 15 And Range("H9").Value < 20 Then
Sheet1.Cells(6, 9) = "matching"
ElseIf Range("H6").Value < 10.5 And Range("H6").Value > 6 And Range("H10").Value > 15 And Range("H10").Value < 20 Then
Sheet1.Cells(6, 9) = "matching"
Else
Sheet1.Cells(6, 9) = ""
End If

If Range("H7").Value < 10.5 And Range("H7").Value > 6 And Range("H5").Value > 15 And Range("H5").Value < 20 Then
Sheet1.Cells(7, 9) = "matching"
ElseIf Range("H7").Value < 10.5 And Range("H7").Value > 6 And Range("H6").Value > 15 And Range("H6").Value < 20 Then
Sheet1.Cells(7, 9) = "matching"
ElseIf Range("H7").Value < 10.5 And Range("H7").Value > 6 And Range("H8").Value > 15 And Range("H8").Value < 20 Then
Sheet1.Cells(7, 9) = "matching"
ElseIf Range("H7").Value < 10.5 And Range("H7").Value > 6 And Range("H9").Value > 15 And Range("H9").Value < 20 Then
Sheet1.Cells(7, 9) = "matching"
ElseIf Range("H7").Value < 10.5 And Range("H7").Value > 6 And Range("H10").Value > 15 And Range("H10").Value < 20 Then
Sheet1.Cells(7, 9) = "matching"
Else
Sheet1.Cells(7, 9) = ""
End If

If Range("H8").Value < 10.5 And Range("H8").Value > 6 And Range("H5").Value > 15 And Range("H5").Value < 20 Then
Sheet1.Cells(8, 9) = "matching"
ElseIf Range("H8").Value < 10.5 And Range("H8").Value > 6 And Range("H6").Value > 15 And Range("H6").Value < 20 Then
Sheet1.Cells(8, 9) = "matching"
ElseIf Range("H8").Value < 10.5 And Range("H8").Value > 6 And Range("H7").Value >15 And Range("H7").Value < 20 Then
Sheet1.Cells(8, 9) = "matching"
ElseIf Range("H8").Value < 10.5 And Range("H8").Value > 6 And Range("H9").Value > 15 And Range("H9").Value < 20 Then
Sheet1.Cells(8, 9) = "matching"
ElseIf Range("H8").Value < 10.5 And Range("H8").Value > 6 And Range("H10").Value > 15 And Range("H10").Value < 20 Then
Sheet1.Cells(8, 9) = "matching"
Else
Sheet1.Cells(8, 9) = ""
End If

but as you can see it can be very long when there are lots of cells in the column to compare. What I would like is a way of doing the same calculations but with a much shorter code.
Any help would be much appreciated.

 

[taupirho]

Since your using excel check out the countif function

eg

The following will put the count of cells in column A that are either >10 or <7 into cell B1. If this is > 0 then you can output an appropriate message


Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],"">10"")+COUNTIF(C[-1],""<7"")"



In order to understand recursion, you must first understand recursion.

 

[Bluejay07]

Also, just to let you know, there is a separate forum for VBA.

http://www.tek-tips.com/threadminder.cfm?pid=707

If at first you don't succeed, then sky diving wasn't meant for you!

 

[SkipVought]

gosees,

This sort of question, ie "Column A < 7 and is any of the other cells in Column A > 10" is quite simple to do in Excel on a sheet with spreadsheet functions, without haveing to resort to ANY VB coding at all, in most cases. However, not knowing where your requirement is coming from, it is not possible to definitively direct you to forum68 or forum707. But one of these two, can provide good tips.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]