Run-time error 1004- Flag or Fix

[cmbacci]

Hi there. I hope someone can help me figure this out because it's driving me crazy! I have been running this code for a while on a report and it has always worked until I just got this message: Run-time error '1004': Application-defined or object-defined error. After troubleshooting, I think it's because I have 9100 characters in the cell I'm trying to copy. I am looking for one of 2 solutions.

1. Increase the character limit that can be copied if 2 cells don't match
2. Flag the cells that won't copy because of this error with red highlighting and maybe pop up a box at the end informing that some data didn't copy - so at least the user will know which cells failed.

Sub CNew()

Dim rngSelection    As Range
Dim rngRow          As Range
Dim cell            As Range

Set rngSelection = Range("Tracker")

On Error Resume Next

For Each rngRow In rngSelection.Rows
    If rngRow.Cells(1, 1) <> rngRow.Cells(1, 32) Then
        rngRow.Cells(1, 1) = rngRow.Cells(1, 32)
  
    End If
Next

End Sub

 

[mole999]

when you step through with f8 what is the error row. Excel allows you to enter up to 32,767 characters in each cell.

 

[Peter_SSs]

When posting vba code please use code tags. More information in my signature block below. I have fixed this in your post this time.

What happens if you run this code (on a copy of your workbook) instead?

Sub CNew()
  Range("Tracker").Columns(1).Value = Range("Tracker").Columns(32).Value
End Sub

 

[cmbacci]

Thank you for your response and correcting my post. It was my first time posting and I am very new to this. I tried your suggestion and it doesn't error out, but it's not copying the right information. It's pulling in from the right column but not from the same row. What I want to happen, is for it to copy from one column to the other within the same row - but only if they don't already match.

I've been using the code for probably a year and it has never been an issue until I had this report that had 9,000+ characters in the cell. If I tell it to resume next on error, it works for all 1000+ lines except for the handful that have the 9000+ characters in the cell. I know that Excel can accommodate over 30,000 characters.

This likely won't happen often, so I'd be happy with resuming next on error and just flagging the cells that didn't copy- but I can't figure out how to do that either.

Thanks again!

 

[cmbacci]

Thank you- I know that the cells can accommodate over 30000 characters, which is why this is so baffling to me.
My code is actually longer than what I posted but it just repeats for different columns so I only posted one part for simplicity. It fails only when I tell it to copy whats in column 32 to column 1 (this line: rngRow.Cells(1, 1) = rngRow.Cells(1, 32)))

and it only fails for a handful of rows where there are 9000 characters - all the rest work fine if I tell it to resume next on error. I have it copying like 10 other columns with no issue and I've been using this for a year with no issues.

Thanks again!

Chrissy

 

[Peter_SSs]

It was my first time posting and I am very new to this.

No problem.

It's pulling in from the right column but not from the same row.

I cannot see how my code could do that & I have not been able to reproduce that situation. Did you alter the code in any way or include it with your other code? If so, try it on its own.

but only if they don't already match.

Surely that is the same as copying the whole column? After all, for rows that do already match you would just be replacing a value with exactly the same value.


Doing the whole column at once seems much more efficient than processing one row at a time. However, if you want to stick to the row-by-row approach, specifying the relevant Cells property should resolve your problem.

Sub CNew()

Dim rngSelection    As Range
Dim rngRow          As Range
Dim cell            As Range

Set rngSelection = Range("Tracker")

On Error Resume Next

For Each rngRow In rngSelection.Rows
    If rngRow.Cells(1, 1).Value <> rngRow.Cells(1, 32).Value Then
        rngRow.Cells(1, 1).Value = rngRow.Cells(1, 32).Value
  
    End If
Next

End Sub

 

[cmbacci]

Thank you Peter- You are 100% correct. I don't know what I was thinking My first code highlights it if it's different. So now I can copy the whole column. If I take out the "IF" part of my code, it works properly. Thanks so much.

On another note, how did you split up my message and turn it orange and respond to each part? Thanks!

 

[Peter_SSs]

how did you split up my message and turn it orange and respond to each part? Thanks!

Here are three ways:

1. Select the part you want to quote, wait a second or two then click the 'Reply' pop-up.
   
   
   
   
   
   
   
2. Click 'Reply' at the bottom right of the post then delete what you don't want when it appears in the Reply window
   
   
   
   
   
   
   
   
   
   
3. Manually type quote tags like this and then copy paste the part you want to quote
   [Quote][/Quote]

Methods 1 & 2 would generally be preferable as they also provide a clickable link back to the post where the quote came from. This is particularly useful if the quote came fro a much earlier post in the thread or even from another thread.

 

[cmbacci]

Methods 1 & 2 would generally be preferable as they also provide a clickable link back to the post where the quote came from. This is particularly useful if the quote came fro a much earlier post in the thread or even from another thread.

Awesome - thanks for teaching me!

 

[Peter_SSs]

You're welcome.