VBA Fast Convert # to Text add ' Before Number

[VenturSum]

Programmers,

I REALLY need your your Help speeding up some code.

In one worksheet a table has 70,000 rows.

In column "Q" (17), 40,000 cells have numbers; 30,000 are empty rows.

I need to convert these numbers into text
by adding a single quote (') before each number.

My code sorts the table in Column Q A-Z,
So the numbers are at the top
Then loop through each column:

lRow = 2
Do While .cells(lRow, 17).value <> ""

   .cells(lRow, 17).value  = "'" & .cells(lRow, 17).value 

   lRow = lRow + 1
Loop

Can you provide any other method to yield the same result??

Respectfully,

John Annapolis, MD

 

[AndyMachin]

If you want to speed up, I will suggest using a For loop instead of a Do while.
Also, use the 'Application.ScreenUpdating = False' at the beginning of the code, and turn it back to 'True' at the end of the code.

Best,
Andy

 

[VenturSum]

Why would a For be faster than a Do While loop?

Also, I am unsure if the data being held in a Table slows things down?


Both Screenupdating and AutoCalcutions are off.

Thank you,

John

 

[AndyMachin]

Hi John,
I haven't use the Do loops for years, so I can't remember the performance difference, and maybe there is no difference at all. When I learned how to use the For loops I never used Do anymore. Also I think that For Each Cell in Range.range is the correct and efficient way to loop through a range.

Try it and let us know if you notice any improvement. I would use this code.

Dim rCell as Range
Dim rRng as Range
dim uFila as Long

uFila = Range("Q" & rows.count).end(xlup).row 'this line look for the last row with value, so the loop do not run beyond the last cell with value

set rRng = Range("Q1:Q" & uFila)

For each rCell in rRng.Cells
   if isNumeric(rcell) then
       rcell.value = "'" & rcell.value
   end if
next rCell

 

[VenturSum]

Andy,

Our coding is not too dissimilar
I'll try using both the Set Range and the For loop.

Btw: 40,000 rows took about 1 hour -- 11 / second!!
This is going through a Citrix network/server, which must also be slowing things down.

Respectfully,

John

 

[shg]

How about no loop?

  Range("Q2", Cells(Rows.Count, "Q")).TextToColumns _
      DataType:=xlFixedWidth, _
      FieldInfo:=Array(0, xlTextFormat)

 

[VenturSum]

SHG...

Can I use this method if the data is in a Table?

John

 

[VenturSum]

I need to convert the cells to text, because the cells contain job numbers.
E.g.:

15023
15045G
15245-003

When I copy the data to a new sheet,
Excel sometimes convert the cells that look like: 15254-002 into Exponent Notation. -> 152.54

So for sorting and copying it is best if everything looks like text with a preceding single quote: '15254-002.

Respectfully,

John in Annapols, MD

 

[Rick Rothstein]

Give this macro a try and see if it is any faster...

[table="width: 500"]
[tr]
	[td]Sub PrefixAnApostophe()
  With Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Give this macro a try and see if it is any faster...

[table="width: 500"]
[tr]
	[td]Sub PrefixAnApostophe()
  With Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

And if the above is still slow, then maybe this would be faster...

Sub PrefixAnApostophe()
  Dim R As Long, Arr As Variant
  Arr = Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
  For R = 2 To UBound(Arr)
    If Len(Arr(R, 1)) Then Arr(R, 1) = "'" & Arr(R, 1)
  Next
  Range("Q2").Resize(UBound(Arr)) = Arr
End Sub