Code skips formatting individual cells from text to numbers-CodePudding

I am getting sensor data, that is stored in an Excel Worksheet. By some reason, the manufacturer of said sensor exports the measurement results as a text instead of a number.

Of course this can be changed manually. But due to the size and frequency of new sheets, I have decided to program a little Add-In that executes the following Makro:

Sub ConvertTextToNumber(ByVal control As IRibbonControl)
    With Range("=D5:G10000")
        .NumberFormat = "General"
        .Value = .Value
    End With

    With Range("=K5:L10000")
        .NumberFormat = "General"
        .Value = .Value
    End With    
End Sub

In principle this works perfectly. Upon pressing a Button in the Ribbon bar, each number inside those ranges converts from text to number format. However, some cells are just immune to this. They cant get reformatted manually aswell. Ive tried numerous things, but ive reached the the end of my excel capabilities. I hope somebody can help with this problem.

Heres a picture showing an example of the problem. The green marked cells are still formatted as text.

CodePudding user response：

Looks like Excel does not convert strings to numbers when using .Value = .Value so you need to explicitly convert them using CDec() into decimals (if the value is numeric).

Option Explicit

Public Sub Example()
    
    With [A1:A2] 'Range("D5:G10000")
        Dim ValueArr() As Variant
        ValueArr = .Value2   ' read values into array for fast processing
        
        ' Loop through 2 dimensional array
        Dim iRow As Long
        For iRow = LBound(ValueArr, 1) To UBound(ValueArr, 1)
            Dim iCol As Long
            For iCol = LBound(ValueArr, 2) To UBound(ValueArr, 2)
            
                ' convert strings to decimals if value is numeric
                If IsNumeric(ValueArr(iRow, iCol)) Then
                    ValueArr(iRow, iCol) = CDec(ValueArr(iRow, iCol))
                End If
                
            Next iCol
        Next iRow
        
        ' set numberformat and write array back to cells
        .NumberFormat = "General"
        .Value2 = ValueArr
    End With

End Sub

CodePudding user response：

For all that have a similar problem: This was my final version of @Pᴇʜ 's answer that worked in the end. Thanks and credits!

Option Explicit

Public Sub ConvertTextToNumber(ByVal control As IRibbonControl)
    
    With [D5:G10000] 'Range("D5:G10000")
        Dim ValueArr() As Variant
        ValueArr = .Value2   ' read values into array for fast processing
        
        ' Loop through 2 dimensional array
        Dim iRow As Long
        For iRow = LBound(ValueArr, 1) To UBound(ValueArr, 1)
            Dim iCol As Long
            For iCol = LBound(ValueArr, 2) To UBound(ValueArr, 2)
            
                ValueArr(iRow, iCol) = Replace((ValueArr(iRow, iCol)), ",", Application.DecimalSeparator)
                ValueArr(iRow, iCol) = Replace((ValueArr(iRow, iCol)), ".", Application.ThousandsSeparator)
                
                ' convert strings to decimals if value is numeric
                If IsNumeric(ValueArr(iRow, iCol)) Then
                    ValueArr(iRow, iCol) = CDec(ValueArr(iRow, iCol))
                End If
                
            Next iCol
        Next iRow
        
        ' set numberformat and write array back to cells
        .NumberFormat = "General"
        .Value2 = ValueArr
    End With

    With [K5:L10000] 'Range("K5:L10000")
        Dim secondValueArr() As Variant
        secondValueArr = .Value2   ' read values into array for fast processing
        
        ' Loop through 2 dimensional array
        Dim secondiRow As Long
        For secondiRow = LBound(secondValueArr, 1) To UBound(secondValueArr, 1)
            Dim secondiCol As Long
            For secondiCol = LBound(secondValueArr, 2) To UBound(secondValueArr, 2)
            
                secondValueArr(secondiRow, secondiCol) = Replace((secondValueArr(secondiRow, secondiCol)), ",", Application.DecimalSeparator)
                secondValueArr(secondiRow, secondiCol) = Replace((secondValueArr(secondiRow, secondiCol)), ".", Application.ThousandsSeparator)
                
                ' convert strings to decimals if value is numeric
                If IsNumeric(secondValueArr(secondiRow, secondiCol)) Then
                    secondValueArr(secondiRow, secondiCol) = CDec(secondValueArr(secondiRow, secondiCol))
                End If
                
            Next secondiCol
        Next secondiRow
        
        ' set numberformat and write array back to cells
        .NumberFormat = "General"
        .Value2 = secondValueArr
    End With


End Sub

Note: Might have been unnessecary to do a second loop and rename every variable, but im new to VBA and this just worked!