Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a list of cells
I have a column of numbers, many that start with 053Z. They don't
all have the exact same number of digits. What I'm trying to do is to go down the list, and change all those numbers starting with 053Z, remove the zero, and put a D at the begining, thus it would look like D53Z then the rest of the number. Example: If a number is 053Z123456, then the macro would make it D53123456. I tried using the recorder, but when I use the macro down the column, it makes every number exactly the same as the cell I used when I recorded it. I would appreciate any help. Thanks, J.O. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a list of cells
Sub changeData()
Range("A1").Activate Do If Left(Activecell,4) = "053Z" then Left(Activecell,4) = "D53Z" else Activecell.Offset(1,0).Activate Loop until Activecell = "" End Sub -- Best wishes, Jim "excelnut1954" wrote: I have a column of numbers, many that start with 053Z. They don't all have the exact same number of digits. What I'm trying to do is to go down the list, and change all those numbers starting with 053Z, remove the zero, and put a D at the begining, thus it would look like D53Z then the rest of the number. Example: If a number is 053Z123456, then the macro would make it D53123456. I tried using the recorder, but when I use the macro down the column, it makes every number exactly the same as the cell I used when I recorded it. I would appreciate any help. Thanks, J.O. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a list of cells
Another may to do it:
ActiveSheet.Range("A1:A1000").Select Selection.Replace What:="053Z", Replacement:="D53Z", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False -- Best wishes, Jim "excelnut1954" wrote: I have a column of numbers, many that start with 053Z. They don't all have the exact same number of digits. What I'm trying to do is to go down the list, and change all those numbers starting with 053Z, remove the zero, and put a D at the begining, thus it would look like D53Z then the rest of the number. Example: If a number is 053Z123456, then the macro would make it D53123456. I tried using the recorder, but when I use the macro down the column, it makes every number exactly the same as the cell I used when I recorded it. I would appreciate any help. Thanks, J.O. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a list of cells
From your post, it isn't clear if you want the Z stripped as well as
the leading 0 changed to D. I'll assume its the later and that your example was in error. If so, maybe the following can help: Sub ZerosToDs() Dim cl As Range Dim s As String For Each cl In Selection.Cells s = cl.Value If s Like "053Z*" Then cl.Value = "D" & Mid(s, 2) End If Next cl End Sub Select the column (or part thereof) you want to change then invoke this and it should work. Hth -John Coleman On Apr 2, 3:37 pm, "excelnut1954" wrote: I have a column of numbers, many that start with 053Z. They don't all have the exact same number of digits. What I'm trying to do is to go down the list, and change all those numbers starting with 053Z, remove the zero, and put a D at the begining, thus it would look like D53Z then the rest of the number. Example: If a number is 053Z123456, then the macro would make it D53123456. I tried using the recorder, but when I use the macro down the column, it makes every number exactly the same as the cell I used when I recorded it. I would appreciate any help. Thanks, J.O. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a list of cells
An afterthought. The sub I wrote satisfies the specifications, but is
possibly not robust. In particular, it won't change strings which begin 053z... (lower case z) and is also sensitive to any space before the zero. These concerns can be addressed by replacing the line s = cl.Value by s = Trim(UCase(cl.Value)) -John Coleman On Apr 2, 4:26 pm, "John Coleman" wrote: From your post, it isn't clear if you want the Z stripped as well as the leading 0 changed to D. I'll assume its the later and that your example was in error. If so, maybe the following can help: Sub ZerosToDs() Dim cl As Range Dim s As String For Each cl In Selection.Cells s = cl.Value If s Like "053Z*" Then cl.Value = "D" & Mid(s, 2) End If Next cl End Sub Select the column (or part thereof) you want to change then invoke this and it should work. Hth -John Coleman On Apr 2, 3:37 pm, "excelnut1954" wrote: I have a column of numbers, many that start with 053Z. They don't all have the exact same number of digits. What I'm trying to do is to go down the list, and change all those numbers starting with 053Z, remove the zero, and put a D at the begining, thus it would look like D53Z then the rest of the number. Example: If a number is 053Z123456, then the macro would make it D53123456. I tried using the recorder, but when I use the macro down the column, it makes every number exactly the same as the cell I used when I recorded it. I would appreciate any help. Thanks, J.O.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing a list of cells
On Apr 2, 8:01 pm, "John Coleman" wrote:
An afterthought. The sub I wrote satisfies the specifications, but is possibly not robust. In particular, it won't change strings which begin 053z... (lower case z) and is also sensitive to any space before the zero. These concerns can be addressed by replacing the line s = cl.Value by s = Trim(UCase(cl.Value)) -John Coleman On Apr 2, 4:26 pm, "John Coleman" wrote: From your post, it isn't clear if you want the Z stripped as well as the leading 0 changed to D. I'll assume its the later and that your example was in error. If so, maybe the following can help: Sub ZerosToDs() Dim cl As Range Dim s As String For Each cl In Selection.Cells s = cl.Value If s Like "053Z*" Then cl.Value = "D" & Mid(s, 2) End If Next cl End Sub Select the column (or part thereof) you want to change then invoke this and it should work. Hth -John Coleman On Apr 2, 3:37 pm, "excelnut1954" wrote: I have a column of numbers, many that start with 053Z. They don't all have the exact same number of digits. What I'm trying to do is to go down the list, and change all those numbers starting with 053Z, remove the zero, and put a D at the begining, thus it would look like D53Z then the rest of the number. Example: If a number is 053Z123456, then the macro would make it D53123456. I tried using the recorder, but when I use the macro down the column, it makes every number exactly the same as the cell I used when I recorded it. I would appreciate any help. Thanks, J.O.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thanks, John This helped alot. I appreciate your time. J.O. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
editing a data validation list | Excel Discussion (Misc queries) | |||
I cannot add rows to a Sharepoint list when editing in Excel | Setting up and Configuration of Excel | |||
Editing a list of names that have numbers- Can i take out the numb | Excel Worksheet Functions | |||
Editing a list of data | Excel Discussion (Misc queries) | |||
A general question about editing current list via UserForm | Excel Programming |