Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
I get this code to work for small lists, however, the
real test is on almost 54,000 records and the code crashes when j = 32,767. In a nutshell Col B and Col C have values and are being compared against Col F and Col G (If B=F AND C=G, then H=1) Dim i As Integer Dim j As Integer i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) i = i + 1 Do While Not (IsEmpty(Cells(j, 6))) j = j + 1 If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3) = Cells(j, 7))) Then Cells(j, 8) = "1" Loop j = 1 Loop |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
Would it be more efficient to utlize Arrays and compare the elements of the arrays? I am having difficulty visualizing how to compare "Array PAIRS"? -----Original Message----- I get this code to work for small lists, however, the real test is on almost 54,000 records and the code crashes when j = 32,767. In a nutshell Col B and Col C have values and are being compared against Col F and Col G (If B=F AND C=G, then H=1) Dim i As Integer Dim j As Integer i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) i = i + 1 Do While Not (IsEmpty(Cells(j, 6))) j = j + 1 If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3) = Cells(j, 7))) Then Cells(j, 8) = "1" Loop j = 1 Loop . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
crashes when j = 32,767. Well it seems that VBA Integer is only two bytes long, I recommend that you use a Long or unsigned integer (dont know if VBA has one tho) that should solve your problem Andrei |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
Hi,
The maximum value for an integer is 32,767. If you wand more declare your variables as long witch is about 2 billions, you should have enough. -- JP http://www.solutionsvba.com "ChrisG" wrote in message ... I get this code to work for small lists, however, the real test is on almost 54,000 records and the code crashes when j = 32,767. In a nutshell Col B and Col C have values and are being compared against Col F and Col G (If B=F AND C=G, then H=1) Dim i As Integer Dim j As Integer i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) i = i + 1 Do While Not (IsEmpty(Cells(j, 6))) j = j + 1 If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3) = Cells(j, 7))) Then Cells(j, 8) = "1" Loop j = 1 Loop |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
Chris,
From the VBA help file Integer Data Type Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767. Long Data Type Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647. Regards Paul "ChrisG" wrote in message ... I get this code to work for small lists, however, the real test is on almost 54,000 records and the code crashes when j = 32,767. In a nutshell Col B and Col C have values and are being compared against Col F and Col G (If B=F AND C=G, then H=1) Dim i As Integer Dim j As Integer i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) i = i + 1 Do While Not (IsEmpty(Cells(j, 6))) j = j + 1 If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3) = Cells(j, 7))) Then Cells(j, 8) = "1" Loop j = 1 Loop |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
Dim i As Long
Dim j As Long i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) Do While Not (IsEmpty(Cells(j, 6))) If Cells(i, 2) = Cells(j, 6) And _ Cells(i, 3) = Cells(j, 7) _ Then Cells(j, 8) = "1" j = j + 1 Loop j = 1 i = i + 1 Loop You want to put a 1 in any row j (column H) where the values in columns F and G match the values in Columns B and C respectively with the B and C values being in row i. this would require 54000 x 54000 comparisons. I your description, however, you sound like you only want a 1 if all on the same row, B = F and C = G. If so, you don't need a nested loop Dim i As Long i = 1 Do While Not (IsEmpty(Cells(i, 2))) and Not (IsEmpty(Cells(j, 6))) If Cells(i, 2) = Cells(i, 6) And _ Cells(i, 3) = Cells(i, 7) _ Then Cells(i, 8) = "1" i = i + 1 Loop If you post back with exactly what you want to check and do, then I am sure we can suggest a faster way. -- Regards, Tom Ogilvy ChrisG wrote in message ... I get this code to work for small lists, however, the real test is on almost 54,000 records and the code crashes when j = 32,767. In a nutshell Col B and Col C have values and are being compared against Col F and Col G (If B=F AND C=G, then H=1) Dim i As Integer Dim j As Integer i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) i = i + 1 Do While Not (IsEmpty(Cells(j, 6))) j = j + 1 If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3) = Cells(j, 7))) Then Cells(j, 8) = "1" Loop j = 1 Loop |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
Faster would be better....I ran subroutine and it was
still churning when I left the office maxing out hte CPU. Hmmmmh. What I want for example: For each value in Col B & Col C compare to every record pair in Col F & Col G respectively when B = F AND C = G then put a "1" in H. I thought by indexing through B & C first comparing to each F & G using Do/For loop to handle discretely would be effecient.....Would an array construct be more effecient?? -----Original Message----- Dim i As Long Dim j As Long i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) Do While Not (IsEmpty(Cells(j, 6))) If Cells(i, 2) = Cells(j, 6) And _ Cells(i, 3) = Cells(j, 7) _ Then Cells(j, 8) = "1" j = j + 1 Loop j = 1 i = i + 1 Loop You want to put a 1 in any row j (column H) where the values in columns F and G match the values in Columns B and C respectively with the B and C values being in row i. this would require 54000 x 54000 comparisons. I your description, however, you sound like you only want a 1 if all on the same row, B = F and C = G. If so, you don't need a nested loop Dim i As Long i = 1 Do While Not (IsEmpty(Cells(i, 2))) and Not (IsEmpty(Cells(j, 6))) If Cells(i, 2) = Cells(i, 6) And _ Cells(i, 3) = Cells(i, 7) _ Then Cells(i, 8) = "1" i = i + 1 Loop If you post back with exactly what you want to check and do, then I am sure we can suggest a faster way. -- Regards, Tom Ogilvy ChrisG wrote in message ... I get this code to work for small lists, however, the real test is on almost 54,000 records and the code crashes when j = 32,767. In a nutshell Col B and Col C have values and are being compared against Col F and Col G (If B=F AND C=G, then H=1) Dim i As Integer Dim j As Integer i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) i = i + 1 Do While Not (IsEmpty(Cells(j, 6))) j = j + 1 If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3) = Cells(j, 7))) Then Cells(j, 8) = "1" Loop j = 1 Loop . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max value in Do While counter
This took about two hours:
Sub CalcH() Dim rng As Range Dim rng2 As Range Dim sngStart As Single Dim sForm As String sngStart = Timer Set rng = Range(Cells(1, 2), _ Cells(Rows.Count, 2).End(xlUp)) Set rng2 = Range(Cells(1, 6), _ Cells(Rows.Count, 6).End(xlUp)) sForm = "(" & rng.Address & "=F1)*(" & _ rng.Offset(0, 1).Address & "=G1))" rng2.Offset(0, 2).Formula = "=If(SUMPRODUCT(" & _ sForm & "0,1,na())" On Error Resume Next rng2.Offset(0, 2).SpecialCells(xlFormulas, _ xlErrors).ClearContents On Error GoTo 0 rng2.Offset(0, 2).Formula = rng2.Offset(0, 2).Value Cells(1, "J").Value = Timer - sngStart ThisWorkbook.Save End Sub --- I checked the results manually and that took about 30 minutes with over 60000 rows. But I had to use a couple of dummy columns past column H. Is this acceptable? Can one make temporary use of columns past column H. -- Regards, Tom Ogilvy ChrisG wrote in message ... Faster would be better....I ran subroutine and it was still churning when I left the office maxing out hte CPU. Hmmmmh. What I want for example: For each value in Col B & Col C compare to every record pair in Col F & Col G respectively when B = F AND C = G then put a "1" in H. I thought by indexing through B & C first comparing to each F & G using Do/For loop to handle discretely would be effecient.....Would an array construct be more effecient?? -----Original Message----- Dim i As Long Dim j As Long i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) Do While Not (IsEmpty(Cells(j, 6))) If Cells(i, 2) = Cells(j, 6) And _ Cells(i, 3) = Cells(j, 7) _ Then Cells(j, 8) = "1" j = j + 1 Loop j = 1 i = i + 1 Loop You want to put a 1 in any row j (column H) where the values in columns F and G match the values in Columns B and C respectively with the B and C values being in row i. this would require 54000 x 54000 comparisons. I your description, however, you sound like you only want a 1 if all on the same row, B = F and C = G. If so, you don't need a nested loop Dim i As Long i = 1 Do While Not (IsEmpty(Cells(i, 2))) and Not (IsEmpty(Cells(j, 6))) If Cells(i, 2) = Cells(i, 6) And _ Cells(i, 3) = Cells(i, 7) _ Then Cells(i, 8) = "1" i = i + 1 Loop If you post back with exactly what you want to check and do, then I am sure we can suggest a faster way. -- Regards, Tom Ogilvy ChrisG wrote in message ... I get this code to work for small lists, however, the real test is on almost 54,000 records and the code crashes when j = 32,767. In a nutshell Col B and Col C have values and are being compared against Col F and Col G (If B=F AND C=G, then H=1) Dim i As Integer Dim j As Integer i = 1 j = 1 Do While Not (IsEmpty(Cells(i, 2))) i = i + 1 Do While Not (IsEmpty(Cells(j, 6))) j = j + 1 If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3) = Cells(j, 7))) Then Cells(j, 8) = "1" Loop j = 1 Loop . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Day counter | Excel Discussion (Misc queries) | |||
1+2+3+4+........... Counter | Excel Discussion (Misc queries) | |||
how do I set up a counter? | Excel Discussion (Misc queries) | |||
counter | Excel Discussion (Misc queries) | |||
Counter | Excel Discussion (Misc queries) |