Populating and Comparing Arrays

[Nick]

I want to feed two sets of cells into two arrays and then compare the array
values. If the values compared within the array match I want to add 1 to a
variable I called "Points". This is the code I have so far to do this:



Option Explicit
Dim Entry1()
Dim Checker()
Dim Points As Integer
Dim X As Integer



Sub Compare()
For X = 1 To 2
Entry1(X) = Workbooks("Entry1.xls").Sheets("sheet1").Cells(3 + (5 * X),
4).Value 'This is what the debugger keeps flagging as wrong
Checker(X) = Sheets("Source").Cells(3 + (5 * X), 4).Value

If Entry1(X) = Checker(X) Then Points = Points + 1
Next X
ThisWorkbook.Sheets("Summary").Range("c6") = Points


End Sub

 

[Paul Robinson]

Hi
You must say what size the array is and what data type. Do you really
want variable declaration outside the sub too? Put the Dim statements
inside the sub if they are only used there.

Option Explicit
Dim Entry1(1 to 2) as Double 'or integer etc
Dim Checker(1 to 2) as Double
Dim Points As Integer
Dim X As Integer

Sub Compare()

For X = 1 To 2
Entry1(X) = Workbooks("Entry1.xls").Sheets("sheet1").Cells(3 + (5 *
X),
4).Value 'This is what the debugger keeps flagging
as wrong
Checker(X) = Sheets("Source").Cells(3 + (5 * X), 4).Value

If Entry1(X) = Checker(X) Then Points = Points + 1
Next X
ThisWorkbook.Sheets("Summary").Range("c6") = Points

End Sub

regards
Paul

 

[Herbert Seidenberg]

Excel 2007 Tables
Compare
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_23_10.xlsm
http://www.mediafire.com/file/jnqmgvmw2wn/03_23_10.pdf

 

[Nick]

Thanks Paul,

I tried your suggestions but I still get flagged on the same line. Entry
and Checker are storing text. I declared them as variants because that is
the only thing I know to do. Any other suggestions? This is what I have now:

Option Explicit
Dim Entry(1 To 2) As Variant
Dim Checker(1 To 2) As Variant
Dim Points As Integer
Dim X As Integer

Sub Compare()
For X = 1 To 2
Entry(X) = Workbooks("Entry1.xls").Sheets("Sheet1").Range(Cells(3 + (5 * X),
4)).Value
Checker(X) = Sheets("Source").Range(Cells(3 + (5 * X), 4)).Value
If Entry(X) = Checker(X) Then Points = Points + 1
Next X
ThisWorkbook.Sheets("Summary").Range("c6") = Points


End Sub

 

[Nick]

Thanks,

The second link (www.meidafire...") has a macro that works if the data I
want to compare was in a table where the data items are in adjacent cells.
However, I would like to compare every 5th cell from a certain point (Cells(3
+ (5 * X)). Can I alter your code to do this. With your method are the
variables 'Entry' and 'Checker' arrays? Or is this something else that seems
to have the same function?

Option Explicit
Dim Entry As Variant
Dim Checker As Variant
Dim Points As Integer
Dim N As Integer
Dim I As Integer


Sub Compare()
With Workbooks("Entry1.xls").Sheets("Sheet1").Range("table1")
Entry = .Value
End With
With Sheets("Source").Range("table2")
Checker = .Value
End With
For I = 1 To UBound(Checker, 2)
For N = 1 To UBound(Checker, 1)

If Entry(N, I) = Checker(N, I) Then Points = Points + 1

Next N
Next I

ThisWorkbook.Sheets("Summary").Range("c6") = Points


End Sub

 

[Paul Robinson]

Hi
Entry1 has now become Entry in this second version of your problem. Is
that the problem?
To declare text use
Dim Entry1(1 to 2) as String

should work ok with Variant, so maybe it is the Entry/Entry1 issue.
regards
Paul