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?

I also noted the SET that assigned the object Range to the variable.

If I would wish to go a step further yet, and instead of building the array
based on the worksheet range A1:A9, I decide to use one cell A1 to enter an
array of numbers (array1) with each number entered creating what would be
effectively a worksheet event on that single cell, "A1". The array would
have a variable length perhaps only confirmable via the .count method.
Similarly, array2 is created from the entry of numbers into another single
cell, "B1". All other factors being similar. What would be the
modifications required?


"Joel" wrote:

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

I think the best way is to add an Input box to select the region. Other
alternatives woul be to hight the area before you run the macro or to select
the first cell of the region. Entering a number like you suggest will also
work if the input data start a fixed offset from this number llike over one
column to the right. See if you like what I did below. If not I will try
again.

Sub Crossfunction()

Dim C As Variant

Worksheets("Sheet1").Activate
Set InputData = Application.InputBox( _
prompt:="Select cells", Type:=8)

Set Array1 = InputData. _
Resize(1, InputData.Columns.Count)
Set Array2 = InputData.Offset(1, 0). _
Resize(1, InputData.Columns.Count)

'commented out
'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:

> I also noted the SET that assigned the object Range to the variable.
>
> If I would wish to go a step further yet, and instead of building the array
> based on the worksheet range A1:A9, I decide to use one cell A1 to enter an
> array of numbers (array1) with each number entered creating what would be
> effectively a worksheet event on that single cell, "A1". The array would
> have a variable length perhaps only confirmable via the .count method.
> Similarly, array2 is created from the entry of numbers into another single
> cell, "B1". All other factors being similar. What would be the
> modifications required?
>
>
> "Joel" wrote:
>
> > 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?

This is not what I wanted. The essential concept is for the array1 to be
entered in "A1" and array2 in "B2" and these numbers are stored in each of
their respective arrays and then compared, and not stored on the worksheet
before compare. That would be similar to the last construct that assigned
(SET) the object Ranges to their respective variables (Array1,Array2).
One such possible scenario would use the worksheet event on say "A1" and
"B1", that takes place with the entry of data in the respective cell, which
triggers (via private sub worksheet_change... etc. ) a loop within a sub
routine that is used to populate the array1 and array2 respectively, and
this is then used for the comparison. Thus, the entry othef numbers, one
following (and overwriting the other) would be stored in an array that
enables processing in a manner outlined in your previous posts.

"Joel" wrote:

> I think the best way is to add an Input box to select the region. Other
> alternatives woul be to hight the area before you run the macro or to select
> the first cell of the region. Entering a number like you suggest will also
> work if the input data start a fixed offset from this number llike over one
> column to the right. See if you like what I did below. If not I will try
> again.
>
> Sub Crossfunction()
>
> Dim C As Variant
>
> Worksheets("Sheet1").Activate
> Set InputData = Application.InputBox( _
> prompt:="Select cells", Type:=8)
>
> Set Array1 = InputData. _
> Resize(1, InputData.Columns.Count)
> Set Array2 = InputData.Offset(1, 0). _
> Resize(1, InputData.Columns.Count)
>
> 'commented out
> '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:
>
> > I also noted the SET that assigned the object Range to the variable.
> >
> > If I would wish to go a step further yet, and instead of building the array
> > based on the worksheet range A1:A9, I decide to use one cell A1 to enter an
> > array of numbers (array1) with each number entered creating what would be
> > effectively a worksheet event on that single cell, "A1". The array would
> > have a variable length perhaps only confirmable via the .count method.
> > Similarly, array2 is created from the entry of numbers into another single
> > cell, "B1". All other factors being similar. What would be the
> > modifications required?
> >
> >
> > "Joel" wrote:
> >
> > > 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?

I'm trying to come up with a solution that would work. If you don't like my
suggestions you are welcome to post your questions again to get other peoples
answers. Most peopel for data entry design a userform with two text boxes
and a control button to run the macro. the control button will move the data
to the worksheet and run the Cross function.

What ever solution you get you have to remember two things

1) VBA will not remember data that is enter. You need to stroe the data
someplace on the worksheet for VBA to get each time the code is run. This
could be a hidden area of the workbook that the user wouldn't normally see
like Column "IV" which is the 256 column.

2) If you always have the data entered in the same cell then each time the
data is entered you have to move the data out of the cell so it is empty the
next time you enter data.


"Gum" wrote:

> This is not what I wanted. The essential concept is for the array1 to be
> entered in "A1" and array2 in "B2" and these numbers are stored in each of
> their respective arrays and then compared, and not stored on the worksheet
> before compare. That would be similar to the last construct that assigned
> (SET) the object Ranges to their respective variables (Array1,Array2).
> One such possible scenario would use the worksheet event on say "A1" and
> "B1", that takes place with the entry of data in the respective cell, which
> triggers (via private sub worksheet_change... etc. ) a loop within a sub
> routine that is used to populate the array1 and array2 respectively, and
> this is then used for the comparison. Thus, the entry othef numbers, one
> following (and overwriting the other) would be stored in an array that
> enables processing in a manner outlined in your previous posts.
>
> "Joel" wrote:
>
> > I think the best way is to add an Input box to select the region. Other
> > alternatives woul be to hight the area before you run the macro or to select
> > the first cell of the region. Entering a number like you suggest will also
> > work if the input data start a fixed offset from this number llike over one
> > column to the right. See if you like what I did below. If not I will try
> > again.
> >
> > Sub Crossfunction()
> >
> > Dim C As Variant
> >
> > Worksheets("Sheet1").Activate
> > Set InputData = Application.InputBox( _
> > prompt:="Select cells", Type:=8)
> >
> > Set Array1 = InputData. _
> > Resize(1, InputData.Columns.Count)
> > Set Array2 = InputData.Offset(1, 0). _
> > Resize(1, InputData.Columns.Count)
> >
> > 'commented out
> > '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:
> >
> > > I also noted the SET that assigned the object Range to the variable.
> > >
> > > If I would wish to go a step further yet, and instead of building the array
> > > based on the worksheet range A1:A9, I decide to use one cell A1 to enter an
> > > array of numbers (array1) with each number entered creating what would be
> > > effectively a worksheet event on that single cell, "A1". The array would
> > > have a variable length perhaps only confirmable via the .count method.
> > > Similarly, array2 is created from the entry of numbers into another single
> > > cell, "B1". All other factors being similar. What would be the
> > > modifications required?
> > >
> > >
> > > "Joel" wrote:
> > >
> > > > 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?