VBA: replace numbers in one column with text

[FryGirl]

For the sake of learning a new technique in VBA, the code below works fine to replace certain numbers with text, but can it be converted into an array construct where it uses Lbound and Ubound? Is there a better method?

[FONT=arial]Sub ReplaceNumbers()[/FONT]
[FONT=arial]    Dim c As Range[/FONT]
[FONT=arial]    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))[/FONT]
[FONT=arial]        Select Case c.Value[/FONT]
[FONT=arial]        Case 1[/FONT]
[FONT=arial]            c.Value = "Working Project"[/FONT]
[FONT=arial]        Case 2[/FONT]
[FONT=arial]            c.Value = "Delayed Project"[/FONT]
[FONT=arial]        Case 3[/FONT]
[FONT=arial]            c.Value = "Completed Project"[/FONT]
[FONT=arial]        End Select[/FONT]
[FONT=arial]    Next c[/FONT]
[FONT=arial]End Sub[/FONT]

 

[My Aswer Is This]

This does not address your question. But is another way to do what you want.

Sub ReplaceNumbers()
    Dim c As Range
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Select Case c.Value
        Case c.Value
        c.Value = Choose(c.Value, "Working Project", "Delayed Project", "Completed Project")
        
        End Select
    Next c
End Sub

 

[Peter_SSs]

@ FryGirl

For 3 values like that, I would probably just go with code that does 3 Find/Replace processes on the column.
If the data is very large and there are many different values to find/replace then an array could be used and would be considerably faster than cycling through the actual cells as your sample code does.

Examples of each approach:

Sub Find_Replace_v1()
  Dim Bits As Variant
  Dim i As Long
  
  Const FindRepl As String = "1|Working Project|2|Delayed Project|3|Completed Project"
  
  Bits = Split(FindRepl, "|")
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    For i = 0 To UBound(Bits) - 1 Step 2
      .Replace What:=Bits(i), Replacement:=Bits(i + 1), LookAt:=xlWhole
    Next i
  End With
  Application.ScreenUpdating = True
End Sub


Sub Find_Replace_v2()
  Dim a As Variant
  Dim i As Long
  Dim Repl As String
  
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      Select Case a(i, 1)
        Case 1: Repl = "Working Project"
        Case 2: Repl = "Delayed Project"
        Case 3: Repl = "Completed Project"
        Case Else: Repl = a(i, 1)
      End Select
      a(i, 1) = Repl
    Next i
    .Value = a
  End With
End Sub

 

[hiker95]

FryGirl,

Here is a macro code that does not do any looping thru the rows in column A that contain 1, 2, or, 3.

Please try the following macro on a copy of your workbook/worksheet.

Sub ReplaceNumbers_V2()
' hiker95, 02/25/2018, ME1045103
Dim Addr As String
Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""1"",@)),""Working Project"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""2"",@)),""Delayed Project"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""3"",@)),""Completed Project"",@)", "@", Addr))
Columns(1).AutoFit
End Sub

 

[Peter_SSs]

Here is a macro code that does not do any looping thru the rows in column A that contain 1, 2, or, 3.

Though note that the description was "to replace certain numbers with text". To me that implies that the data could contain other numbers (or in fact any other values, including blank) as well as 1,2,3. If that is possible, then your code could produce considerably different results to the OP's code.

 

[FryGirl]

Thank you all for the responses.

My overall data set will be around 500 cells, so not that large. Just for fun and to learn more about code and efficiency, I ran some tests on 100000 cells.

The results
ReplaceNumbers_V2 - hiker95
2.516 secs
Find_Replace_v1 - Peter
2.484 secs
Find_Replace_v2 - Peter
0.438 secs
ReplaceNumbers - MAIT
0.430 secs
ReplaceNumbers2 - FryGirl
0.422 secs

@Peter

an array could be used and would be considerably faster

I'm guessing v2 is the array version?

 

[Peter_SSs]

.. I ran some tests on 100000 cells.

The results
ReplaceNumbers_V2 - hiker95
2.516 secs
Find_Replace_v1 - Peter
2.484 secs
Find_Replace_v2 - Peter
0.438 secs
ReplaceNumbers - MAIT
0.430 secs
ReplaceNumbers2 - FryGirl
0.422 secs

I am extremely surprised by some of those results but before questioning them I should ask what your sample data was like?
Was it 100,000 rows all of which contained just 1, 2 or 3? Or were there any other numbers, blank cells, text values in the range? Would/might your real data perhaps have any of these alternatives?

Also, is ReplaceNumbers2 - FryGirl the original code from post #1 just renamed?

I'm guessing v2 is the array version?

Correct.

 

[hiker95]

Thank you all for the responses.

FryGirl,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[FryGirl]

Hi Peter,

Was it 100,000 rows all of which contained just 1, 2 or 3?

Yes.

The real data will only consist of numbers, and it this time, there is only one number outside of the 1,2, or 3 which is not a concern.

Also, is ReplaceNumbers2 - FryGirl the original code from post # 1 just renamed?

Yes.

Since post # 2 had the same name, I renamed mine.

 

[Peter_SSs]

The real data will only consist of numbers, and it this time, there is only one number outside of the 1,2, or 3 which is not a concern.

You say that there is only one number outside 1, 2 or 3 so it isn't a concern. I would suggest that it may be a concern, depending on which, if any, of the suggested codes you choose & what should happen with any such values - see blue comments below.

I also previously expressed surprise about your timing data so I did some tests too. For the timing tests I generated 100,000 rows randomly containing only 1, 2 or 3 and then duplicated that identical worksheet for each of the tests.
I have listed my times in red beside yours, and added a couple of notes about my point above. As different machines will run at different speeds, it is the relative speed of the various codes that would be relevant.

Just for fun and to learn more about code and efficiency, I ran some tests on 100000 cells.

The results
ReplaceNumbers_V2 - hiker95
2.516 secs <del>0.073</del> 0.730 (If any 'extra' numbers contain a 1, 2 or 3 digit - eg 14 - the cell will be replaced by one of the 3 text options)
Find_Replace_v1 - Peter
2.484 secs 1.375
Find_Replace_v2 - Peter
0.438 secs 0.164
ReplaceNumbers - MAIT
0.430 secs 5.547 (Cells containing any numbers other than 1,2,3 will be cleared)
ReplaceNumbers2 - FryGirl
0.422 secs 5.180