Re: Format "0000"

On Jun 29, 9:47 am, Matt <mwagn...@xxxxxxxxx> wrote:
On Jun 29, 9:43 am, Limey <dav...@xxxxxxxxxxxx> wrote:



On Jun 29, 9:38 am, Matt <mwagn...@xxxxxxxxx> wrote:

I am performing a collection of values from different cells on a
work*** and inserting them into an Access database. When I update
an Access database with the value in C2, which in this case is the
number 2, I need to it to go into the Access record as "0002". The
field in Access is a Text field.

Is there some type of Format function that I can use to push the C2
value into Access the way I need it?

THanks

Hi Mat
You can use a custom format, (Format> Cells go to the Number tab and
select Custom), and insert this following 0000#. However this will not
change the value, only what is displayed. To change the actual value,
to match what is being displayed, will need a small VBA macro. I have
it if you need it, let me know.
Cheers
Dave

I have tried using the custom format, and you're right, it only
changes the appearance not the value. If you have that macro I would
appreciate taking a look at it.

Thanks

Hi Mat,

I'm sure there is a much more efficient way of doing this, but this
works for me. Hope it helps you mate.

Sub InsertZeros()
Dim x As Integer
Dim Rng As Range

Selection.NumberFormat = "@"
For Each Rng In Selection
x = Len(ActiveCell)
Select Case x
Case Is = 1
Rng.Value = "0000" & Rng
Case Is = 2
Rng.Value = "000" & Rng
Case Is = 3
Rng.Value = "00" & Rng
Case Is = 4
Rng.Value = "0" & Rng
Case Is = 5
Rng.Value = Rng
End Select
ActiveCell.Offset(1, 0).Activate
Next


End Sub

.