Max value in Do While counter

  • Thread starter Thread starter ChrisG
  • Start date Start date
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
 
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"?
 
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
 
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.
 
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
 
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.
 
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??
 
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
 
Back
Top