Format "0000"
 

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

Format "0000"
 

On Jun 29, 9:38 am, Matt wrote:
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

Format "0000"
 

On Jun 29, 9:43 am, Limey wrote:
On Jun 29, 9:38 am, Matt wrote:

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

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

Format "0000"
 

On Jun 29, 9:47 am, Matt wrote:
On Jun 29, 9:43 am, Limey wrote:



On Jun 29, 9:38 am, Matt wrote:

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

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

Format "0000"
 

On Jun 29, 10:07 am, Limey wrote:
On Jun 29, 9:47 am, Matt wrote:





On Jun 29, 9:43 am, Limey wrote:

On Jun 29, 9:38 am, Matt wrote:

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

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

- Show quoted text -

It worked perfect! Thanks for the help!

Format "0000"
 

On Jun 29, 10:18 am, Matt wrote:
On Jun 29, 10:07 am, Limey wrote:



On Jun 29, 9:47 am, Matt wrote:

On Jun 29, 9:43 am, Limey wrote:

On Jun 29, 9:38 am, Matt wrote:

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

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

- Show quoted text -

It worked perfect! Thanks for the help!

Cool! Glad to help mate. :)

Format "0000"
 

On Jun 29, 10:23 am, Limey wrote:
On Jun 29, 10:18 am, Matt wrote:





On Jun 29, 10:07 am, Limey wrote:

On Jun 29, 9:47 am, Matt wrote:

On Jun 29, 9:43 am, Limey wrote:

On Jun 29, 9:38 am, Matt wrote:

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

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

- Show quoted text -

It worked perfect! Thanks for the help!

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?

Format "0000"
 

On Jun 29, 10:25 am, Matt wrote:
On Jun 29, 10:23 am, Limey wrote:





On Jun 29, 10:18 am, Matt wrote:

On Jun 29, 10:07 am, Limey wrote:

On Jun 29, 9:47 am, Matt wrote:

On Jun 29, 9:43 am, Limey wrote:

On Jun 29, 9:38 am, Matt wrote:

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

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

- Show quoted text -

It worked perfect! Thanks for the help!

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?- 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.

Format "0000"
 

On Jun 29, 10:30 am, Matt wrote:
On Jun 29, 10:25 am, Matt wrote:



On Jun 29, 10:23 am, Limey wrote:

On Jun 29, 10:18 am, Matt wrote:

On Jun 29, 10:07 am, Limey wrote:

On Jun 29, 9:47 am, Matt wrote:

On Jun 29, 9:43 am, Limey wrote:

On Jun 29, 9:38 am, Matt wrote:

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

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

- Show quoted text -

It worked perfect! Thanks for the help!

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?- 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.

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