Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
I am not very good at VB and need some help with a
Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Sub HypenateB()
Dim rng as Range, cell as Range On Error Resume Next set rng = Columns(2).SpecialCells(xlConstants,xlTextValues) On Error goto 0 if not rng is nothing then for each cell in rng cell.Value = Left(cell,2) & "-" & right(cell,len(cell)-2) Next End if End Sub Test it on a copy of your data. -- Regards, Tom Ogilvy "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Chad,
This seemed to work in Excel97 [watch word wrap] Dim i As Long i = 1 Do Until Cells(i, 2) = "" Cells(i, 2) = Left(Trim(Cells(i, 2)), 2) & "-" & _ Right(Trim(Cells(i, 2)), Len(Trim(Cells(i, 2))) - 2) i = i + 1 Loop -- sb "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Tom,
You solution catches the spaces at the beginning of the string " 131212A" and results in " - 131212A" (see my use of Trim) -- sb "Tom Ogilvy" wrote in message ... Sub HypenateB() Dim rng as Range, cell as Range On Error Resume Next set rng = Columns(2).SpecialCells(xlConstants,xlTextValues) On Error goto 0 if not rng is nothing then for each cell in rng cell.Value = Left(cell,2) & "-" & right(cell,len(cell)-2) Next End if End Sub Test it on a copy of your data. -- Regards, Tom Ogilvy "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Steve,
Thanks this works great and saved me a huge headache. Thanks Chad -----Original Message----- Chad, This seemed to work in Excel97 [watch word wrap] Dim i As Long i = 1 Do Until Cells(i, 2) = "" Cells(i, 2) = Left(Trim(Cells(i, 2)), 2) & "-" & _ Right(Trim(Cells(i, 2)), Len(Trim(Cells(i, 2))) - 2) i = i + 1 Loop -- sb "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Chad,
Glad to be of assisstance! Keep on Exceling! Note that a faster way would be to have Excel write the formula into a dummy column, copy the results and paste.special xlvalues, than clear the dummy column. The only trick is to determine the number of rows. But it would look something like: Range("Z1:Z500").FormulaR1C1="=Left(Trim(RC2, 2) & "-" & _ Right(Trim(RC2), Len(Trim(RC2)) - 2) -- sb wrote in message ... Steve, Thanks this works great and saved me a huge headache. Thanks Chad -----Original Message----- Chad, This seemed to work in Excel97 [watch word wrap] Dim i As Long i = 1 Do Until Cells(i, 2) = "" Cells(i, 2) = Left(Trim(Cells(i, 2)), 2) & "-" & _ Right(Trim(Cells(i, 2)), Len(Trim(Cells(i, 2))) - 2) i = i + 1 Loop -- sb "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Thanks for point that out
Sub HypenateB() Dim rng as Range, cell as Range On Error Resume Next set rng = Columns(2).SpecialCells(xlConstants,xlTextValues) On Error goto 0 if not rng is nothing then for each cell in rng cell.Value = Left(trim(cell),2) & "-" & _ right(Trim(cell),len(trim(cell))-2) Next End if End Sub or Sub HypenateB() Dim rng as Range, cell as Range On Error Resume Next set rng = Columns(2).SpecialCells(xlConstants,xlTextValues) On Error goto 0 if not rng is nothing then for each cell in rng sCell = Trim(cell) cell.Value = Left(scell,2) & "-" & _ right(scell,len(scell)-2) Next End if End Sub -- Regards, Tom Ogilvy steve wrote in message ... Tom, You solution catches the spaces at the beginning of the string " 131212A" and results in " - 131212A" (see my use of Trim) -- sb "Tom Ogilvy" wrote in message ... Sub HypenateB() Dim rng as Range, cell as Range On Error Resume Next set rng = Columns(2).SpecialCells(xlConstants,xlTextValues) On Error goto 0 if not rng is nothing then for each cell in rng cell.Value = Left(cell,2) & "-" & right(cell,len(cell)-2) Next End if End Sub Test it on a copy of your data. -- Regards, Tom Ogilvy "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Steve -
Think you meant to use quotes about the various portions including putting the & within the formula and with quotes around the hypen. Something like this: Range("Z1:Z500").Formula = "=LEFT(TRIM(B1),2)" & _ "&""-""&" & "RIGHT(TRIM(B1),LEN(TRIM(B1))-2)" Testing in the immediate window to be sure ? "=LEFT(TRIM(B1),2)" & _ "&""-""&" & "RIGHT(TRIM(B1),LEN(TRIM(B1))-2)" =LEFT(TRIM(B1),2)&"-"&RIGHT(TRIM(B1),LEN(TRIM(B1))-2) Since most people use A1 notation, I have included that as a variation. -- Regards, Tom Ogilvy steve wrote in message ... Chad, Glad to be of assisstance! Keep on Exceling! Note that a faster way would be to have Excel write the formula into a dummy column, copy the results and paste.special xlvalues, than clear the dummy column. The only trick is to determine the number of rows. But it would look something like: Range("Z1:Z500").FormulaR1C1="=Left(Trim(RC2, 2) & "-" & _ Right(Trim(RC2), Len(Trim(RC2)) - 2) -- sb wrote in message ... Steve, Thanks this works great and saved me a huge headache. Thanks Chad -----Original Message----- Chad, This seemed to work in Excel97 [watch word wrap] Dim i As Long i = 1 Do Until Cells(i, 2) = "" Cells(i, 2) = Left(Trim(Cells(i, 2)), 2) & "-" & _ Right(Trim(Cells(i, 2)), Len(Trim(Cells(i, 2))) - 2) i = i + 1 Loop -- sb "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro
Tom,
Thanks! But as always - your solutions are always so much more elegant! And simpler... -- sb "Tom Ogilvy" wrote in message ... Steve - Think you meant to use quotes about the various portions including putting the & within the formula and with quotes around the hypen. Something like this: Range("Z1:Z500").Formula = "=LEFT(TRIM(B1),2)" & _ "&""-""&" & "RIGHT(TRIM(B1),LEN(TRIM(B1))-2)" Testing in the immediate window to be sure ? "=LEFT(TRIM(B1),2)" & _ "&""-""&" & "RIGHT(TRIM(B1),LEN(TRIM(B1))-2)" =LEFT(TRIM(B1),2)&"-"&RIGHT(TRIM(B1),LEN(TRIM(B1))-2) Since most people use A1 notation, I have included that as a variation. -- Regards, Tom Ogilvy steve wrote in message ... Chad, Glad to be of assisstance! Keep on Exceling! Note that a faster way would be to have Excel write the formula into a dummy column, copy the results and paste.special xlvalues, than clear the dummy column. The only trick is to determine the number of rows. But it would look something like: Range("Z1:Z500").FormulaR1C1="=Left(Trim(RC2, 2) & "-" & _ Right(Trim(RC2), Len(Trim(RC2)) - 2) -- sb wrote in message ... Steve, Thanks this works great and saved me a huge headache. Thanks Chad -----Original Message----- Chad, This seemed to work in Excel97 [watch word wrap] Dim i As Long i = 1 Do Until Cells(i, 2) = "" Cells(i, 2) = Left(Trim(Cells(i, 2)), 2) & "-" & _ Right(Trim(Cells(i, 2)), Len(Trim(Cells(i, 2))) - 2) i = i + 1 Loop -- sb "Chad Holstead" wrote in message ... I am not very good at VB and need some help with a Macro. I have spread sheet that is exported from another program. We need to reformat the data in Column B. The data comes into excell like this " 131212A" It needs to look like this "13-1212A". I have tried the standard recored macro, however it just replaces the data with the hard coded value. I need this to be able to handle diffrent values in column B. Any ideas would really help, I think this easy, I'm just not a vb guy. Thans Chad . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions |