Help with excel

crumplydazzler

New Member
Joined
Jun 24, 2017
Messages
3
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:
Code:
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
 
Upvote 0
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:
Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top