ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Application-defined or object -defined error (https://www.excelbanter.com/excel-programming/331252-application-defined-object-defined-error.html)

philwants2bgolf
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

Jim Thomlinson[_4_]
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


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

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