Help with Macro

  • Thread starter Thread starter Chad Holstead
  • Start date Start date
C

Chad Holstead

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
 
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.
 
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
 
Tom,

You solution catches the spaces at the beginning of the string
" 131212A"
and results in
" - 131212A"

(see my use of Trim)
 
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 said:
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
Click to expand...


.
Click to expand...
 
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
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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
 
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 said:
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
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
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


.
Click to expand...
Click to expand...
Click to expand...
 
Tom,

Thanks!

But as always - your solutions are always so much more elegant! And
simpler...

--
sb
Tom Ogilvy said:
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 said:
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
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
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


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top