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.
Bookmarks