ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Application Defined or object defined error..?Need help generic er (https://www.excelbanter.com/excel-programming/433406-application-defined-object-defined-error-need-help-generic-er.html)

el dee
Application Defined or object defined error..?Need help generic er
 
Hi,
I know the 1004 error common and a problem, but I can't seem to get around
this one. I have used this code before and it worked great. I have denoted
the function on the bottom for debug....
Thanks!!

Const Maxrow = 50000
Sub Headers_To_Macro_Test()
' Headers_To_Macro_Test Macro
' Macro recorded 9/9/2009 by

Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data_Headers")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B6:C6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C:D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5:F5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E:F")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H5:I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "K:L")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E6:F6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "G:H")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E7:F7")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "I:J")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H6:I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "M:N")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "O")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "D9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "P")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "G9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "Q")
rngCopy.Copy rngPaste

End Sub


Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String) As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
**Set SetPasteRangeByColumn = Worksheets("All_Data_Headers").Cells(lngRow,
strColumn).End(xlUp).Offset(1, 0)--**Application defined or object-defined
ERROR
End Function

Barb Reinhardt
Application Defined or object defined error..?Need help generic er
 
When you call the function, you've already defined wks to be the worksheet
named "All Data Headers" in ThisWorkbook.

Try changing the offending line to this

Set SetPasteRangeByColumn = wks.Cells(lngRow, _
strColumn).End(xlUp).Offset(1, 0)

HTH,
Barb Reinhardt


"el dee" wrote:

Hi,
I know the 1004 error common and a problem, but I can't seem to get around
this one. I have used this code before and it worked great. I have denoted
the function on the bottom for debug....
Thanks!!

Const Maxrow = 50000
Sub Headers_To_Macro_Test()
' Headers_To_Macro_Test Macro
' Macro recorded 9/9/2009 by

Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data_Headers")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B6:C6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C:D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5:F5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E:F")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H5:I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "K:L")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E6:F6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "G:H")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E7:F7")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "I:J")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H6:I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "M:N")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "O")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "D9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "P")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "G9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "Q")
rngCopy.Copy rngPaste

End Sub


Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String) As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
**Set SetPasteRangeByColumn = Worksheets("All_Data_Headers").Cells(lngRow,
strColumn).End(xlUp).Offset(1, 0)--**Application defined or object-defined
ERROR
End Function

el dee
Application Defined or object defined error..?Need help generi
 
Hi Barb,
I changed the line, I had it that way to begin with actually. I am still
recieving the same error. Any other ideas?
Thanks again


"Barb Reinhardt" wrote:

When you call the function, you've already defined wks to be the worksheet
named "All Data Headers" in ThisWorkbook.

Try changing the offending line to this

Set SetPasteRangeByColumn = wks.Cells(lngRow, _
strColumn).End(xlUp).Offset(1, 0)

HTH,
Barb Reinhardt


"el dee" wrote:

Hi,
I know the 1004 error common and a problem, but I can't seem to get around
this one. I have used this code before and it worked great. I have denoted
the function on the bottom for debug....
Thanks!!

Const Maxrow = 50000
Sub Headers_To_Macro_Test()
' Headers_To_Macro_Test Macro
' Macro recorded 9/9/2009 by

Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data_Headers")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B6:C6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C:D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5:F5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E:F")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H5:I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "K:L")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E6:F6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "G:H")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E7:F7")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "I:J")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H6:I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "M:N")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "O")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "D9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "P")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "G9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "Q")
rngCopy.Copy rngPaste

End Sub


Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String) As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
**Set SetPasteRangeByColumn = Worksheets("All_Data_Headers").Cells(lngRow,
strColumn).End(xlUp).Offset(1, 0)--**Application defined or object-defined
ERROR
End Function

Barb Reinhardt
Application Defined or object defined error..?Need help generi
 
When I use .cells, I generally enter a Row # and a Column #, I don't use the
row Letter. Try this

Set SetPasteRangeByColumn = Wks.Range(strcolumn & _
lngRow).End(xlUp).Offset(1, 0)


"el dee" wrote:

Hi Barb,
I changed the line, I had it that way to begin with actually. I am still
recieving the same error. Any other ideas?
Thanks again


"Barb Reinhardt" wrote:

When you call the function, you've already defined wks to be the worksheet
named "All Data Headers" in ThisWorkbook.

Try changing the offending line to this

