Max value in Do While counter

C

ChrisG

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
 
C

ChrisG

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"?
 
A

Andrei Scudder

crashes when j = 32,767.
Click to expand...
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
 
J

Jean-Paul Viel

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.
 
P

Paulw2k

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
 
T

Tom Ogilvy

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.
 
C

ChrisG

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??
 
T

Tom Ogilvy

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
 
Top