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

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

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

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?