Set SetPasteRangeByColumn = wks.Cells(lngRow, _
strColumn).End(xlUp).Offset(1, 0)

HTH,
Barb Reinhardt


"el dee" wrote:

Hi,
I know the 1004 error common and a problem, but I can't seem to get around
this one. I have used this code before and it worked great. I have denoted
the function on the bottom for debug....
Thanks!!

Const Maxrow = 50000
Sub Headers_To_Macro_Test()
' Headers_To_Macro_Test Macro
' Macro recorded 9/9/2009 by

Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data_Headers")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B6:C6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C:D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5:F5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E:F")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H5:I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "K:L")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E6:F6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "G:H")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E7:F7")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "I:J")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H6:I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "M:N")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "O")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "D9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "P")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "G9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "Q")
rngCopy.Copy rngPaste

End Sub


Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String) As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
**Set SetPasteRangeByColumn = Worksheets("All_Data_Headers").Cells(lngRow,
strColumn).End(xlUp).Offset(1, 0)--**Application defined or object-defined
ERROR
End Function

Bob Phillips[_3_]
Application Defined or object defined error..?Need help generi
 
Make your paste range a single column, not two, even if copying two columns.

For instance


Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
rngCopy.Copy rngPaste

instead of


Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

--
__________________________________
HTH

Bob

"el dee" wrote in message
...
Hi Barb,
I changed the line, I had it that way to begin with actually. I am still
recieving the same error. Any other ideas?
Thanks again


"Barb Reinhardt" wrote:

When you call the function, you've already defined wks to be the
worksheet
named "All Data Headers" in ThisWorkbook.

Try changing the offending line to this

Set SetPasteRangeByColumn = wks.Cells(lngRow, _
strColumn).End(xlUp).Offset(1, 0)

HTH,
Barb Reinhardt


"el dee" wrote:

Hi,
I know the 1004 error common and a problem, but I can't seem to get
around
this one. I have used this code before and it worked great. I have
denoted
the function on the bottom for debug....
Thanks!!

Const Maxrow = 50000
Sub Headers_To_Macro_Test()
' Headers_To_Macro_Test Macro
' Macro recorded 9/9/2009 by

Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data_Headers")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B6:C6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C:D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5:F5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E:F")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H5:I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "K:L")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E6:F6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "G:H")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E7:F7")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "I:J")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H6:I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "M:N")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "O")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "D9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "P")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "G9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "Q")
rngCopy.Copy rngPaste

End Sub


Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
**Set SetPasteRangeByColumn =
Worksheets("All_Data_Headers").Cells(lngRow,
strColumn).End(xlUp).Offset(1, 0)--**Application defined or
object-defined
ERROR
End Function



el dee
Application Defined or object defined error..?Need help generi
 
Thanks, That was the golden ticket!

"Bob Phillips" wrote:

Make your paste range a single column, not two, even if copying two columns.

For instance


Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
rngCopy.Copy rngPaste

instead of


Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

--
__________________________________
HTH

Bob

"el dee" wrote in message
...
Hi Barb,
I changed the line, I had it that way to begin with actually. I am still
recieving the same error. Any other ideas?
Thanks again


"Barb Reinhardt" wrote:

When you call the function, you've already defined wks to be the
worksheet
named "All Data Headers" in ThisWorkbook.

Try changing the offending line to this

Set SetPasteRangeByColumn = wks.Cells(lngRow, _
strColumn).End(xlUp).Offset(1, 0)

HTH,
Barb Reinhardt


"el dee" wrote:

Hi,
I know the 1004 error common and a problem, but I can't seem to get
around
this one. I have used this code before and it worked great. I have
denoted
the function on the bottom for debug....
Thanks!!

Const Maxrow = 50000
Sub Headers_To_Macro_Test()
' Headers_To_Macro_Test Macro
' Macro recorded 9/9/2009 by

Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data_Headers")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B6:C6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C:D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5:F5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E:F")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H5:I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "K:L")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E6:F6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "G:H")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E7:F7")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "I:J")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H6:I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "M:N")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "O")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "D9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "P")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "G9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "Q")
rngCopy.Copy rngPaste

End Sub


Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
**Set SetPasteRangeByColumn =
Worksheets("All_Data_Headers").Cells(lngRow,
strColumn).End(xlUp).Offset(1, 0)--**Application defined or
object-defined
ERROR
End Function





All times are GMT +1. The time now is 01:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com