Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count rownumbers in loop | Excel Discussion (Misc queries) | |||
loop count | Excel Discussion (Misc queries) | |||
How can I loop to find the grouped shapes count | Excel Programming | |||
End loop macro with count | Excel Programming |