Cross Function when array crosses above another

[Gum]

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?

 

[Joel]

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]

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]

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]

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]

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]

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]

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]

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]

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]

You are doing great so far! If the solution is to store the array data in the
"IV" sheet, is there a way to limit it to x lines (like an inventory) with
the oldest data being removed to keep the number of lines constant? This
would effectively limit the 'memory drag' as the array size grows.

 

[Joel]

You can do anything you want in the code. You just need to fix the location
of where the array are going to be stored. You can use a defined Name
"STARTDATA" to define where you want to store the data. Go to the worksheet
menu Insert - Name - Define. Then enter STARTDATA in the top box and select
a cell where you want to start storing the data and then press OK.

I would use a Control Button to enter the data because you want the data to
be entered in Pairs. The code will check that both A1 and A2 contain data.

Here is the code


Private Sub CommandButton1_Click()
Const MAXROWS = 10

'First Test if A1 and B1 both contian data
Array1 = Range("A1")
Array2 = Range("B1")

If (Not IsNumeric(Array1)) Or _
(Not IsNumeric(Array2)) Or _
Array1 = "" Or Array2 = "" Then

MsgBox ("Bad Data - Re-Enter your data")
Exit Sub
End If


If Range("startdata") = "" Then
Range("startdata") = Array1
Range("startdata").Offset(0, 1) = Array2
Else
OffsetRow = Range("startdata").End(xlDown).Row
If OffsetRow = MAXROWS Then
Range("startdata").Resize(9, 2).Offset(1, 0).Copy _
Destination:=Range("startdata")
OffsetRow = MAXROWS - 1
End If

If OffsetRow = Rows.Count Then
OffsetRow = 1
End If

Range("startdata").Offset(LastRow, 0) = Array1
Range("startdata").Offset(LastRow, 1) = Array2
End If

'Add the following arrays that are sourced from the worksheet:
LastRow = Range("startdata").End(xlDown).Row

Set Array1 = Range("startdata").Resize(LastRow, 1)
Set Array2 = Array1.Offset(0, 1)

ReDim C(LastRow)


Above = True
For i = 1 To LastRow
C(i) = (Not Above) And (Array1(i) > Array2(i))
Above = Array1(i) > Array2(i)
Next i


End Sub

 

[Gum]

I placed the code in 'Sheet1' vba code area, which corresponds to the
worksheet ("Sheet1") containing the 'A1' , 'B1' and 'startdata' items.

The line: Set Array1=Range("startdata").Resize(LastRow,1) is giving an error:

Runtime error '1004'
application-defined or object-defined error

 

[Joel]

I made the code more robust. I was only designed to start in Row1. the code
will not work under two conditions.

1) StartData cannot be located in the Last Column because you need two
columns of data Array2 Data will be off set worksheet.
2) Start Data has to be placed lower than the maximum number of rows in the
worksheet - MaxRow

If you have 10 Rows then you can't start the data at row 65534 because there
is only 65536 rows in the worksheet.

 

[Gum]

You are correct. When I debugged the code, the last row was over 1 million
rows down at the bottom of the sheet1, with empty cells between startdata and
the bottom. It was as though the assignment statement:
LastRow = Range("startdata").End(xlDown).Row
was the {End}.{downArrow} keys which in the absence of data sends the cursor
to the end of the sheet. Could it be
When I filled in some rows starting with startdata (at least 2 rows starting
with startdata), no error was returned (and the table was not filled).

 

[Joel]

It the way XLDOWN works. When you don't have any data in cell or you are at
the last row of data the xldown returns the LastRow which is 65536 in excel
2003 and over a million in Exel 2007. That is why I used Rows.Count to test
for this condition which is the last row, so the code would work in both
excel 2003 and excel 2007.

 

[Gum]

Kindly confirm the location of the code that works. My input is in the 'A1'
and 'B1' cells. The startdata name is defined as the $A$20 cell and I placed
a commandbutton1 on the sheet that is linked to the code that is placed on
'Sheet1' VBA Code Editor. I put in the data and press the command button and
get the same error as described below. My version gives the same error on
the Excel 2007 and 2003 versions. What have I done incorrectly?

 

[Joel]

Sorry, I thought I posted the corrected macro

Private Sub CommandButton1_Click()
Const MAXROWS = 10

'First Test if A1 and B1 both contian data
Array1 = Range("A1")
Array2 = Range("B1")
FirstRow = Range("startdata").Row

If (Not IsNumeric(Array1)) Or _
(Not IsNumeric(Array2)) Or _
Array1 = "" Or Array2 = "" Then

MsgBox ("Bad Data - Re-Enter your data")
Exit Sub
End If


If Range("startdata") = "" Then
Range("startdata") = Array1
Range("startdata").Offset(0, 1) = Array2
OffsetRow = 0
LastRow = Range("startdata").Row
Else
LastRow = Range("startdata").End(xlDown).Row
If LastRow - FirstRow + 1 = MAXROWS Then
Range("startdata").Resize(9, 2).Offset(1, 0).Copy _
Destination:=Range("startdata")
OffsetRow = MAXROWS - 1
Else
If LastRow = Rows.Count Then
OffsetRow = 1
Else
OffsetRow = LastRow - FirstRow + 1
End If
End If

Range("startdata").Offset(OffsetRow, 0) = Array1
Range("startdata").Offset(OffsetRow, 1) = Array2
End If

'Add the following arrays that are sourced from the worksheet:
'LastRow = Range("startdata").End(xlDown).Row

Set Array1 = Range("startdata").Resize(OffsetRow + 1, 1)
Set Array2 = Array1.Offset(0, 1)

ReDim C(LastRow)


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]

Thanks. It is working without error.

I believe the crossing logic is encapsulated in the 'above' boolean
variable, which when tested in by the sub procedure is true not only for the
instance the array1 crosses array2 but every other subsequent instance that
array1 is above array2. Simply put, when array1 > array2. However, the rule
as stated in the initial posting was that the cross should be true only for
that instance when the array1 crosses array2 and no other instance. So that
the moment the array crosses, it is true, but before and after that moment,
it is false.

When I checked the simple sample array, it appeared to answer the question,
but in looking at the more complete implementation, there seems to be a
difference? What could have happened?