Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default How do I create a macro to add zeros in front of a value?

PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default How do I create a macro to add zeros in front of a value?

an easy way is to custom format to the number of 0's desired IF they are the
same length.
000000000000

--
Don Guillett
SalesAid Software

"rexie3" wrote in message
...
PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know
how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default How do I create a macro to add zeros in front of a value?

Sub test()
Set thisrng = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Sep 2006 09:15:02 -0700, rexie3
wrote:

PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default How do I create a macro to add zeros in front of a value?

Hello Don,

Unfortunately, I can't use the custom format because I have to format as a
text. I will be using the vlookup later to compare the data with another
sheet.

I wanted a macro because not all have the same # of characters. Some I have
to add 3 zeros and others 2, etc. It has become a nightmare.

But thank you anyway for answering, it was my fault, in my panic I forgot to
be more specific.

"Don Guillett" wrote:

an easy way is to custom format to the number of 0's desired IF they are the
same length.
000000000000

--
Don Guillett
SalesAid Software

"rexie3" wrote in message
...
PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know
how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default How do I create a macro to add zeros in front of a value?

Thank you so much! Question: Would I be able to run this macro for a number
of rows within my column? For example, I would want to run it for 20 rows in
Column B. How do I do that?

I have to add 3 zeros in some cells and others 2 zeros, etc. How do I tell
the macro to repeat itself for a number of rows down a column?

"Gord Dibben" wrote:

Sub test()
Set thisrng = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Sep 2006 09:15:02 -0700, rexie3
wrote:

PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default How do I create a macro to add zeros in front of a value?

You need to clarify what condition causes 3 and what causes 2. If you are
trying to get all to line up, use my original suggestion. Gord's macro
figured the number of rows for you but if you want to specify.

Sub addzerosinfront()
Range("c1:c4").NumberFormat = "@"
For Each c In Range("c1:c4")
If Len(c) = 5 Then c.Value = "0" & c
If Len(c) = 4 Then c.Value = "00" & c
Next
End Sub

--
Don Guillett
SalesAid Software

"rexie3" wrote in message
...
Thank you so much! Question: Would I be able to run this macro for a
number
of rows within my column? For example, I would want to run it for 20 rows
in
Column B. How do I do that?

I have to add 3 zeros in some cells and others 2 zeros, etc. How do I
tell
the macro to repeat itself for a number of rows down a column?

"Gord Dibben" wrote:

Sub test()
Set thisrng = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Sep 2006 09:15:02 -0700, rexie3

wrote:

PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know
how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.





  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default How do I create a macro to add zeros in front of a value?

As written the macro operates on any column from the activecell to the bottom of
the column.

You do know that by adding the zeros you are changing the numbers to text?

For a specific range use this version.

Sub test22()
Set thisrng = Range("B1:B20")
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub

To select a range use this version.

Sub test33()
Set thisrng = Application.InputBox(prompt:= _
"Select the range of cells.", Type:=8)
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub

How will Excel know which cells are to receive 3 zeros or 2 zeros?


Gord




On Mon, 18 Sep 2006 14:38:02 -0700, rexie3
wrote:

Thank you so much! Question: Would I be able to run this macro for a number
of rows within my column? For example, I would want to run it for 20 rows in
Column B. How do I do that?

I have to add 3 zeros in some cells and others 2 zeros, etc. How do I tell
the macro to repeat itself for a number of rows down a column?

"Gord Dibben" wrote:

Sub test()
Set thisrng = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Sep 2006 09:15:02 -0700, rexie3
wrote:

PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.




Gord Dibben MS Excel MVP
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default How do I create a macro to add zeros in front of a value?

Thank you Don for being so patient with me....

(You too Gord!)

I'm soooo close I can taste it. I'm trying to end up with 10 characters for
each cell.
For now I'm using your macro with the IF THEN statements and it's working
great.

But for later on, I sure would like to know how to combine your macro with
Gord's macro which asks the user for a range. I tried to type in the
underscore and Excel doesn't like it. What does "Type:=8" mean? I'm really
trying to understand how to write macros because this new job of mine has
MACROS! written all over it!


Gord's macro states:
Sub test33()
Set thisrng = Application.InputBox(prompt:= _
"Select the range of cells.", Type:=8)
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


So far I have this macro written out and it doesn't want to work. Can a
macro like this even be done?

Sub Addzeros()

Set thisrng = Application.InputBox(prompt:="Select the range of cells.",
Type:=8)
For Each cell In thisrng
If Len(cell.Value ) = 3 Then cell.Value = "0000000" & cell.Value
If Len(cell.Value ) = 4 Then cell.Value = "000000" & cell.Value
If Len(cell.Value ) = 5 Then cell.Value = "00000" & cell.Value
If Len(cell.Value ) = 6 Then cell.Value = "0000" & cell.Value
If Len(cell.Value ) = 7 Then cell.Value = "000" & cell.Value
If Len(cell.Value ) = 8 Then cell.Value = "00" & cell.Value
If Len(cell.Value ) = 9 Then cell.Value = "0" & cell.Value
Next
End Sub

"Don Guillett" wrote:

You need to clarify what condition causes 3 and what causes 2. If you are
trying to get all to line up, use my original suggestion. Gord's macro
figured the number of rows for you but if you want to specify.

Sub addzerosinfront()
Range("c1:c4").NumberFormat = "@"
For Each c In Range("c1:c4")
If Len(c) = 5 Then c.Value = "0" & c
If Len(c) = 4 Then c.Value = "00" & c
Next
End Sub

