Data substitution in a spreadsheet

ThomasOtte

New Member
Joined
Oct 1, 2014
Messages
11
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.../
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

You could adapt something like this:

Code:
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
 
Last edited:

ThomasOtte

New Member
Joined
Oct 1, 2014
Messages
11
Thanks very much for your help!
How do I use this in my spreadsheet? I have never used anything like this...
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 1, 2014
Messages
11

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 1, 2014
Messages
11
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top