Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset
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. -- Regards, Tom Ogilvy "T.K Kullervo" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do I really need OFFSET? | Charts and Charting in Excel | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset Help | Excel Worksheet Functions | |||
Offset. | Excel Worksheet Functions | |||
OFFSET PLEASE HELP! | Excel Discussion (Misc queries) |