Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Application Defined or object defined error..?Need help generic er
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; [email protected] Excel Programming 4 September 16th 07 11:09 PM
Application-Defined or object defined error 1004 When ran on exel97 but not 2003 bornweb Excel Programming 0 February 17th 07 11:30 PM
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined Matt[_39_] Excel Programming 3 July 25th 06 01:13 AM
Runtime error 1004- application defined or object defined erro Novice Excel Programming 1 February 6th 06 09:33 PM
Runtime error 1004- application defined or object defined erro Jim Thomlinson[_5_] Excel Programming 0 February 6th 06 09:29 PM


All times are GMT +1. The time now is 01:56 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement