Code Runs in Macro but Not in Command Button

[Larry R Harrison Jr]

I have Excel 2003 and code in a macro that runs perfectly when you run it in
the macro. But the MINUTE you copy & paste that EXACT SAME code in a command
button, I get a "subscript out of range" error.

The code opens a TXT file, does numerous conversions to it (filters, sorts,
renaming of sheets, saving as an Excel file). Works PERFECTLY if you run the
macro. But again, copy & paste the code in a command button to make it
"friendly," and you get a "subscript out of range" error.

Yes, I was smart enough to not repaste the Sub/End sub headers but just the
code between it.

The code (with a *** comment showing the point where it crashes):

====================================================================
Private Sub cmdImportFile_Click()
' Macro1 Macro
' Macro recorded 3/10/2004 by Larry R Harrison Jr
'
Dim stFilePath As String, stFileOnly As String, stX As String, stXX As
String
Dim stL As Integer
stFilePath = Application.GetOpenFilename

Workbooks.OpenText Filename:=stFilePath _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1),
Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1),
Array(20, 1), Array(21, 1), _
Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26,
1)), _
TrailingMinusNumbers:=True
stFileOnly = ActiveWorkbook.Name

stL = Len(stFileOnly) - 4
stXX = Left(stFileOnly, stL)



' Fill in Headings that will be used
' *******The following line is the one that makes it crash********
Range("D1").Select
ActiveCell.FormulaR1C1 = "PropValue"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Seller_Lender"
Range("F1").Select
ActiveCell.FormulaR1C1 = "SellerAddr"
Range("G1").Select
ActiveCell.FormulaR1C1 = "SellerCityState"
Range("H1").Select
ActiveCell.FormulaR1C1 = "SellerZip"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Tenant"
Range("M1").Select
ActiveCell.FormulaR1C1 = "TenAddr"
Range("N1").Select
ActiveCell.FormulaR1C1 = "TenCityState"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "TenZIP"


Range("A1").Select
ActiveCell.Value = "ColA"
Range("B1").Select
ActiveCell.Value = "ColB"
Range("C1").Select
ActiveCell.Value = "ColC"
Range("I1").Select
ActiveCell.Value = "ColI"
Range("J1").Select
ActiveCell.Value = "ColJ"
Range("K1").Select
ActiveCell.Value = "ColK"
Range("P1").Select
ActiveCell.Value = "ColP"
Range("O1").Select
ActiveCell.Value = "ColO"
Range("R1").Select
ActiveCell.Value = "ColR"
Range("S1").Select
ActiveCell.Value = "ColS"
Range("T1").Select
ActiveCell.Value = "ColT"
Range("U1").Select
ActiveCell.Value = "ColU"
Range("V1").Select
ActiveCell.Value = "ColV"
Range("W1").Select
ActiveCell.Value = "ColW"
Range("X1").Select
ActiveCell.Value = "ColX"
Range("Y1").Select
ActiveCell.Value = "ColY"
Range("Z1").Select
ActiveCell.Value = "ColZ"

stX = ActiveWorkbook.Path & "\" & "affadavids_" & Format(Now(), "yyyymmdd")
& "_txt_excel_format.txt"
' MsgBox "Data successfully imported, preparing to save.", vbOKOnly,
"File Converted"

'MsgBox "stX = " & stX


' Filter property values, sort by zip code
Cells.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=">=100000", Operator:=xlAnd
Selection.Sort Key1:=Range("Q2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Cells.Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
True, Transpose:=False


' Redo sheet
Sheets(stXX).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Sheet1").Select
Selection.AutoFilter
Sheets("Sheet1").Name = "AllData"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "PublishFormat"

' Delete unnecessary columns in "PublishFormat"
Columns("A:C").Select
Selection.Delete Shift:=xlToLeft
Columns("B:H").Select
Selection.Delete Shift:=xlToLeft
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("F:N").Select
Selection.Delete Shift:=xlToLeft

' Save new file as an Excel spreadsheet

stX = ActiveWorkbook.Path & "\" & "affadavids_" & Format(Now(), "yyyymmdd")
& ".xls"

ActiveWorkbook.SaveAs Filename:= _
stX _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = False
MsgBox "File saved successfully", vbOKOnly, "Operation successful"
Application.Quit

End Sub

====================================================================

The code prior to that (opening the TXT file) does successfully run, it
doesn't crash immediately upon clicking the button.

Again--put this in a macro, runs perfectly.

Tips?

LRH

 

[BrianB]

I can see that you might get a problem with the incorrect code :-

'-------------------------------------------------
ActiveCell.FormulaR1C1 = "PropValue"
'--------------------------------------------------

which should be :-

'----------------------------------------------------------------
ActiveCell.Value= "PropValue"

or better - not requiring 'Select' :-
ActiveSheet.Range("D1").Value = "PropValue"
'-----------------------------------------------------------------