Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change values in a column
The values in column J are displayed as 1, 2, 3, 4, ...., 9, 10, 11, ....
I need all the values that are less than 10 added with leading 0, which are put into column L. (actually i want the values in column J change themselves, but i want to see the difference before and after change, so i put the new values in column L) But the following codes failed to do so.... Pls help... Thanks a lot!! ---------------------------------------------------------------------- Sub test() Dim i As Long Dim st As String For i = 2 To 150 Cells(i, 13) = TypeName(Cells(i, 10).Value) If Cells(i, 10) < 10 Then Cells(i, 11) = "yes" st = "0" & Trim(Str(Cells(i, 10).Value)) Cells(i, 12).Value = st End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change values in a column
You could just format the cells to display numbers using a Custom Number
Format of "00". In VBA code, this would look like the following: Sub ShowLeadingZeroFormat() Selection.NumberFormat = "00" End Sub -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change values in a column
Like Bill said, you can simply change the number format. But that
will only be a visible change, not a real change. The value in the cell will remain a single character. If you need tp physically place a zero in the front, you'll need to change the number format of the column to text. Something like this would work. Sub test() Dim i As Long Dim st As String Cells(1, 12).EntireColumn.NumberFormat = "@" For i = 2 To 150 Cells(i, 13) = TypeName(Cells(i, 10).Value) If Cells(i, 10) < 10 Then Cells(i, 11) = "yes" st = Trim("0" & Cells(i, 10).Text) Cells(i, 12).Value = st End If Next End Sub guy wrote: The values in column J are displayed as 1, 2, 3, 4, ...., 9, 10, 11, .... I need all the values that are less than 10 added with leading 0, which are put into column L. (actually i want the values in column J change themselves, but i want to see the difference before and after change, so i put the new values in column L) But the following codes failed to do so.... Pls help... Thanks a lot!! ---------------------------------------------------------------------- Sub test() Dim i As Long Dim st As String For i = 2 To 150 Cells(i, 13) = TypeName(Cells(i, 10).Value) If Cells(i, 10) < 10 Then Cells(i, 11) = "yes" st = "0" & Trim(Str(Cells(i, 10).Value)) Cells(i, 12).Value = st End If Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
change values in a column
If you want to simply apply the changes/formatting to the existing
records, you could use this: Sub test() Dim i As Long Cells(1, 10).EntireColumn.NumberFormat = "@" For i = 2 To 150 Cells(i, 10).Value = _ Format(Cells(i, 10).Value, "00") Next End Sub guy wrote: The values in column J are displayed as 1, 2, 3, 4, ...., 9, 10, 11, .... I need all the values that are less than 10 added with leading 0, which are put into column L. (actually i want the values in column J change themselves, but i want to see the difference before and after change, so i put the new values in column L) But the following codes failed to do so.... Pls help... Thanks a lot!! ---------------------------------------------------------------------- Sub test() Dim i As Long Dim st As String For i = 2 To 150 Cells(i, 13) = TypeName(Cells(i, 10).Value) If Cells(i, 10) < 10 Then Cells(i, 11) = "yes" st = "0" & Trim(Str(Cells(i, 10).Value)) Cells(i, 12).Value = st End If Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
change values in a column
thank you so much!
"JW" groups.com... Like Bill said, you can simply change the number format. But that will only be a visible change, not a real change. The value in the cell will remain a single character. If you need tp physically place a zero in the front, you'll need to change the number format of the column to text. Something like this would work. Sub test() Dim i As Long Dim st As String Cells(1, 12).EntireColumn.NumberFormat = "@" For i = 2 To 150 Cells(i, 13) = TypeName(Cells(i, 10).Value) If Cells(i, 10) < 10 Then Cells(i, 11) = "yes" st = Trim("0" & Cells(i, 10).Text) Cells(i, 12).Value = st End If Next End Sub guy wrote: The values in column J are displayed as 1, 2, 3, 4, ...., 9, 10, 11, .... I need all the values that are less than 10 added with leading 0, which are put into column L. (actually i want the values in column J change themselves, but i want to see the difference before and after change, so i put the new values in column L) But the following codes failed to do so.... Pls help... Thanks a lot!! ---------------------------------------------------------------------- Sub test() Dim i As Long Dim st As String For i = 2 To 150 Cells(i, 13) = TypeName(Cells(i, 10).Value) If Cells(i, 10) < 10 Then Cells(i, 11) = "yes" st = "0" & Trim(Str(Cells(i, 10).Value)) Cells(i, 12).Value = st End If Next End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
change values in a column
thanks!!
"Bill Renaud" ... You could just format the cells to display numbers using a Custom Number Format of "00". In VBA code, this would look like the following: Sub ShowLeadingZeroFormat() Selection.NumberFormat = "00" End Sub -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change values in a column | Excel Discussion (Misc queries) | |||
How can I change all negative values in a column to = 0? | Excel Worksheet Functions | |||
How to find the largest change of values in a column | Excel Worksheet Functions | |||
Change all values in an excel column at once? | Excel Discussion (Misc queries) | |||
Better way to apply change to column of values | Excel Programming |