Help with excel

[crumplydazzler]

At work i use excel to create a customer demand plan.
one column has codes in which I have to use "find & replace" to turn them into names of colours.
This is very time consuming, is there any way that excel can do this automatically?

 

[Joe4]

Yes, with VBA. You can automate all the Find/Replace steps.
You would just need to have a list of all the values to Find and what to Replace with so you could loop through it.

Here is an example. I have data in column B on Sheet2 that needs to be replaced, and I have the Find/Replace values on Sheet3, in columns A and B:

Sub MyReplaceMacro()

    Dim myRow As Long
    Dim myFind As String
    Dim myReplace As String

    Application.ScreenUpdating = False

'   List of values to find/replace on Sheet3, range A2:B4
'   So loop through rows 2-5
    For myRow = 2 To 5
'       Get values to find/replace
        myFind = Sheets("Sheet3").Cells(myRow, "A")
        myReplace = Sheets("Sheet3").Cells(myRow, "B")
'       Replace values in column B on Sheet 2
        On Error GoTo 0
        Sheets("Sheet2").Columns("B:B").Replace What:=myFind, Replacement:=myReplace, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        On Error Resume Next
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[crumplydazzler]

Got to be honest I have no idea when it comes to macros

Yes, with VBA. You can automate all the Find/Replace steps.
You would just need to have a list of all the values to Find and what to Replace with so you could loop through it.

Here is an example. I have data in column B on Sheet2 that needs to be replaced, and I have the Find/Replace values on Sheet3, in columns A and B:

Sub MyReplaceMacro()

    Dim myRow As Long
    Dim myFind As String
    Dim myReplace As String

    Application.ScreenUpdating = False

'   List of values to find/replace on Sheet3, range A2:B4
'   So loop through rows 2-5
    For myRow = 2 To 5
'       Get values to find/replace
        myFind = Sheets("Sheet3").Cells(myRow, "A")
        myReplace = Sheets("Sheet3").Cells(myRow, "B")
'       Replace values in column B on Sheet 2
        On Error GoTo 0
        Sheets("Sheet2").Columns("B:B").Replace What:=myFind, Replacement:=myReplace, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        On Error Resume Next
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[Joe4]

You didn't provide any details, so I really couldn't write any code specific to your situation (that is why it is good to be as specific as possible in your explanations).

However, I tried to document my code enough that it should be pretty easy to modify to get to work for you without having to know any VBA.
I documented the code to tell you what each step is.

All you really have to do is copy the code "as-is" and make the following adjustments below (none of which requires knowing any VBA)
- Set-up your two column reference table (what you are replacing with what)
- Replace the Sheet names ("Sheet2", "Sheet3") with your sheet names
- Replace the row numbers (i.e. 2 through 5) with the row numbers where your reference table resides
- Replace the column references ("A" and "B") for columns your reference table resides in, and the column you are replacing on your other sheet.

If you have trouble, provide all the details above that we would need.