Store ranges in arrays, and build third array based on IF test?

[NorTor]

Hello


I am trying to build a range based on whether to specific columns in
two arrays are equal or not.

I used to do this with a for ... next and do until loop, but because I
have a huge number of rows and quite a few columns, this ran way to
slowly.

Here is the poor try I did so far....

**************************************
Dim dSett3() As Variant
ReDim dSett3(3 To (cnt1 + 3))

dSett1 = Range("A3:A" & cnt1 + 3).Value
dSett2 = Sheets("Datasett2a").Range("A2:Z" & cnt2 + 2).Value

For i = 3 To (cnt1 + 3)
For j = 1 To 9
If dSett1(i, 1) = dSett2(i, 1) Then
dSett3 = dSett2(i, (9 + j)).Value
End If
Next j
Next i
*************************************

Please help if you can!



Cheers
NorTor

 

[Tom Ogilvy]

Dim dSett3() As Variant
ReDim dSett3(3 To (cnt1 + 3))

dSett1 = Range("A3:A" & cnt1 + 3).Value
dSett2 = Sheets("Datasett2a").Range("A2:Z" & cnt2 + 2).Value

For i = 3 To (cnt1 + 3)
If dSett1(i, 1) = dSett2(i, 1) Then
For j = 1 To 9
dSett3(i) = dSett3(i) + dSett2(i, (9 + j))
Next j
End If
Next i

but shouldn't this
dSett2 = Sheets("Datasett2a").Range("A2:Z" & cnt2 + 2).Value
be
dSett2 = Sheets("Datasett2a").Range("A2:Z" & cnt1 + 2).Value

 

[NorTor]

Dear Tom!

Once again you are the 'Salvatore' !
Works silk smooth; thanks a plenty!


Best regards
NorTor

 

[NorTor]

Dear Tom!

Forgot to say that your comment on the dSett2 line is 100% correct;
misspelled by Mr NorTor


Cheers
NorTor