• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help on the below filter and replace contentes - VBA code


Hi Team,

Hello Team,

I need code on the below scenario:

I have data dump which contains 15 columns of data. I need code to perform the below steps:

Col A(Region), Col D(country), Col J(comments).

For eg: I filter the Col A (Region) by "ANZ". Then column D(country) contains like PAK, ENG, AU, NZ etc.,(In this PAK and ENG is not part of ANZ I need to replace this with one conditon i.e., based on comments in Col J) , I will see the column J(comments) based on the content if it contains AU/NZ i ll replace the column D contents with the same. OR if it contains other than AU and NZ anything(eg Ind, Pak, ENG ) I will replace by default as AU. , How do I do this by coding.

I need to do the same for different region based on different condition. I have made a list as well for all the region. How do I make this by VBA.



Luke M

Excel Ninja
Here a start hopefully:

Sub DataHandler()
Dim xRegion As String
Dim Crit1 As String
Dim Crit2 As String
Dim xComment As String
'Based on a table or something, you could set these parameters
xRegion = "ANZ"
Crit1 = "AU"
Crit2 = "NZ"
'This is the section that does the work
For Each c In Range("A2:A100")  ' adjust as needed
If c = xRegion Then
xComment = c.Offset(0, 9).Value
If InStr(xComment, Crit1) > 0 Or InStr(xComment, Crit2) > 0 Then
c.Offset(0, 3) = xComment
c.Offset(0, 3) = Crit1
End If
End If
End Sub


New Member
Hi Luke,

I hope you can assit me I have a simular task. My code finds text in column G and uses a refence sheet to replace the correct text in the same column. Now I need to use the same code to find reference in Range(b:b) and offset the value in column O. My only problem is that its not exactly. Instead of changing the value in sheet 1 it changed in the refence sheet column A.

I know I need to Offset(0, 14).Value but Im not sure where that will go or because I have added additional funtions if I need to declare string.

here is my code..

Sub SupplierID()


For i = 2 To 20

gFind = ActiveWorkbook.Worksheets("Reference").Cells(i, 1).Value

gNewValue = ActiveWorkbook.Worksheets("Reference").Cells(i, 2).Value



Cells.Replace What:=gFind, Replacement:=gNewValue, LookAt:=xlPart, SearchOrder _

:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _



End Sub