Excel VBA Editor:
I would like to create a cross function that evaluates 2 arrays: array1 and
array2. When array1 crosses above array2, then the function is true for that
instant, otherwise it is false.
Any suggestions?

Sub Crossfunction()

Dim C As Variant

Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)


ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub


"Gum" wrote:

Excel VBA Editor:
I would like to create a cross function that evaluates 2 arrays: array1 and
array2. When array1 crosses above array2, then the function is true for that
instant, otherwise it is false.
Any suggestions?

It works! If I need to source the array from the spread sheet and add the
following:
Sub Crossfunction()

Dim C As Variant

'Instead of:
'Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
'Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)

'Add the following arrays that are sourced from the worksheet:
array1=Range("A1:A9").Value
array2=Range("B1:B9").Value

ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub

This results in a 'subscript out of range' error runtime error '9'
Why?
The boundaries for the loop: LBound(Array1) is 1 and UBound(Array1) is 9
and during the first pass Array1(1) and Array2(1) are both 'out of range'.

how to resolve the error?
Could it arise from the object being poorly defined that the data is not
found, despite there being only one worksheet in the book?

"Joel" wrote:

Sub Crossfunction()

Dim C As Variant

Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)


ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub


"Gum" wrote:

Excel VBA Editor:
I would like to create a cross function that evaluates 2 arrays: array1 and
array2. When array1 crosses above array2, then the function is true for that
instant, otherwise it is false.
Any suggestions?

The index of arrays are usually 0 to (size - 1), but you can ignore item 0.
With ranges on worksheets they start at index 1. Had to make some slight
changes.

Sub Crossfunction()

Dim C As Variant

'Add the following arrays that are sourced from the worksheet:
Set Array1 = Range("A1:A9")
Set Array2 = Range("B1:B9")

ReDim C(Array1.Count)


Above = True
For i = 1 To Array1.Count
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub

"Gum" wrote:

It works! If I need to source the array from the spread sheet and add the
following:
Sub Crossfunction()

Dim C As Variant

'Instead of:
'Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
'Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)

'Add the following arrays that are sourced from the worksheet:
array1=Range("A1:A9").Value
array2=Range("B1:B9").Value

ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub

This results in a 'subscript out of range' error runtime error '9'
Why?
The boundaries for the loop: LBound(Array1) is 1 and UBound(Array1) is 9
and during the first pass Array1(1) and Array2(1) are both 'out of range'.

how to resolve the error?
Could it arise from the object being poorly defined that the data is not
found, despite there being only one worksheet in the book?

"Joel" wrote:

Sub Crossfunction()

Dim C As Variant

Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)


ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub


"Gum" wrote:

Excel VBA Editor:
I would like to create a cross function that evaluates 2 arrays: array1 and
array2. When array1 crosses above array2, then the function is true for that
instant, otherwise it is false.
Any suggestions?

It worked! I thought that that could the problem but when I used 'Option
Base 1' without success, this suggested a further look.

Thanks!

"Joel" wrote:

The index of arrays are usually 0 to (size - 1), but you can ignore item 0.
With ranges on worksheets they start at index 1. Had to make some slight
changes.

Sub Crossfunction()

Dim C As Variant

'Add the following arrays that are sourced from the worksheet:
Set Array1 = Range("A1:A9")
Set Array2 = Range("B1:B9")

ReDim C(Array1.Count)


Above = True
For i = 1 To Array1.Count
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub

"Gum" wrote:

It works! If I need to source the array from the spread sheet and add the
following:
Sub Crossfunction()

Dim C As Variant

'Instead of:
'Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
'Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)

'Add the following arrays that are sourced from the worksheet:
array1=Range("A1:A9").Value
array2=Range("B1:B9").Value

ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub

This results in a 'subscript out of range' error runtime error '9'
Why?
The boundaries for the loop: LBound(Array1) is 1 and UBound(Array1) is 9
and during the first pass Array1(1) and Array2(1) are both 'out of range'.

how to resolve the error?
Could it arise from the object being poorly defined that the data is not
found, despite there being only one worksheet in the book?

"Joel" wrote:

Sub Crossfunction()

Dim C As Variant

Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)


ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub


"Gum" wrote:

Excel VBA Editor:
I would like to create a cross function that evaluates 2 arrays: array1 and
array2. When array1 crosses above array2, then the function is true for that
instant, otherwise it is false.
Any suggestions?

Option Base 1 willnot change the worksheet Range items. Range doesn't like
zero as an index. I also had to put SET infront of Array1 and Array2.

"Gum" wrote:

It worked! I thought that that could the problem but when I used 'Option
Base 1' without success, this suggested a further look.

Thanks!

"Joel" wrote:

The index of arrays are usually 0 to (size - 1), but you can ignore item 0.
With ranges on worksheets they start at index 1. Had to make some slight
changes.

Sub Crossfunction()

Dim C As Variant

'Add the following arrays that are sourced from the worksheet:
Set Array1 = Range("A1:A9")
Set Array2 = Range("B1:B9")

ReDim C(Array1.Count)


Above = True
For i = 1 To Array1.Count
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub

"Gum" wrote:

It works! If I need to source the array from the spread sheet and add the
following:
Sub Crossfunction()

Dim C As Variant

'Instead of:
'Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
'Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)

'Add the following arrays that are sourced from the worksheet:
array1=Range("A1:A9").Value
array2=Range("B1:B9").Value

ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub

This results in a 'subscript out of range' error runtime error '9'
Why?
The boundaries for the loop: LBound(Array1) is 1 and UBound(Array1) is 9
and during the first pass Array1(1) and Array2(1) are both 'out of range'.

how to resolve the error?
Could it arise from the object being poorly defined that the data is not
found, despite there being only one worksheet in the book?

"Joel" wrote:

Sub Crossfunction()

Dim C As Variant

Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
Array2 = Array(1, 4, 3, 7, 5, 8, 7, 2, 9)


ReDim C(UBound(Array1))

Above = True
For i = LBound(Array1) To UBound(Array1)
C(i) = (Not Above) And (Array1(i) Array2(i))
Above = Array1(i) Array2(i)
Next i

End Sub


"Gum" wrote:

Excel VBA Editor:
I would like to create a cross function that evaluates 2 arrays: array1 and
array2. When array1 crosses above array2, then the function is true for that
instant, otherwise it is false.
Any suggestions?