CODEWITHSUNDEEP
vbaexcelexcel-2013
BellHopByDayAmetuerCoderByNigh
BellHopByDayAmetuerCoderByNigh

Reputation: 1167

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

Upvotes: 1

Views: 57

Answers (1)

Keenlearner
Keenlearner

Reputation: 704

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

Upvotes: 2

Related Questions