--
Don Guillett
SalesAid Software

"rexie3" wrote in message
...
Thank you so much! Question: Would I be able to run this macro for a
number
of rows within my column? For example, I would want to run it for 20 rows
in
Column B. How do I do that?

I have to add 3 zeros in some cells and others 2 zeros, etc. How do I
tell
the macro to repeat itself for a number of rows down a column?

"Gord Dibben" wrote:

Sub test()
Set thisrng = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Sep 2006 09:15:02 -0700, rexie3

wrote:

PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know
how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default How do I create a macro to add zeros in front of a value?

The underscore is called a "line-continuation" character.

Used to break long lines into multiples.

Note there is a <space before the underscore.

Set thisrng = Application.InputBox(prompt:= _
"Select the range of cells.", Type:=8)


From Help on Application.InputBox

Type Optional Variant. Specifies the return data type. If this argument is
omitted, the dialog box returns text. Can be one or a sum of the following
values.

Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values

You can use the sum of the allowable values for Type. For example, for an input
box that can accept both text and numbers, set Type to 1 + 2.

Try this amended macro which re-formats the numbers to text before looking at
the Len

Sub Addzeros()
Set thisrng = Application.InputBox(prompt:= _
"Select the range of cells.", Type:=8)

thisrng.NumberFormat = "@"

For Each cell In thisrng
If Len(cell.Value) = 3 Then cell.Value = "0000000" & cell.Value
If Len(cell.Value) = 4 Then cell.Value = "000000" & cell.Value
If Len(cell.Value) = 5 Then cell.Value = "00000" & cell.Value
If Len(cell.Value) = 6 Then cell.Value = "0000" & cell.Value
If Len(cell.Value) = 7 Then cell.Value = "000" & cell.Value
If Len(cell.Value) = 8 Then cell.Value = "00" & cell.Value
If Len(cell.Value) = 9 Then cell.Value = "0" & cell.Value
Next
End Sub


Gord

On Tue, 19 Sep 2006 14:00:02 -0700, rexie3
wrote:

Thank you Don for being so patient with me....

(You too Gord!)

I'm soooo close I can taste it. I'm trying to end up with 10 characters for
each cell.
For now I'm using your macro with the IF THEN statements and it's working
great.

But for later on, I sure would like to know how to combine your macro with
Gord's macro which asks the user for a range. I tried to type in the
underscore and Excel doesn't like it. What does "Type:=8" mean? I'm really
trying to understand how to write macros because this new job of mine has
MACROS! written all over it!


Gord's macro states:
Sub test33()
Set thisrng = Application.InputBox(prompt:= _
"Select the range of cells.", Type:=8)
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


So far I have this macro written out and it doesn't want to work. Can a
macro like this even be done?

Sub Addzeros()

Set thisrng = Application.InputBox(prompt:="Select the range of cells.",
Type:=8)
For Each cell In thisrng
If Len(cell.Value ) = 3 Then cell.Value = "0000000" & cell.Value
If Len(cell.Value ) = 4 Then cell.Value = "000000" & cell.Value
If Len(cell.Value ) = 5 Then cell.Value = "00000" & cell.Value
If Len(cell.Value ) = 6 Then cell.Value = "0000" & cell.Value
If Len(cell.Value ) = 7 Then cell.Value = "000" & cell.Value
If Len(cell.Value ) = 8 Then cell.Value = "00" & cell.Value
If Len(cell.Value ) = 9 Then cell.Value = "0" & cell.Value
Next
End Sub

"Don Guillett" wrote:

You need to clarify what condition causes 3 and what causes 2. If you are
trying to get all to line up, use my original suggestion. Gord's macro
figured the number of rows for you but if you want to specify.

Sub addzerosinfront()
Range("c1:c4").NumberFormat = "@"
For Each c In Range("c1:c4")
If Len(c) = 5 Then c.Value = "0" & c
If Len(c) = 4 Then c.Value = "00" & c
Next
End Sub

--
Don Guillett
SalesAid Software

"rexie3" wrote in message
...
Thank you so much! Question: Would I be able to run this macro for a
number
of rows within my column? For example, I would want to run it for 20 rows
in
Column B. How do I do that?

I have to add 3 zeros in some cells and others 2 zeros, etc. How do I
tell
the macro to repeat itself for a number of rows down a column?

"Gord Dibben" wrote:

Sub test()
Set thisrng = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))
For Each cell In thisrng
cell.Value = "00" & cell.Value
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 18 Sep 2006 09:15:02 -0700, rexie3

wrote:

PLEASE SOMEONE HELP ME!!!

I have to add zeros to over 61,000 line items in excel. I need to know
how
to create a macro that will enable me to select a range within the same
column and have zeros added to the the numbers in the cell.

I would be eternally greatful.
Thanking you in advance.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I email amacro? leo Excel Worksheet Functions 24 August 9th 06 02:47 PM
Setting up Macro to create pie chart. Hamish Charts and Charting in Excel 2 August 8th 06 02:30 PM
Trying to Create a Macro [email protected] Excel Worksheet Functions 1 July 27th 06 10:03 PM
How do I create a macro that will compare columns and place data CompuCat Excel Worksheet Functions 0 March 20th 06 07:21 PM
Vb macro stops when I try to create more than 89 charts Tiberius Charts and Charting in Excel 0 January 19th 06 06:52 PM


All times are GMT +1. The time now is 12:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"