offset

  • Thread starter Thread starter T.K Kullervo
  • Start date Start date
T

T.K Kullervo

Hi, im using the following code to move all the text from all the columns on
a row to the first column. It works great except that i have also numbers
that include decimals like 33.56. When it is copied to the first column on a
row excel rounds it to 34. How can i stop this rounding from happening?
The data in a cell can be text or numbers like
c | 33.56 | %
and it should turn out like
c 33.56 %
but it turns out like
c 34 %

Sub MakeOneCol()
Dim ThisRow As Range
Dim NewVal As String
Dim i, j As Integer
Set ThisRow = Sheets("Summary").Cells(1, 1)
For i = 0 To 54
NewVal = ""
For j = 0 To 5
NewVal = NewVal & Format(ThisRow.Offset(i, j), 0) & " "
Next j
ThisRow.Offset(i, 0) = NewVal
Next i
Sheets("Summary").Range("B2", "F55").Delete (1)
Sheets("Summary").PageSetup.PrintArea = "$A$1:$F$55"
End Sub

Youll be a hero if you help me.
 
change
NewVal & Format(ThisRow.Offset(i, j), 0) & " "


to

NewVal & Format(ThisRow.Offset(i, j), 0.00) & " "

would be my guess. or don't use format

NewVal & ThisRow.Offset(i, j).Value & " "

or
NewVal & ThisRow.Offset(i, j).text & " "

if you want to duplicate what is displayed in the cell.
 
Back
Top