Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is ther a better way?
Is there a better way to achieve the same result that doesn't take s long. I let this run for 10 minutes before stopping it. I changed th LastRow to = 50 and it ran fine so I know it works. Normally whe running this there will be between 2000 and 10000 rows to loop through I know when looping through that many rows it's going to take some tim but if 2000 rows takes longer than 10 minutes then this won't work wel for me. Ok, This is seeing if 2 rows in columns A match then 2 rows in column C match then 2 rows in columns D match. If all are true then add 1 t previous rows value. if its not true then put a 1 in the cell. I'm basically counting the number of times columns A, C, and D matc with previous row then starting the count over. Code ------------------- Dim counter As Integer LastRow = Range("A65532").End(xlUp).Row counter = 2 Do Until counter = LastRow + 1 If Range("A" & counter) = Range("A" & counter - 1) And Range("D" & counter) = Range("D" & counter - 1) And Range("C" & counter) = Range("C" & counter - 1) Then Range("G" & counter) = Range("G" & counter - 1).Value + 1 Else Range("G" & counter) = 1 End If counter = counter + 1 Loo ------------------- -- BN-C ----------------------------------------------------------------------- BN-CD's Profile: http://www.excelforum.com/member.php...fo&userid=3537 View this thread: http://www.excelforum.com/showthread.php?threadid=57081 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is ther a better way?
This might help
Dim counter As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual LastRow = Range("A65532").End(xlUp).Row counter = 2 Do Until counter = LastRow + 1 If Range("A" & counter) = Range("A" & counter - 1) And _ Range("D" & counter) = Range("D" & counter - 1) And _ Range("C" & counter) = Range("C" & counter - 1) Then Range("G" & counter) = Range("G" & counter - 1).Value + 1 Else Range("G" & counter) = 1 End If counter = counter + 1 Loop Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "BN-CD" wrote in message ... Is there a better way to achieve the same result that doesn't take so long. I let this run for 10 minutes before stopping it. I changed the LastRow to = 50 and it ran fine so I know it works. Normally when running this there will be between 2000 and 10000 rows to loop through. I know when looping through that many rows it's going to take some time but if 2000 rows takes longer than 10 minutes then this won't work well for me. Ok, This is seeing if 2 rows in columns A match then 2 rows in columns C match then 2 rows in columns D match. If all are true then add 1 to previous rows value. if its not true then put a 1 in the cell. I'm basically counting the number of times columns A, C, and D match with previous row then starting the count over. Code: -------------------- Dim counter As Integer LastRow = Range("A65532").End(xlUp).Row counter = 2 Do Until counter = LastRow + 1 If Range("A" & counter) = Range("A" & counter - 1) And Range("D" & counter) = Range("D" & counter - 1) And Range("C" & counter) = Range("C" & counter - 1) Then Range("G" & counter) = Range("G" & counter - 1).Value + 1 Else Range("G" & counter) = 1 End If counter = counter + 1 Loop -------------------- -- BN-CD ------------------------------------------------------------------------ BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374 View this thread: http://www.excelforum.com/showthread...hreadid=570815 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is ther a better way?
=SUMPRODUCT(--($A$1:$A$2000=$A$2:$A$2001),--($C$1:$C$2000=$C$2:$C$2001),--($D$1:$D$2000=$D$2:$D$2001))
will give you the count if you have values in all the rows. (if you rows 15 to 21 were all blank, for example, then this would show them as matching - which technically, they are). -- Regards, Tom Ogilvy "BN-CD" wrote: Is there a better way to achieve the same result that doesn't take so long. I let this run for 10 minutes before stopping it. I changed the LastRow to = 50 and it ran fine so I know it works. Normally when running this there will be between 2000 and 10000 rows to loop through. I know when looping through that many rows it's going to take some time but if 2000 rows takes longer than 10 minutes then this won't work well for me. Ok, This is seeing if 2 rows in columns A match then 2 rows in columns C match then 2 rows in columns D match. If all are true then add 1 to previous rows value. if its not true then put a 1 in the cell. I'm basically counting the number of times columns A, C, and D match with previous row then starting the count over. Code: -------------------- Dim counter As Integer LastRow = Range("A65532").End(xlUp).Row counter = 2 Do Until counter = LastRow + 1 If Range("A" & counter) = Range("A" & counter - 1) And Range("D" & counter) = Range("D" & counter - 1) And Range("C" & counter) = Range("C" & counter - 1) Then Range("G" & counter) = Range("G" & counter - 1).Value + 1 Else Range("G" & counter) = 1 End If counter = counter + 1 Loop -------------------- -- BN-CD ------------------------------------------------------------------------ BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374 View this thread: http://www.excelforum.com/showthread...hreadid=570815 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is ther a better way?
Thanks for the help guys. It works great! -- BN-CD ------------------------------------------------------------------------ BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374 View this thread: http://www.excelforum.com/showthread...hreadid=570815 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is ther no "Convert" function available in excel? | Excel Worksheet Functions | |||
excel, can put name in one cell and get ther# in the next cell? | Excel Worksheet Functions | |||
Is ther a way to change the interior.colorindex of multiple rows | Excel Programming |