Luis Diaz
asked on
VBA: replace values in an specific column based on the values of another column
Hello experts,
I have the following procedure which allows me to replace a column which has empty values based on another column and highlight in green the values which have been replaced:
I would like to bring some improvements to this procedure:
1-Be able to call the procedure through a config sheet in order to be able to loop the various lines reported in the config sheet.
In this config Sheet I would have the parameter reported in the procedure (Sheet, ResultColumn and Refcolumn. Note in order to simplify the procedure I keep both columns in the same sheet.
2-Be able to highlight in red the various values for which there is a different value between ResultColumn and RefColumn (both columns should have values) in that case the procedure should just highlight in red and transfer them in an error-sheet.
I attached the reference file.
Thank you again for your help.
Replacement_based_on_columns.xlsm
I have the following procedure which allows me to replace a column which has empty values based on another column and highlight in green the values which have been replaced:
Sub Replacement(strWSname As String, strResultColumn, strRefColumn)
Dim NumRows As Long
NumRows = Worksheets(strWSname).Range("A1048576").End(xlUp).Row
For i = 2 To NumRows
If Worksheets(strWSname).Range(strResultColumn & i).Value = "" Then
Worksheets(strWSname).Range(strResultColumn & i).Value = Worksheets(strWSname).Range(strRefColumn & i).Value
Worksheets(strWSname).Rows(i).Interior.Color = rgbDarkGreen
End If
Next i
End Sub
Private Sub CommandButton1_Click()
Call Replacement("Sheet1", "B", "A")
End Sub
I would like to bring some improvements to this procedure:
1-Be able to call the procedure through a config sheet in order to be able to loop the various lines reported in the config sheet.
In this config Sheet I would have the parameter reported in the procedure (Sheet, ResultColumn and Refcolumn. Note in order to simplify the procedure I keep both columns in the same sheet.
2-Be able to highlight in red the various values for which there is a different value between ResultColumn and RefColumn (both columns should have values) in that case the procedure should just highlight in red and transfer them in an error-sheet.
I attached the reference file.
Thank you again for your help.
Replacement_based_on_columns.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tested and it works.
Thank you again for your help.
Thank you again for your help.
ASKER
I will test it as soon as I can.
Thank you again for your help.