Change while loop from count to formula
 

I would like to adapt the following code to use a formula to calculat
the value of 53, like the counter is doing below. For eac
"Currentcolumn" there is a countIf formula in row 8 when that formul
reaches 53 I want to stop adding the value "New" to each column.

What it does now is check the variable totalrow once and if it is les
than 53 it adds the value upto 53 and higher. I want to stop whe
totalrow(8) = 53

Thanks



Dim CurrentColumn As Integer, CurrentRow As Long, totalrow As Long
Count As Byte
With ActiveSheet

For CurrentColumn = 7 To 103
CurrentRow = 11
totalrow = 8
Count = 0
While Count < 53 And CurrentRow <= 298 And .cells(totalrow
CurrentColumn).Value < 53 If .cells(CurrentRow, CurrentColumn).Value
"." Then
.cells(CurrentRow, CurrentColumn).Value = "New"
Count = Count + 1 + totalrow
End If
CurrentRow = CurrentRow + 1
Wend
Next CurrentColumn
End Wit

--
hotherp
-----------------------------------------------------------------------
hotherps's Profile: http://www.excelforum.com/member.php...nfo&userid=505
View this thread: http://www.excelforum.com/showthread.php?threadid=31371

Change while loop from count to formula
 

isn't that what this line is doing?

.cells(totalrow,CurrentColumn).Value < 53

--
Regards,
Tom Ogilvy


"hotherps" wrote in message
...

I would like to adapt the following code to use a formula to calculate
the value of 53, like the counter is doing below. For each
"Currentcolumn" there is a countIf formula in row 8 when that formula
reaches 53 I want to stop adding the value "New" to each column.

What it does now is check the variable totalrow once and if it is less
than 53 it adds the value upto 53 and higher. I want to stop when
totalrow(8) = 53

Thanks



Dim CurrentColumn As Integer, CurrentRow As Long, totalrow As Long,
Count As Byte
With ActiveSheet

For CurrentColumn = 7 To 103
CurrentRow = 11
totalrow = 8
Count = 0
While Count < 53 And CurrentRow <= 298 And .cells(totalrow,
CurrentColumn).Value < 53 If .cells(CurrentRow, CurrentColumn).Value =
"." Then
cells(CurrentRow, CurrentColumn).Value = "New"
Count = Count + 1 + totalrow
End If
CurrentRow = CurrentRow + 1
Wend
Next CurrentColumn
End With


--
hotherps
------------------------------------------------------------------------
hotherps's Profile:
http://www.excelforum.com/member.php...fo&userid=5055
View this thread: http://www.excelforum.com/showthread...hreadid=313719