+ Reply to Thread
Results 1 to 2 of 2

VBA stops running after .NumberFormat method but no error is raised

Hybrid View

  1. #1
    Registered User
    Join Date
    03-06-2006
    Location
    South Central Pennsylvania
    MS-Off Ver
    MS Office Pro Plus 2016, on Window 10
    Posts
    27

    VBA stops running after .NumberFormat method but no error is raised

    I tried running the following routines to figure out this problem. I think the comments will explain what is happening. I am using Excel that is part of Microsoft Office Professional Plus 2016 on Windows 10.

    Sub ChangeToNumber()
        'This was set to run from a custom toolbar button.
        Dim myRange As Range
        Dim I As Integer
        
        If Selection Is Nothing Then Exit Sub
        Set myRange = Selection.SpecialCells(xlCellTypeConstants) 'Cells that are not formulas
        ChangeRangeToNumberFormat myRange 'It does change the entire range but the VBA stops running.
        
        I = 0 'Just for setting a debug breakpoint instead of the "End Sub" statement.  Usually, this can be edited while in debug mode.
        
        'Then, it was planned to run another sub (ChangeCellsToNumber) without the .NumberFormat method and loop through each cell.
    End Sub
    
    Sub ChangeRangeToNumberFormat(myRange As Range)
        
        myRange.NumberFormat = "0" 'Can't loop through the cells and do more because the routine exits after this with no errors!!
        'So, to do more after the above, use another routine to call this first then call another routine a loop through the cells
        'to do the rest. (Does not work!)
    End Sub
    
    Sub ChangeCellsToNumber(myRange As Range)
        'After changing the range to .NumberFormat = "0", Excel does not reformat until you change the cells' values
        'by selecting the cell and retyping the value.  This routine simulates that and it works.
        Dim cell As Range
        
        For Each cell In myRange
            If IsNumeric(cell) And InStr(cell.Formula, "=") = 0 Then
                cell = cell 'Simulates retyping the value (cell.value = cell.value works too)
            Else
                'MsgBox "The value in row " & Cell.Row & " is not a number" 'Debugging stuff.
            End If
        Next cell
    End Sub
    Some help would be much appreciated. This is weird.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,317

    Re: VBA stops running after .NumberFormat method but no error is raised

    Hmmm.... works as it should on my system.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel 2016 stops running(crashes) after running VBA that was working fine on previous vers
    By lauriejerome in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2017, 01:51 PM
  2. Macro stops running randomly with no break or error when looping
    By blh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2015, 10:37 AM
  3. Replies: 10
    Last Post: 01-28-2014, 02:05 PM
  4. [SOLVED] Code execution stops after .insert method
    By deucejmp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2013, 06:43 PM
  5. [SOLVED] Method 'Range' error at running macro on Worksheet
    By DiCaver in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-25-2013, 08:00 AM
  6. [SOLVED] Method 'Range' of object '_Global' failed error message on running userform when another
    By sivakumar123 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-01-2012, 11:39 AM
  7. application or object defined error raised by some simple construct
    By aretai in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-21-2010, 02:27 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1