Update this macro to make it generate a counter as text
 

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.


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

Update this macro to make it generate a counter as text
 

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:D" & cRows)
.Formula = "=row()"
.Copy
.PasteSpecial xlPasteValues
.NumberFormat = "000"
End With

End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Update this macro to make it generate a counter as text
 

Thank you, I will give this a try.


try:



Sub add_page_and_line_new()

Dim cRows As Long



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



With Range("D1:D" & cRows)

.Formula = "=row()"

.Copy

.PasteSpecial xlPasteValues

.NumberFormat = "000"

End With



End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Update this macro to make it generate a counter as text
 

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?


With Range("D1:D" & cRows)

.Formula = "=row()"

.Copy

.PasteSpecial xlPasteValues

.NumberFormat = "000"

End With



End Sub





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Update this macro to make it generate a counter as text
 

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:D" & cRows)
.Formula = "=text(row(),""000"")"
.Copy
.PasteSpecial xlPasteValues
End With


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Update this macro to make it generate a counter as text
 

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.


On Thursday, October 18, 2012 1:25:37 AM UTC-5, Claus Busch wrote:
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:D" & cRows)

.Formula = "=text(row(),""000"")"

.Copy

.PasteSpecial xlPasteValues

End With





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Update this macro to make it generate a counter as text
 

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)




On Thursday, October 18, 2012 9:21:57 AM UTC-5, B Roberson wrote:
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.





On Thursday, October 18, 2012 1:25:37 AM UTC-5, Claus Busch wrote:

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:D" & cRows)



.Formula = "=text(row(),""000"")"



.Copy



.PasteSpecial xlPasteValues



End With











Regards



Claus Busch



--



Win XP PRof SP2 / Vista Ultimate SP2



Office 2003 SP2 /2007 Ultimate SP2

Update this macro to make it generate a counter as text
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2