array question

G

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

Jerry W. Lewis

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
 
R

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
 
G

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

Charlie

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

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

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
 
C

Charlie

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

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

RB Smissaert

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

RBS
 
D

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
 
G

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

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!
 

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