array question
 

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


Gary

array question
 

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

"Gary Keramidas" wrote:

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


Gary

array question
 

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary

array question
 

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.




--


Gary


"RB Smissaert" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary

array question
 

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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary

array question
 

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

"Charlie" wrote in message
...
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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary

array question
 

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?

"RB Smissaert" wrote:

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

"Charlie" wrote in message
...
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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary

array question
 

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?

--


Gary


"RB Smissaert" wrote in message
...
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

"Charlie" wrote in message
...
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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary

array question
 

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

RBS


"Charlie" wrote in message
...
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?

"RB Smissaert" wrote:

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

"Charlie" wrote in message
...
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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary

array question
 

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 wrote:

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?

--

Gary

"RB Smissaert" wrote in message
...
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

"Charlie" wrote in message
...
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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary







--

Dave Peterson

array question
 

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.



--


Gary


"Dave Peterson" wrote in message
...
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 wrote:

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?

--

Gary

"RB Smissaert" wrote in message
...
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

"Charlie" wrote in message
...
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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary







--

Dave Peterson

array question
 

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!

Gary Keramidas wrote:

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.

--

Gary

"Dave Peterson" wrote in message
...
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 wrote:

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?

--

Gary

"RB Smissaert" wrote in message
...
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

"Charlie" wrote in message
...
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" wrote:

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" <GKeramidasATmsn.com wrote in message
...
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.
--


Gary







--

Dave Peterson

--

Dave Peterson