Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a macro assigned to a button that has worked fine for a couple of years. The code allows me to open one or more files that I downloaded from a server that are in mainframe report layout, strip out junk and leave me usable data. This code below immediately sticks on the "If VarType(FName) = vbBoolean Then" line. If I click through that, I then get stuck on the "ActiveCell.SpecialCells(xlLastCell).Select" line.

    Can anyone suggest what might be the problem, especially with the first sticking point? Again, its one of those weird things (to me) where you are running code like you have for a long time, and now, things stop working.....Again, if I click Continue when I hit a debug point, I get the eventual results I want, but the error is befuddling.

    Oddly (perhaps not to more experienced Excel-VBA users) when I step through the code, it works with no errors at all ???


    [codebox]Public Sub DoTheMultiImport()
    Dim FName As Variant
    Dim Sep As String
    Dim i As Integer

    FName = Application.GetOpenFilename("All files (*.*), *.*", , _
    "Select the Check Register Report you want to open.", , True)
    If VarType(FName) = vbBoolean Then
    MsgBox "You didn't select a file"
    Exit Sub
    End If

    For i = LBound(FName) To UBound(FName)
    Workbooks.OpenText Filename:=FName(i), _
    Origin:=xlWindows, StartRow:=8, DataType:=xlFixedWidth, FieldInfo:= _
    Array(Array(0, 1), Array(11, 1), Array(22, 1), Array(53, 1), Array(65, 1), Array(74, 1), _
    Array(79, 1))
    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, Cells(1)).Select
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Find(What:="-------", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    ActiveCell.Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    Range("A1").Select
    'Trying to reset last cell
    'ActiveWorksheet.UsedRange.Rows.Count
    Call Reset_all_lastcells
    Range("A1").Select
    Call FormatColumns
    Next i
    End Sub
    [/codebox]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your macro runs without problems when I try it.

    Open the workbook.
    Activate the Visual Basic Editor.
    Select Tools | References...
    Do you see any reference among those at the top whose check box is ticked that starts with MISSING?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I would try running Code Cleaner on the workbook, though, as it happens, I recently had a period of about 2 weeks where a lot of my code (and 3rd party add-ins) would hit imaginary breakpoints and I had to keep hitting f5. Then it disappeared just as suddenly, so I suspect a Windows/Office update got pushed out to cause it, and then another one fixed it. I've heard several other reports of this, but no identified cause yet.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans--I didn't see any missing references, but I think some references in my Personal workbook were missing, causing some other macros to behave weirdly.

    And I did run Code Cleaner. For whatever reason, the code now executes correctly. Also, another "explode" add-in that was giving me similar issues starting working again.

    Thanks for the help guys! I was really at a complete loss and didn't know what to do or try. I know these kinds of "things" can happen, but glad it is working again.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •