Macro to find and replace based on different text within one cell

[rjwrig]

Hello,

I am looking for some help building a macro that would find and replace numbers with text for different questions on a questionnaire. For example my data file would have each question in a separate column and cell B2 would contain the label for what each response has been coded as numerically e.g.,

0 = [red], 1 = [blue], 2 = [orange], 3 = [yellow]

in the cells below would be a row for each participant and what option they selected (e.g. 0,1,2 or 3). What I need to do is replace the numbers with the corresponding text. Is there anyway to get Excel to look at what the corresponding text is for the different numbers in B2 and use this information to select and replace? It would be great if it would look at what is inside the [] as the text for different questions would be different.

Any help gratefully received. I have an attached an excel file. Column B would be an example of the raw data and what I would like to
be able to do is end up with column F by it looking at the wording in cell B2 0 = [Red], 1 = [Blue], 2 = [Yellow]

Is it possible to refer it to cell B2 and the labeling here in order to recode the column to look like column F? As the questions, and hence the labels will change often.

B2 is how our questions are exported in the raw data so I cant change the format of 0 = [Red], 1 = [Blue], 2 = [Yellow]


B

[skywriter]

In your example file the name of your sheet has a space at the end.

I removed that space as you see in the code the worksheet is referred to as "Example", not "Example ", be aware of this as you may have a habit of pressing the space bar after typing a name for a sheet and Excel sees the space as a character in the name.

Below is the code based on your example sheet and there's a workbook attached with the code installed.

This is custom code based on you formatting B2 exactly as you did and the numbers have to start at 0, as you have shown, the code doesn't actually look at the numbers, it finds the colors in the text and looks for 0 for the first color, 1 for the second etc.

If you take away the [] that you are using the code will fail.

Sub rjwrig()
Dim a, b As Long, c As String, rngData As Range
    With Worksheets("Example")
        a = Split(.Range("B2").Value, "]")
        Set rngData = .Range(.Cells(4, 2), .Cells(.Rows.Count, 2).End(xlUp))
    End With
    For b = LBound(a) To UBound(a)
        c = Trim(Mid(a(b), InStr(1, a(b), "[") + 1, Len(a(b)) - InStr(1, a(b), "[")))
        If c <> "" Then
            rngData.Replace What:=b, LookAt:=xlWhole, Replacement:=c
        End If
    Next b
End Sub

[rjwrig]

Hi,


This is so helpful, thank you so much. I've been having a play around (unsuccessfully as new to VBA) to see if I can refer to row 2 for column i in order to loop this code to run across multiple columns in the file?

Is this possible with the Split function?

I tried to use this code to loop through the file:
For i = 1 To 100
'Columns(i).Select

next i

And then change this line ' a = Split(Range("B2").Value, "]") ' so it wasn't referring to B2 but ' a = Split(Range(.Cells(2,i)).Value, "]")

Tried other ways too but whatever I try it ends in error in this line. I's just wondering if this is my poor coding and simple to rectify or if the split function wont allow it.

Thanks again for previous help, this macro is extremely helpful as is, we just have multiple variables so many columns in the data.

Best,

Becky

[skywriter]

Hi Becky,

So specifically what is different from the example you posted to your other sheet?

With your example I got the colors from B2 and applied them to the numbers in column B, B4:B18.

Are you saying you have other colors in row 2 of other columns and you want to apply those colors to each column?

For example you have colors in C2 and you want to apply them from C4 to C18 or whatever the last cell with a number in column C is?


Bruce

[skywriter]

Make sure you read my other post #4.

I made another example to see if it's possibly what you are looking to do.

Sub rjwrig()
Dim a, b As Long, c As String, rngData As Range, lc As Long, x As Long
    With Worksheets("Example")
    lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
        For x = 2 To lc
                a = Split(.Cells(2, x).Value, "]")
                Set rngData = .Range(.Cells(4, x), .Cells(.Rows.Count, x).End(xlUp))
            
            For b = LBound(a) To UBound(a)
                c = Trim(Mid(a(b), InStr(1, a(b), "[") + 1, Len(a(b)) - InStr(1, a(b), "[")))
                If c <> "" Then
                    rngData.Replace What:=b, LookAt:=xlWhole, Replacement:=c
                End If
            Next b
        Next x
    End With
    Application.ScreenUpdating = True
End Sub