Search code examples
vbaexcelexcel-2013

Converting To String Removes 0's after Decimal


I am copying data from a source workbook to a destination workbook by using the Implode() method below. The issue that I have is that in the source workbook the format will be 7.00 but in the destination workbook the format will be 7, I believe this is due to CStr(MyR(1, i)) i.e. ConvertToString. How can I alter this method so that if the column is in a numeric format that once it is copied to the destination workbook, it is once again in a numeric format?

Private Function Implode(ByVal R As Range, Optional ByVal D As String = strSeparator) As String
    Dim i As Long, ii As Long, str As String, MyR() As Variant
    MyR = R
    For i = 1 To R.Columns.Count
        isPercent = False
        If iPC > 0 And IsNumeric(MyR(1, i)) And MyR(1, i) <> "" Then
            For ii = 1 To iPC
                If i = PercCols(ii) Then
                    isPercent = True
                    Exit For
                End If
            Next ii
        End If
        str = CStr(MyR(1, i))
        If InStr(1, str, D) > 0 Then str = """" & str & """"
        If i = 1 Then
            Implode = str
        Else
            Implode = Implode & D & str
        End If
    Next i
End Function

Solution

  • Can try

     If IsNumeric(MyR(1, i)) Then 'Check for numeric
          Round(CDec(MyR(1, i)),2)
     Else
          CStr(MyR(1, i))
     End If
    

    CDec allows those without decimal to be displayed as whole numbers

    More Info on Conversion Function