Update this macro to make it generate a counter as text

[B Roberson]

I have the macro listed below that does generate a numeric value for each cell in column D for as many rows as there are records. I would like to adjust this macro to where if the counter is less than 10, that the active cellwould get 001,002 etc. If it is 35, it should past 035 as text, and if its625, it would not have to paste any leading zeroes in that case. So, I guess I need a case or a series of if then else type structure, but I am not sure how to format the "0", "00", or no leading zeroes depending on the number involved.


Sub add_page_and_line_new()
Dim cRows As Long
cRows = Cells(Rows.Count, "A").End(xlUp).Row
Dim linenumcounter As Integer
linenumcounter = 1
Range("d1").Select
For i = 1 To cRows

If linecounter < 10 Then

With ActiveCell
.Value = linenumcounter
.Copy
.PasteSpecial Paste:=xlValues
.Offset(1, 0).Select
End With
linenumcounter = linenumcounter + 1

End With
Next

 

[Claus Busch]

Hi

Am Wed, 17 Oct 2012 09:14:02 -0700 (PDT) schrieb B Roberson:

I have the macro listed below that does generate a numeric value for each cell in column D for as many rows as there are records. I would like to adjust this macro to where if the counter is less than 10, that the active cell would get 001,002 etc. If it is 35, it should past 035 as text, and if its 625, it would not have to paste any leading zeroes in that case. So, I guess I need a case or a series of if then else type structure, but I am not sure how to format the "0", "00", or no leading zeroes depending on the number involved.

try:

Sub add_page_and_line_new()
Dim cRows As Long

cRows = Cells(Rows.Count, "A").End(xlUp).Row

With Range("D1" & cRows)
.Formula = "=row()"
.Copy
.PasteSpecial xlPasteValues
.NumberFormat = "000"
End With

End Sub


Regards
Claus Busch

 

[B Roberson]

Thank you, I will give this a try.

 

[B Roberson]

I got the leading zeros but I had trouble with it uploading to this database and had to go back and do a formula =istext() on the resulting cells and found that they were not actually text. So, my question is how do I get them to display like this but actually be text? I used an =text()sometimesto bring a number over as text. Is there an alternative to that here?

 

[Claus Busch]

Hi,

Am Wed, 17 Oct 2012 14:02:38 -0700 (PDT) schrieb B Roberson:

I got the leading zeros but I had trouble with it uploading to this database and had to go back and do a formula =istext() on the resulting cells and found that they were not actually text. So, my question is how do I get them to display like this but actually be text? I used an =text()sometimes to bring a number over as text. Is there an alternative to that here?

try:
With Range("D1" & cRows)
.Formula = "=text(row(),""000"")"
.Copy
.PasteSpecial xlPasteValues
End With


Regards
Claus Busch

 

[B Roberson]

That fixed it based on doing an istext() formula on the resulting fields. Surely now it will upload to my database in Alpha 5 correctly.

 

[B Roberson]

I thought this solution would fix both my columns, and it did for the one that was strictly working off the rows. I have another field that is numericand its values are almost always the numeric 1 or 4, and they need to convert to text also, with the 1 being 01, and the 4 being 04. You can display it that way all you want with .NumberFormat = "00" but that is only for display, its still a numeric field with this in there.

If I were doing this on a cell and copying it down, it would be a formula in B1 with something like =text(A1,00)

 

[Claus Busch]

Hi,

Am Thu, 18 Oct 2012 08:21:13 -0700 (PDT) schrieb B Roberson:

I thought this solution would fix both my columns, and it did for the one that was strictly working off the rows. I have another field that is numeric and its values are almost always the numeric 1 or 4, and they need to convert to text also, with the 1 being 01, and the 4 being 04. You can display it that way all you want with .NumberFormat = "00" but that is only for display, its still a numeric field with this in there.

If I were doing this on a cell and copying it down, it would be a formula in B1 with something like =text(A1,00)

=TEXT(A1,"00")


Regards
Claus Busch