VBA help, counters and loops

[Jameo]

Hi everyone.

I have a little issue. I have two sheets. A sheet that contains the names of sites, and the minimum guranteed pressures for that site.

I then have another sheet, with the same sites, but daily pressure values for a month.

I am trying to write a macro that will determine the number of times in a dataset that the actual pressures have dropped below the guranteed pressures.

In both sheets, the site name starts in cell (1,2), with the pressure values below the site name. i.e.


Guranteed Pressure list

     A      B        C         D
1|NULL | Site A | Site B | Site C |
2|NULL | 50     | 60     | 70     |

The actual pressure list is similar:

     A      B        C         D
1|Date1| Site A | Site B | Site C |
2|Date2| 50     | 60     | 70     |

So for each sheet, the first line of data is in B2. The site names are exactly the same in both sheets.


Now, here is the macro that I have written (that doesn't work):

Public Sub CntBreach()
Dim RowIn As Long
Dim LRow As Long
Dim IntCnt As Boolean
Dim Cnt As Long

IntCnt = False
LRow = Sheets("Pressures").Range("A" & Rows.Count).End(xlUp).Row
Cnt = 0

If Not ActiveSheet.Name = "Pressures" Then
Sheets("Pressures").Select
End If
For i = 2 To LRow
RowIn = 1
Do
RowIn = RowIn + 1
If RowIn = 121 Then
IntCnt = True
End If
If Sheets("Pressures").Cells(i, RowIn) < Sheets("AllOTCalc").Cells(2, RowIn) Then
Cnt = Cnt + 1
End If

Loop Until IntCnt = True
 
Next i

Sheets("AllOTCalc").Cells(3, 3).Value = Cnt
 
 

End Sub

I am trying to use "for each i" to iterate through the rows, and a loop with a counter "RowIn" to itterate through the columns. i.e for 120 different sites, the macro should check each site against the reference sheet, add one to the counter if the actual pressure is lower than the guranteed, and then at the end of the row, move down to the next row.

Does anyone have any ideas?

Many thanks

 

[Jameo]

Never mind all, I saw my mistake. I simply moved IntCnt = False, into the "for i" statement.

Cheers anyway, and sorry to anyone who has spent valuable minutes reading this

 

[Ruddles]

This forum always comes up with a solution one way or another!