Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Kimputer
Kimputer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Rather than iterating column R, why not just invoke a find/replace method for each of the items in your configuration (translate) range?
Avatar of Luis Diaz

ASKER

@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.
@aikmark: could you please show us an example?
Thank  you again for your help.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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

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.
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
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.
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.