How to efficiently change number to text in VBA

user_11493948 0
Viewed 319

I am looking for a way to create a toggle, which I could use to change cell value to "General" format or "Text" format as well as making it appear as text, rather than a number.

Let's say I have 3 values: 2839283 8572839 5922038

What I have tried (it works) is this:

Dim MyRng as Range
Dim MyCell as Range

Set MyRng = Selection

if MyRng.NumberFormat = "General" Then
   For each MyCell in MyRng
      MyCell.NumberFormat = "@"
      MyCell.Value2 = CStr(MyCell.Value2)
   next MyCell
Else
   For each MyCell in MyRng
      MyCell.NumberFormat = "General"
      MyCell.Value2 = MyCell.Value2
   next MyCell
End if

Is there any way to achieve the same objective without using a for loop? I am trying to make it efficient and when this macro is applied to 20k rows then... Well, it works but rather slow.

I have tried to simply call:

MyRng.NumberFormat
MyRng.Value2

And it works with conversion from text to numbers, but sadly it shows mismatch error when trying to convert numbers to text (that is why I have used a loop there).

Any ideas?

vba excel
2 Answers

Toggle General and Text

  • See if this helps a bit or gives you an idea.

The Code

Option Explicit

Sub toggleGeneralText()
   
    If TypeName(Selection) <> "Range" Then Exit Sub

    Dim rg As Range: Set rg = Selection
    
    If rg.NumberFormat = "General" Then
        Dim rCount As Long: rCount = rg.Rows.Count
        Dim cCount As Long: cCount = rg.Columns.Count
        Dim Data As Variant
        If rCount > 1 Or cCount > 1 Then
            Data = rg.Value2
        Else
            ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value2
        End If
        Dim i As Long
        Dim j As Long
        For i = 1 To rCount
            For j = 1 To cCount
                Data(i, j) = CStr(Data(i, j))
            Next j
        Next i
        rg.NumberFormat = "@"
        rg.Value2 = Data
    Else
        rg.NumberFormat = "General"
        rg.Value2 = rg.Value2
    End If

End Sub
user_98140690

My previous attempt was wrong. Let me try this again.

Dim MyRng As Range
Dim MyCell As Range

Set MyRng = Selection

If MyRng.NumberFormat = "General" Then
    MyRng.NumberFormat = "@"
    MyRng.FormulaArray = MyRng.Value2
Else
    MyRng.NumberFormat = "General"
    MyRng.Value2 = MyRng.Value2
End If`
user_143854190
Related