Luis Diaz
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Rather than iterating column R, why not just invoke a find/replace method for each of the items in your configuration (translate) range?
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.
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.
ASKER
@aikmark: could you please show us an example?
Thank you again for your help.
Thank you again for your help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
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
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.
ASKER
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
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.
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.
ASKER
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.
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.