RE: Cross Function when array crosses above another

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Relevant Pages

  • RE: Cross Function when array crosses above another
    ... Private Sub CommandButton1_Click ... 'Add the following arrays that are sourced from the worksheet: ... a cell where you want to start storing the data and then press OK. ...
    (microsoft.public.excel.programming)
  • RE: Cross Function when array crosses above another
    ... Sub Crossfunction() ... 'Add the following arrays that are sourced from the worksheet: ... "Gum" wrote: ...
    (microsoft.public.excel.programming)
  • RE: Cross Function when array crosses above another
    ... someplace on the worksheet for VBA to get each time the code is run. ... If you always have the data entered in the same cell then each time the ... Sub Crossfunction() ... 'Add the following arrays that are sourced from the worksheet: ...
    (microsoft.public.excel.programming)
  • RE: Cross Function when array crosses above another
    ... columns of data Array2 Data will be off set worksheet. ... Private Sub CommandButton1_Click ... Range.Offset= Array1 ... would effectively limit the 'memory drag' as the array size grows. ...
    (microsoft.public.excel.programming)
  • RE: Cross Function when array crosses above another
    ... "Joel" wrote: ... Sub Crossfunction() ... 'Add the following arrays that are sourced from the worksheet: ...
    (microsoft.public.excel.programming)