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

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

.

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

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

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

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

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



.

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



.