Prev: Kill Open Read Only Message on Open
Next: Special character in Input Message - Data Validation
From: sebastico on 18 May 2010 15:24
Hello
I have this code taht when I run displays Run-timeerror 1004:
Application-defined or object-defined error.

Could you suggest me how to fix it?

Thanks in advance

Sub Copy_transpose()
Dim Row As Integer, Cols As Byte, nRow As Integer
Application.ScreenUpdating = False
nRow = 1

With Worksheets("Sheet1")
For Row = 1 To 668
Cols = Application.CountA(.Range("a" & Row).EntireRow) - 1
Worksheets("Sheet1").Range("a" & nRow).Resize(Cols).Value = .Range("a" &
Row).Value
Worksheets("Sheet1").Range("b" & nRow).Resize(Cols).Value =
Application.Transpose(.Range("b" & Row).Resize(, Cols).Value)
nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
Next
End With
End Sub
From: Rick Rothstein on 18 May 2010 15:48
Can you explain, in words, what this code is supposed to be doing?

--
Rick (MVP - Excel)



"sebastico" <sebastico(a)discussions.microsoft.com> wrote in message
news:685A6A7A-CCEA-447F-BBC5-E89E9645801C(a)microsoft.com...
> Hello
> I have this code taht when I run displays Run-timeerror 1004:
> Application-defined or object-defined error.
>
> Could you suggest me how to fix it?
>
> Thanks in advance
>
> Sub Copy_transpose()
> Dim Row As Integer, Cols As Byte, nRow As Integer
> Application.ScreenUpdating = False
> nRow = 1
>
> With Worksheets("Sheet1")
> For Row = 1 To 668
> Cols = Application.CountA(.Range("a" & Row).EntireRow) - 1
> Worksheets("Sheet1").Range("a" & nRow).Resize(Cols).Value = .Range("a" &
> Row).Value
> Worksheets("Sheet1").Range("b" & nRow).Resize(Cols).Value =
> Application.Transpose(.Range("b" & Row).Resize(, Cols).Value)
> nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
> Next
> End With
> End Sub

From: Barb Reinhardt on 18 May 2010 15:56
Several suggestions:

1. Add the following at the beginning


Dim myWS as Excel.Worksheet

Set myWS = Worksheets("Sheet1")

Anywhere you have Worksheets("Sheet1"), put myWS

I think your resize piece is wrong. You need both a row and column entry
and both must be 1 or greater.
--
HTH,

Barb Reinhardt



"sebastico" wrote:

> Hello
> I have this code taht when I run displays Run-timeerror 1004:
> Application-defined or object-defined error.
>
> Could you suggest me how to fix it?
>
> Thanks in advance
>
> Sub Copy_transpose()
> Dim Row As Integer, Cols As Byte, nRow As Integer
> Application.ScreenUpdating = False
> nRow = 1
>
> With Worksheets("Sheet1")
> For Row = 1 To 668
> Cols = Application.CountA(.Range("a" & Row).EntireRow) - 1
> Worksheets("Sheet1").Range("a" & nRow).Resize(Cols).Value = .Range("a" &
> Row).Value
> Worksheets("Sheet1").Range("b" & nRow).Resize(Cols).Value =
> Application.Transpose(.Range("b" & Row).Resize(, Cols).Value)
> nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
> Next
> End With
> End Sub
From: Barb Reinhardt on 18 May 2010 15:57
Lastly, I think I'd replace

"a65536" with "a" & myws.rows.count

the 65536 is applicable for Excel 2003, but not 2007.
--
HTH,

Barb Reinhardt



"sebastico" wrote:

> Hello
> I have this code taht when I run displays Run-timeerror 1004:
> Application-defined or object-defined error.
>
> Could you suggest me how to fix it?
>
> Thanks in advance
>
> Sub Copy_transpose()
> Dim Row As Integer, Cols As Byte, nRow As Integer
> Application.ScreenUpdating = False
> nRow = 1
>
> With Worksheets("Sheet1")
> For Row = 1 To 668
> Cols = Application.CountA(.Range("a" & Row).EntireRow) - 1
> Worksheets("Sheet1").Range("a" & nRow).Resize(Cols).Value = .Range("a" &
> Row).Value
> Worksheets("Sheet1").Range("b" & nRow).Resize(Cols).Value =
> Application.Transpose(.Range("b" & Row).Resize(, Cols).Value)
> nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
> Next
> End With
> End Sub
From: Dave Peterson on 18 May 2010 16:11
First, I wouldn't use Row as a variable name. I wouldn't use "As Integer" or
"as Byte" either.

And since you're within a "With/End With" block, you can drop some of those
Worksheets("Sheet1") references.

Wait! Wait!

Those references are probably typos. You want the info to go to Sheet2!

And depending on your data, your code could be having trouble with the
..resize(cols) expression.

If there's 1 entry or 0 entries in that row, then it would cause a 1004 error.

But that's a guess, since you didn't say what line caused the error.

I have no idea if this does what you want/expect, but it did compile and run for me:

Option Explicit
Sub Copy_transpose()
Dim myRow As Long
Dim Cols As Long
Dim nRow As Long
Application.ScreenUpdating = False
nRow = 1

With Worksheets("Sheet1")
For myRow = 1 To 668
Cols = Application.CountA(.Range("a" & myRow).EntireRow) - 1

If Cols < 1 Then
'do nothing
Else
Worksheets("Sheet2").Rows(nRow).Resize(Cols).Value _
= .Range("a" & myRow).Value

Worksheets("Sheet2").Range("b" & nRow).Resize(Cols).Value _
= Application.Transpose(.Range("b" & myRow) _
.Resize(, Cols).Value)

nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
End If

Next myRow
End With
End Sub




On 05/18/2010 14:24, sebastico wrote:
> Hello
> I have this code taht when I run displays Run-timeerror 1004:
> Application-defined or object-defined error.
>
> Could you suggest me how to fix it?
>
> Thanks in advance
>
> Sub Copy_transpose()
> Dim Row As Integer, Cols As Byte, nRow As Integer
> Application.ScreenUpdating = False
> nRow = 1
>
> With Worksheets("Sheet1")
> For Row = 1 To 668
> Cols = Application.CountA(.Range("a"& Row).EntireRow) - 1
> Worksheets("Sheet1").Range("a"& nRow).Resize(Cols).Value = .Range("a"&
> Row).Value
> Worksheets("Sheet1").Range("b"& nRow).Resize(Cols).Value =
> Application.Transpose(.Range("b"& Row).Resize(, Cols).Value)
> nRow = Worksheets("Sheet2").Range("a65536").End(xlUp).Row + 3
> Next
> End With
> End Sub
 |  Next  |  Last
Pages: 1 2
Prev: Kill Open Read Only Message on Open
Next: Special character in Input Message - Data Validation