Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Application-defined or object -defined error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Application-defined or object -defined error

I get the error messege below when I run my macro. I can't figure ou
what is wrong that causes this to happen. It breaks a
Columns("A:A").Select I have placed a comment field around it to hel
make it stand out. Any help would be great.

By the way, my two file names are 1.txt and 2.txt


Error Messege:

Run-Time error'1004':

Application-defined or object -defined error





Code
-------------------

Private Sub cmdImport_Click()
Dim schfile As Variant 'Scheduled input file with path
Dim procfile As Variant 'Case Procedure input file with path
Dim mainf As Variant 'Main program window name
Dim newf As Variant 'New file
Dim schwb As Variant 'Schedule Workbook file name
Dim prowb As Variant 'Case Procedure Workbook file name



mainf = ActiveWorkbook.Name

schfile = Application.GetOpenFilename(, , "Select Scheduled File")
If schfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

procfile = Application.GetOpenFilename(, , "Select Case Procedure File")
If procfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

Workbooks.Add
newf = ActiveWorkbook.Name

Workbooks.OpenText Filename:=schfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(20, 2), Array(31, 2), Array(72, 2))

schwb = ActiveWorkbook.Name
Workbooks.OpenText Filename:=procfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(19, 2), Array(26, 2), Array(68, 2), Array(79, 2))

procwb = ActiveWorkbook.Name


' copy and paste into one file
'
Windows(schwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("1").Select
Sheets("1").Move Befo=Workbooks(newf).Sheets(1)
Windows(procwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("2").Select
Sheets("2").Move Befo=Workbooks(newf).Sheets(2)
Sheets("Sheet1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: Sheets("X")
''''''''''''''''''''''''''''''''''''''''''''''
Sheets("1").Select
Sheets("1").Name = "Sheet1"
Sheets("2").Select
Sheets("2").Name = "Sheet2"

'insert the header line and the vlookup field
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
'''''''''This is were it errors out''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
Columns("A:A").Select
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Procedure"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1] & "" "" & RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Selection.AutoFill Destination:=Range("A2:A14")
Range("A2:A14").Select
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Scheduled Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Selection.AutoFill Destination:=Range("A2:A15")
Range("A2:A15").Select
End Sub

--------------------


--
philwants2bgolf
------------------------------------------------------------------------
philwants2bgolf's Profile: http://www.excelforum.com/member.php...o&userid=24117
View this thread: http://www.excelforum.com/showthread...hreadid=377429

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default Application-defined or object -defined error
Change Columns to Range

Range("A:A").Select
--
HTH...

Jim Thomlinson


"philwants2bgolf" wrote:


I get the error messege below when I run my macro. I can't figure out
what is wrong that causes this to happen. It breaks at
Columns("A:A").Select I have placed a comment field around it to help
make it stand out. Any help would be great.

By the way, my two file names are 1.txt and 2.txt


Error Messege:

Run-Time error'1004':

Application-defined or object -defined error





Code:
--------------------

Private Sub cmdImport_Click()
Dim schfile As Variant 'Scheduled input file with path
Dim procfile As Variant 'Case Procedure input file with path
Dim mainf As Variant 'Main program window name
Dim newf As Variant 'New file
Dim schwb As Variant 'Schedule Workbook file name
Dim prowb As Variant 'Case Procedure Workbook file name



mainf = ActiveWorkbook.Name

schfile = Application.GetOpenFilename(, , "Select Scheduled File")
If schfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

procfile = Application.GetOpenFilename(, , "Select Case Procedure File")
If procfile = False Then
MsgBox ("File Selection Canceled" & Chr(13) & "Program TERMINATED")
Exit Sub
End If

Workbooks.Add
newf = ActiveWorkbook.Name

Workbooks.OpenText Filename:=schfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(20, 2), Array(31, 2), Array(72, 2))

schwb = ActiveWorkbook.Name
Workbooks.OpenText Filename:=procfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(10 _
, 2), Array(19, 2), Array(26, 2), Array(68, 2), Array(79, 2))

procwb = ActiveWorkbook.Name


' copy and paste into one file
'
Windows(schwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("1").Select
Sheets("1").Move Befo=Workbooks(newf).Sheets(1)
Windows(procwb).Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Sheets("2").Select
Sheets("2").Move Befo=Workbooks(newf).Sheets(2)
Sheets("Sheet1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
''''''''''''''''''''''''''''''''''''''''''''''
'Needs Automated: Sheets("X")
''''''''''''''''''''''''''''''''''''''''''''''
Sheets("1").Select
Sheets("1").Name = "Sheet1"
Sheets("2").Select
Sheets("2").Name = "Sheet2"

'insert the header line and the vlookup field
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
'''''''''This is were it errors out''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
Columns("A:A").Select
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Procedure"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Start Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1] & "" "" & RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Selection.AutoFill Destination:=Range("A2:A14")
Range("A2:A14").Select
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fudge"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("D1").Select
ActiveCell.FormulaR1C1 = "MRN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Patient"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Scheduled Time"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[4]"
Range("A2").Select
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
'Needs Automated: X Number of Cells
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Selection.AutoFill Destination:=Range("A2:A15")
Range("A2:A15").Select
End Sub

--------------------


--
philwants2bgolf
------------------------------------------------------------------------
philwants2bgolf's Profile: http://www.excelforum.com/member.php...o&userid=24117
View this thread: http://www.excelforum.com/showthread...hreadid=377429


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
Run-time error '50290': Application-defined or object-defined erro Macro button Excel Discussion (Misc queries) 1 March 12th 09 10:59 AM
error: ActiveCell.Offset(0, -1).Select = Application-defined or object-defined error -[::::Shamran::::]- Excel Programming 7 June 7th 05 02:14 PM
Application-Defined or Object-Defined Error on simple code [email protected] Excel Programming 6 February 22nd 05 09:03 PM
Application-defined or object-defined error - missing the basics [email protected] Excel Programming 1 December 28th 04 10:23 PM
Macro Run-time Error 1004 Application Defined or Object Defined Error Anddmx Excel Programming 6 June 9th 04 03:40 PM


All times are GMT +1. The time now is 06:11 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement