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