Error Code when compare cells in excel macro
 

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
-------
BTW, I tried reformatting the columns I'm comparing from "General" to
"Number" to no avail. Any suggestions would be greatly appreciated.

Error Code when compare cells in excel macro
 

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

Jim Thomlinson


"todd" wrote:

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
-------
BTW, I tried reformatting the columns I'm comparing from "General" to
"Number" to no avail. Any suggestions would be greatly appreciated.

Error Code when compare cells in excel macro
 

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.

--
Regards,
Tom Ogilvy



"todd" wrote in message
...
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
-------
BTW, I tried reformatting the columns I'm comparing from "General" to
"Number" to no avail. Any suggestions would be greatly appreciated.

Error Code when compare cells in excel macro
 

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

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.

--
Regards,
Tom Ogilvy



"todd" wrote in message
...
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
-------
BTW, I tried reformatting the columns I'm comparing from "General" to
"Number" to no avail. Any suggestions would be greatly appreciated.

Error Code when compare cells in excel macro
 

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.

--
Regards,
Tom Ogilvy

"todd" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy



"todd" wrote in message
...
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
-------
BTW, I tried reformatting the columns I'm comparing from "General" to
"Number" to no avail. Any suggestions would be greatly appreciated.