• 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

    Hui...

  • 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

vinu

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


Regards,

Vin
 

Luke M

Excel Ninja
Here a start hopefully:

[pre]
Code:
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
Else
c.Offset(0, 3) = Crit1
End If
End If
Next
End Sub
[/pre]
 

danielle2706

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


Columns("G:G").Select

Range("G1").Activate


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

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

False


Next


End Sub
 
Top