Error Code when compare cells in excel macro

[Guest]

I am trying to compare values on worksheet1 to worksheet2, and copy some of
the data to worksheet3 based upon the if statement results. I've seen a
couple posts discussing what I am trying to do; tried their codes and my own,
and continue to get this error...

"Run-time error ‘1004’:

Application-defined or object-defined error"

The "if" line gets highlighted upon macro execution.
-----
Sub correlate()
Dim i, j, k As Integer
For i = 1 To 500 'correlation worksheet
For j = 2 To 11933 'ctsytd05 worksheet
For k = 2 To 14010 'srytd05 worksheet
If Worksheets("srytd05").Cells(4, k).Value =
Worksheets("ctsytd05").Cells(3, j).Value Then
Worksheets("correlation").Cells(1, i - 1).Value =
Worksheets("srytd05").Cells(4, k - 1).Value
Worksheets("correlation").Cells(2, i - 1).Value =
Worksheets("srytd05").Cells(8, k - 1).Value
Worksheets("correlation").Cells(3, i - 1).Value =
Worksheets("ctsytd05").Cells(1, j - 1).Value
End If
Next k
End
Next j
End
Next i
End
End Sub

 

[Guest]

If Worksheets("srytd05").Cells(4, k).Value =
Worksheets("ctsytd05").Cells(3, j).Value Then
Worksheets("correlation").Cells(1, i - 1).Value =
Worksheets("srytd05").Cells(4, k - 1).Value

I think You have your parameters backwards in these statements. Cells looks
at rows and then columns. You are incrementing j and k well beyond the 256
columns in the workbook. Also your variables are declared incorrectly (not
that it causes teh error in this case). Both i and J are of type variant, not
integer. Check out this link...

http://www.cpearson.com/excel/variables.htm

 

[Tom Ogilvy]

i - 1 = 0

cells(anything,0) will raise an error

Look for these situations and adjust your code

also, there are only 256 columns in a spreadsheet and you are looping 1 to
500 in the column position. That won't fly either. the cells object is

cells(rownumber, columnumber)

I think you have your references reversed.

Beyond that, I doubt this algorithm will do what you want since you replace
values rather than accumulate. I doubt you want the i loop, but probably
want a variable that implements 1 each time a row is consumed in recording
data. That is just a guess however.

 

[Guest]

Tom,

Thanks for helping me think through this - I added a "i" counter; rearranged
the "Cells" rows and columns; and "stepped" through the code to learn that
the "End" statements required re-orientation. For anyone who can learn from
it, here is the revised code
-----
Sub correlate()
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2 'correlation worksheet row counter
For j = 2 To 11933 'ctsytd05 worksheet
For k = 2 To 14010 'srytd05 worksheet
If (Worksheets("srytd05").Cells(k, 4).Value =
Worksheets("ctsytd05").Cells(j, 3).Value) Then 'rows then cells
Worksheets("correlation").Cells(i, 1).Value =
Worksheets("srytd05").Cells(k, 4).Value
Worksheets("correlation").Cells(i, 2).Value =
Worksheets("srytd05").Cells(k, 8).Value
Worksheets("correlation").Cells(i, 3).Value =
Worksheets("ctsytd05").Cells(j, 1).Value
End If
Next k
Next j
End
End
End Sub
-----

 

[Tom Ogilvy]

Just a thought, but you don't increment i:

Sub correlate()
Dim i As Long
Dim j As Long
Dim k As Long
i = 2 'correlation worksheet row counter
For j = 2 To 11933 'ctsytd05 worksheet
For k = 2 To 14010 'srytd05 worksheet
If (Worksheets("srytd05").Cells(k, 4).Value = _
Worksheets("ctsytd05").Cells(j, 3).Value) Then
Worksheets("correlation").Cells(i, 1).Value = _
Worksheets("srytd05").Cells(k, 4).Value
Worksheets("correlation").Cells(i, 2).Value = _
Worksheets("srytd05").Cells(k, 8).Value
Worksheets("correlation").Cells(i, 3).Value = _
Worksheets("ctsytd05").Cells(j, 1).Value

i = i + 1 '<====

End If
Next k
Next j
End Sub

No advantage to using integer and Long is more flexible.