VBA: replace values based on a Config Sheet

Hello experts, I use the following loop to replace values

For Each c In Range("R2", Range("R" & Rows.Count).End(xlUp))
If c.Value = "Fra" Then c.Value = "FGX"
If c.Value = "In" Then c.Value = "I"
If c.Value = "" Then c.Value = "T"
Next c

Instead of inserting the values in the code I was wondering if I can use a config-sheet in which I report:

Column A          ; Column B ;  Column C ; Column D
Orginal Value   ; Revised Value ; Column letter concerned by the change; Sheet concerned by the modification

Based on my example The config-sheet should be composed as following
Fra;FGX;R;Test
In;I;R;Test
;T;R;Test

I supposed that my for each statement is incomplete as the letter column and the sheet concerned by the change and reported in the config-sheet could be different.

Thank you again for your help
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
Have a sheet be called config, then use this code:

Sub test()


Dim config_sheet As Worksheet

Set config_sheet = ThisWorkbook.Sheets("config")

'MsgBox config_sheet.UsedRange.Rows.Count

counter = 1

Do While counter <= config_sheet.UsedRange.Rows.Count
  
    var1 = config_sheet.Cells(counter, 1)
    var2 = config_sheet.Cells(counter, 2)
    var3 = config_sheet.Cells(counter, 3)
    var4 = config_sheet.Cells(counter, 4)
    
    For Each c In ThisWorkbook.Sheets(var4).Range(var3 + "2", ThisWorkbook.Sheets(var4).Range(var3 & ThisWorkbook.Sheets(var4).Rows.Count).End(xlUp))
        If c.Value = var1 Then c.Value = var2
    Next c
    counter = counter + 1
Loop
 
 

End Sub

Open in new window


In the config sheet, use the 4 variables you mentioned before, but in each cell, not in the ; format you showed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
Rather than iterating column R, why not just invoke a find/replace method for each of the items in your configuration (translate) range?
LD16Author Commented:
@Kimputer: your code works perfectly, thank you for this solution. You are right I omit in the my question the Worksheet.

 I have few remarks:
1-If I want to put headers in the Config sheet and ignore the headers counter may start at 2?
counter =2
2-How can I put some check statements such as:
-If Sheets reported in Column D doesn't exit in my workbook msgbox "the sheet" .... is not valid and exit sub
-If Letter column reported is numerical and not alphabetical msgbox "Column letter" .... is not valid and exit sub

Thank you again for your help.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LD16Author Commented:
@aikmark: could you please show us an example?
Thank  you again for your help.
aikimarkCommented:
Please test this:
Sub Q_28695621()
    Dim wksConfig As Worksheet
    Dim wks As Worksheet
    Dim rng As Range
    
    Set wksConfig = Worksheets("config")
    Application.ScreenUpdating = False
    For Each rng In wksConfig.Range(wksConfig.Range("C2"), wksConfig.Range("C2").End(xlDown))
        Set wks = Worksheets(rng.Offset(0, 1).Value)
        wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp)).Replace rng.Offset(0, -2).Value, rng.Offset(0, -1).Value
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

LD16Author Commented:
@aikimark: your code works however I don't know why the End.(xlDown) take into account empty values.

In order to workaround this I add an if statement:
I don't know if it is the best to do this.
Another question, is there a way to exit sub with a msg when the destination is not valid or empty and when the letter column is numerical and not alphabetical?

Thank you again for your help.

Sub Replacement()
    Dim wksConfig As Worksheet
    Dim wks As Worksheet
    Dim rng As Range
    
    Set wksConfig = Worksheets("Config-R")
    Application.ScreenUpdating = False
     For Each rng In wksConfig.Range(wksConfig.Range("C2"), wksConfig.Range("C2").End(xlDown))
     If rng = "" Then
     Exit Sub
     Else
        Set wks = Worksheets(rng.Offset(0, 1).Value)
        wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp)).Replace rng.Offset(0, -2).Value, rng.Offset(0, -1).Value
     End If
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

aikimarkCommented:
The column and sheet cells must never be empty.  If you need to validate your config data, then that should be code that executes before the code I posted.  You shouldn't do a partial data change only to abort when you detect a config error.  The error(s) should be spotted before any of the change activities.
LD16Author Commented:
Hello aikimark

Please find attached my file. As recommended I putted the verification process in another sub and before the replacement process, however I am not able to properly exit the sub when I proceed like this. (Sub replacement1).
However When I putt the code of verification as it was done in replacement2 I am able to properly exit sub.

I was also wondering how can I check if values of column C are alphabetical and not numeric and if they are composed by just one character.

Thank you again for your help.
Replace-values-based-on-Config-Sheet.xls
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for aikimark's comment #a40873304

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
LD16Author Commented:
Hello Martin,

I have already selected multiple solutions. I was waiting to have complementary comment about the solutions. But overall the solutions are in accordance to the request. Please, don't close the question.

Thank you in advance for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.