Copy from 1 cell to another

[NewbiDoobie]

I have a loop that goes through and copies information from a spreasheet into another.

All of the cells are being copied cept for one which keeps displaying as a #Value.

It is the same field each time. I can diplay it in a message box with no problems. I thought if I changed from a .Value to .Text that this would fix it as it has special characters and such.

When I did I get an error: 1004 Unable to set the text property of the range class.

Here is the code I am using:

Worksheets("Repair Track Info").Range("B3").Value = Worksheets("RTDATA").Cells(i, 5)
Worksheets("Repair Track Info").Range("B7").Value = Worksheets("RTDATA").Cells(i, 6)
Worksheets("Repair Track Info").Range("B9").Value = Worksheets("RTDATA").Cells(i, 3)
MsgBox Worksheets("RTDATA").Cells(i, 4)
Worksheets("Repair Track Info").Range("B12") = Worksheets("RTDATA").Cells(i, 4)

 

[Zathras]

.Text is read-only so that cooks that.

What exactly is in cell "B12"? Perhaps it is a formula? What happens when you copy manually in the workbook?

 

[NewbiDoobie]

there is nothing in B12. I can copy it maually, just not through code

 

[Zathras]

I just tried it and your code works fine. There must be something you aren't telling us. Merged cells? Protected cells? Protected sheet?

What exactly is in cell Worksheets("RTDATA").Cells(i, 4)? And what is the value of "i" when the macro fails? And what error message do you get?

You have to give us something to work with here.

 

[NewbiDoobie]

A text string up to 2000 char including special characters is what is in the cell.

the value if I is the value of the row that the inforamtion is in and it is correct as I can view the information in a messagebox prior to the imput.

I am only getting an error if I try to use .text instead of .Value.

If I use .Value I get #Value as the output. If I do a direct reference it copies over fine.

 

[Zathras]

That's what you didn't tell us. Long strings can cause problems in Excel. Try a variation like this:

Worksheets("Repair Track Info").Range("B3").Value = Worksheets("RTDATA").Cells(i, 5)
Worksheets("Repair Track Info").Range("B7").Value = Worksheets("RTDATA").Cells(i, 6)
Worksheets("Repair Track Info").Range("B9").Value = Worksheets("RTDATA").Cells(i, 3)
s = Worksheets("RTDATA").Cells(i, 4)
MsgBox Len(s) & ":  " & s
Worksheets("Repair Track Info").Range("B12") = s

Some places the number of characters that can be handled is greatly reduced. In this case when trying to go from cell to cell, Excel places a limit of less than 2000 characters. (It seems to be limited to 1,024 on my version.) When copying manually, Excel uses the clipboard, so - no limit. When using the variation given here, Excel uses a variable, so again - no limit. Actually there is a limit, but it is much larger than 2000 characters.

BTW, you really should define your variables explicitly and put Option Explicit at the top of the code. That will make you life a lot easier in the future:

Option Explicit
Sub MyRoutine()
Dim s As String
Dim i As Long
:
:
End Sub