Data substitution in a spreadsheet

[ThomasOtte]

Hi everyone,

new here, so bear with me.

I have a spreadsheet full of letters A,B,C...N,O in separate cells
I would like to replace these letters by names. Just as there are 15 letters, I have 15 names to match.

A = John
B= Pete
C= Dave
etc.

Names are placed into the worksheet too, to be able to refer to them.

I did this manually with the "Search and Replace function" but I am sure that Excel has a much more clever way to do this than do the Replace stuff 15 separate times.

I can't figure it out and it frustrates me beyond all recognition...

Can anyone help me out? I have no experience at all with VBA.

Thanks ever so much for your effort in advance.

Best,
Thomas

/The file is a schedule for students working shifts in teams of 2. A colleague made this schedule for me with special software. Could have inserted names instead of letters in the first place, but what's the fun in that.../

 

[ParamRay]

.
.

You could adapt something like this:

Sub ReplaceValues()

    Dim Arr(3, 2) As String
    Dim Rng As Range
    Dim i As Byte
    
    'set values to be replaced
    Arr(0, 0) = "A"
    Arr(1, 0) = "B"
    Arr(2, 0) = "C"
    
    'set replacement values
    Arr(0, 1) = "Alpha"
    Arr(1, 1) = "Bravo"
    Arr(2, 1) = "Charlie"
    
    'loop through cells
    'and replace values
    For Each Rng In ActiveSheet.UsedRange
        For i = LBound(Arr, 1) To UBound(Arr, 1)
            If LCase(Rng.Value) = LCase(Arr(i, 0)) Then
                Rng.Value = Arr(i, 1)
            End If
        Next i
    Next Rng

End Sub

 

[ThomasOtte]

Thanks very much for your help!
How do I use this in my spreadsheet? I have never used anything like this...

 

[Rick Rothstein]

How do I use this in my spreadsheet? I have never used anything like this...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste gpeacock's code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ReplaceValues) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ThomasOtte]

Can I just copy the code and add on to it? The list of items to be replaced is 15, so can I go:

Arr (2,0) = "C"
Arr (3,0) = "D"
etc

and

Arr (2, 1) = "Charlie"
Arr (3, 1) = "Delta"
etc

until I have reached "O" to be replaced with "Oscar"? Quickly tried it a minute ago and it ran an error somewhere... didn't check properly though for any typos.
Should this work or do I have to adjust more than just adding to the list in the same format?

Thanks!!

 

[Rick Rothstein]

Can I just copy the code and add on to it? The list of items to be replaced is 15, so can I go:

Arr (2,0) = "C"
Arr (3,0) = "D"
etc

and

Arr (2, 1) = "Charlie"
Arr (3, 1) = "Delta"
etc

Yes, but you must change this line of code to account for the extra items in the array...

Dim Arr(3, 2) As String

The count starts from 0 (not 1), so for 15 letter/names you would need to replace the 3 with 14

 

[ThomasOtte]

Worked like clockwork. Thank you so much for your help!!

Anyone know how to do this with regular formulas, just for the fun of it?