CODEWITHSUNDEEP
excelvba
Dave Mackey
Dave Mackey

Reputation: 4432

Excel VBA to replace column values with another value?

I have a worksheet with around 3,000 rows. One of these columns contains ethnicity data but it is in numeric format, rather than text format, e.g.:

I'd like to write a module in VBA that uses a Select Case (or...?) to swap out the values in this column from the integer to text. Can someone show me how to accomplish this?

Upvotes: 2

Views: 11158

Answers (3)

GrzMat
GrzMat

Reputation: 36

To do something like VLOOKUP, but with replacement you can try the below

Option Explicit
Sub ReplaceData()

Dim i As Integer
Dim ABCArray() As Variant, DEFArray As Variant

    ABCArray = Array("A", "b", "C")
    DEFArray = Array("D", "E", "F")

    With ActiveSheet.Range("A:A")

        For i = LBound(ABCArray) To UBound(ABCArray)

            .Replace What:=ABCArray(i), _
            Replacement:=DEFArray(i), _
            LookAt:=xlWhole, _
            SearchOrder:=xlByColumns, _
            MatchCase:=False

        Next i

    End With
End Sub

I cannot agree with VLOOKUP being better for one simple reason: we do macros to automate repeating actions and I assume someone is looking for macro solution mostly in cases like that.

Upvotes: 0

Maverik
Maverik

Reputation: 5671

Heres a quick VBA mashup that will replace your Current Selection values with substitutes:

Option Explicit
Option Base 1

Public Sub ReplaceData()

    Dim RangeCells As Range

    Dim LookupArray(4) As String, RangeCell As Range

    LookupArray(1) = "Test A"
    LookupArray(2) = "Test B"
    LookupArray(3) = "Test C"
    LookupArray(4) = "Test D"

    For Each RangeCell In Selection

        If IsNumeric(RangeCell.Value) And RangeCell.Value <= UBound(LookupArray) And RangeCell.Value >= LBound(LookupArray) Then
            RangeCell.Value = LookupArray(Val(RangeCell.Value))
        End If

    Next RangeCell

End Sub

Assumptions: all possible cell values are covered in array index. Otherwise select case is probably what you want to switch to. Let me know if you can't figure how to switch to that.

Hope this solves your problem.

Upvotes: 5

Mikeb
Mikeb

Reputation: 6361

A non VBA (simpler, in my opinion) approach in Excel would be to create a lookup table with that data - the integer in the first column, and the text in the second, and then in the column next to your data, use VLOOKUP to find the corresponding text.

Upvotes: 6

Related Questions