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

N

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
 
T

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
 
N

NorTor

Dear Tom!

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


Best regards
NorTor
 
N

NorTor

Dear Tom!

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


Cheers
NorTor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top