array question

[Gary Keramidas]

if i create 2 arrays from a range, is there a way to compare the entire array,
instead of element by element?


For i = 2 To lastrow
arr1 = ws.Range("c" & i & ":E" & i)
arr2 = ws.Range("c" & i - 1 & ":E" & i - 1)

i want to know when arr1 = arr2.

right now i just use a loop to check the elements and if they all match, i
continue on.

 

[Guest]

VBA has no concept analogous to the worksheet array formula. However, the
VBA Evaluate() function will have Excel evaluate a string expression as
though it were an array entered formula.

If everything is numeric, you could use
IF Evaluate("MAX(ABS(c" & i & ":E" & i &"-c" & i - 1 & ":E" & i - 1 &
"))") = 0 Then
to test for equality of all corresponding cells.

If there may be non-numeric values, you could construct an Excel expression
that would coerce the array of logical comparisons into numbers and summarize
as a single value for return to VBA, such as
IF Evaluate("MIN(1*(c" & i & ":E" & i &"=c" & i - 1 & ":E" & i - 1 & "))")
= 0 Then
to test for inequality of at least on of the corresponding cells.

Jerry

 

[RB Smissaert]

Not sure I get the way you make your arrays.
Can't you simply do:

With ws
arr1 = .Range(.Cells(3, 2), .Cells(5, lastrow))
arr2 = .Range(.Cells(3, 1), .Cells(5, lastrow - 1))
End With

As to comparing arrays. I don't think there is any other way than to loop
through
all the array elements, of course getting out the loops if you find a
difference.
The only refinement I can think of is loop in such a way that you are most
likely
to get a non-matching element as early as possible.


RBS

 

[Gary Keramidas]

thanks rb.

i create the array in a for next statement, for i = 2 to lastrow, that's why i
have the i variable.

but your suggestion about the comparison makes sense.

 

[Guest]

Try using Join to convert the arrays to strings (There's a Split function too):

If Join(arr1) = Join(arr2) Then
. . .
End If

 

[RB Smissaert]

Not sure that is any faster and my guess is that it is slower.
VB(A) is slow with string concatenations, plus you may come across
a differing array element early in the loops so exit early.
Only some testing will tell for sure what the fastest method is.

RBS

 

[Guest]

I'm sure it is slower, but the OP seemed to be interested in simplicity not
speed. What's a few banano-seconds if the project is not compute intensive?

 

[Gary Keramidas]

when you create an array using a range, how can you check the number of
elements?
arr = range("A1:C1")

ubound(arr) doesn't seem to work

is there one element. what is the delimiter?

is there any way to be able to view the entire contents of any array to see what
it looks like?

 

[RB Smissaert]

Sure it is a few more lines of code (4?), but worth it if you are dealing
with large arrays.

RBS

 

[Dave Peterson]

One way is to use the number of cells in that range:

msgbox range("a1:c1").cells.count


But if you pick up the values the way you showed, you end up with a 2
dimensional array. In your case, it's 1 row by 3 columns

You could use:

Dim myArr As Variant
Dim NumOfRows As Long
Dim NumOfCols As Long

myArr = ActiveSheet.Range("a1:c1")

NumOfRows = UBound(myArr, 1) - LBound(myArr) + 1
NumOfCols = UBound(myArr, 2) - LBound(myArr) + 1

MsgBox NumOfRows & vbLf & NumOfCols & vbLf & NumOfCols * NumOfRows

 

[Gary Keramidas]

thanks dave, i kept that info from a previous post of yours. what was throwing
me was
UBound(Arr1, 2) - LBound(Arr1) + 1
returned 3,
which is correct, but i thought it just may be a coincidence that (Arr1, 2) was
returning 3 because i had 3 cells in in row.

 

[Dave Peterson]

OOOOhhhh!

I had a couple of typos. I wanted this:

Dim myArr As Variant
Dim NumOfRows As Long
Dim NumOfCols As Long

myArr = ActiveSheet.Range("a1:c1")

NumOfRows = UBound(myArr, 1) - LBound(myArr, 1) + 1
NumOfCols = UBound(myArr, 2) - LBound(myArr, 2) + 1

MsgBox NumOfRows & vbLf & NumOfCols & vbLf & NumOfCols * NumOfRows

Sorry about that!