Macro that looks at cell contents

[teamwill]

I have a column filled with ID numbers. Some begin with E. example
E12353. Other ID numbers are 5 digits long. example 12345. Every day I
copy and paste these numbers from a program and into my excel
spreadsheet. Along with these numbers is important data that I also
copy and paste into the spreadsheet. On the other side of the company
they use the same ID's but add "00" to the end of all 5 digit ID
numbers. It is not important to either side that these numbers dont
match so they dont care. I have to deal with combining these ID's to
get good information multiple times a day and very quickly at that. I
have set up a Vlookup table and everything else that I need to come up
with a total immediately. Right now I manually go through all the ID
numbers and add two 0's at the end of the ID's. I know how to create
basic macros but I couldn't find anything on how to do this. I paste
these ID numbers in col A. Simply put. I need a macro to find all 5
digit numbers in Col A and add two zeros to the end of them.

Please help and change my days for the better.
Thanks, Will

 

[ShaunM]

Hi Will

Here is a simple vba macro that will do what you need, It assumes tha
your list of ID's does not contain any blank cells.

To use it you will need to go to the vba editor and create a modul
then paste the following:
'--------------------------------------
Sub AddZeros()
Dim count As Integer
Dim pos As String

count = 1
pos = "A" & count

While (Range(pos).Value <> "")
If (Len(Range(pos).Value) = 5) Then
Range(pos).Value = Range(pos).Value & "00"
End If
count = count + 1
pos = "A" & count
Wend


End Sub
'------------------------------------------

In your worksheet I would suggest creating a button and attaching th
macro that so it can be run whenever you add new sets of ID's

Good Luck

Shau

 

[teamwill]

It works great and I learned something new.
thanks
Will