Format "0000"

[Matt]

I am performing a collection of values from different cells on a
worksheet 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

 

[Limey]

I am performing a collection of values from different cells on a
worksheet 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

 

[Matt]

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

 

[Limey]

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

 

[Matt]

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- Hide quoted text -

- Show quoted text -

It worked perfect! Thanks for the help!

 

[Limey]

It worked perfect! Thanks for the help!

Cool! Glad to help mate.

 

[Matt]

Cool! Glad to help mate. - Hide quoted text -

- Show quoted text -

One more quick question. That macro works on the currently active
cell that is selected. How would I change it to always have it run on
cell C2?

 

[Matt]

One more quick question. That macro works on the currently active
cell that is selected. How would I change it to always have it run on
cell C2?- Hide quoted text -

- Show quoted text -

Nevermind I guess. I just add Range("C2").Activate line before the
Selection.NumberFormat line and that seems to take care of it.

 

[Limey]

Nevermind I guess. I just add Range("C2").Activate line before the
Selection.NumberFormat line and that seems to take care of it.

Oh Man you beat me to it ))
I got a macro that goes the opposite way also, takes a text 00002 for
example and reduces back the the number only, then formats it with
0000#. Just FYI if you needed that.
Dave