Excel VBA to replace column values with another value?

Question

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.:

1 = American Indian or Alaskan Native
2 = Asian, Asian American, or Pacific Islander
3 = African American or Black
4 = Mexican or Mexican American

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?

@Mikeb's comment on VLookUp sounds like the best bet, but if it does not suit, you may wish to look at ADO. — Fionnuala
– Fionnuala, Commented Mar 9, 2011 at 15:55

Mikeb · Accepted Answer · 2011-03-09 15:53:56Z

6

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.

answered Mar 9, 2011 at 15:53

Mikeb

6,3813 gold badges31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Honorez Over a year ago

Right ! no need to VBA for that

Maverik · Accepted Answer · 2011-03-09 17:02:34Z

5

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.

edited Mar 9, 2011 at 17:02

answered Mar 9, 2011 at 16:09

Maverik

5,68138 silver badges50 bronze badges

4 Comments

Dave Mackey Over a year ago

Thanks. I tried that. Can I just add this to a new module or does it need to go under Sheet1 or ThisWorkbook (I'm very new at VBA)? How exactly do I get this module to run against the selected column?

Maverik Over a year ago

well I ran it by dropping this in a module and then using run macro dialog while i had my desired selection set in sheet. Probably the easiest way of doing it. Or you can hard-code the range of RangeCells variable with a Set RangeCells = Sheet1.Range("A1:A4") just after Dim RangeCells as Range line

Dave Mackey Over a year ago

Awesome, thanks! The only problem I'm having now is a "type mismatch" error when I run the code from the line RanngeCell.Value = LookupArray(RangeCell.Value)

Maverik Over a year ago

That would imply the cell has a value that isn't numeric. I'll update the code to make it more robust. Will also compensate if you end up going outside the range of array index.

GrzMat · Accepted Answer · 2013-09-05 13:07:19Z

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.

Collectives™ on Stack Overflow

Excel VBA to replace column values with another value?

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related