Search Column for matches to a Range of Data, then modify cell
 

Hey I need a script to do the following. I have part of the script
written but I want to see if there is a way to compare the data in the
column to an range of data in a seperate worksheet. I need this
dynamic since people might add categories to this worksheet (changing
who the assigments go to). Basically I need the script to compare
every cell in a column to a category list and then change a different
field (name field) to the person with those categories. Categories and
name are in one worksheet (A column is name, B and C have hte
categories).

Below is the script I have, but I need the comparision to basically
compare the cell to a range instead of a single value. Is there an
easy way to do this without adding a third loop? Thanks.

Right now this script just replaces the field with "auto" but we need
the script to use the person's name which the category is assigned
to.

Sub searchAndReplace()

Dim currentPositionW1 As Integer
Dim currentPositionW2 As Integer
Dim PathName1 As String
Dim PathName2 As String
Dim w1 As Workbook
Dim w2 As Workbook
Dim searchstring As String
Dim counter As Integer
Dim currentCellVal As String
Dim i As Integer
Dim file1 As String
Dim file2 As String
Dim tabname1 As String
Dim tabname2 As String
Dim tempString As String

tempString = Range("D7").Value
file1 = Range("D6").Value
file2 = Range("D4").Value
PathName1 = Range("D5").Value
PathName2 = Range("D3").Value
tabname1 = tempString
tabname2 = "AutoSource"
Set w1 = Workbooks.Open(Filename:=PathName1 & file1)
Set w2 = Workbooks.Open(Filename:=PathName2 & file2)


Sheets(tabname2).Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Selection.AutoFilter Field:=10

currentPositionW1 = 1
For i = 0 To 1 Step 0
searchstring =
w1.Sheets(tabname1).Range("f1").Offset(currentPosi tionW1, 0).Value
If searchstring = "" Then Exit For
currentPositionW2 = 1
currentCellVal =
w2.Sheets(tabname2).Range("a1").Offset(currentPosi tionW2, 0).Value


Do While currentCellVal < ""


If currentCellVal = searchstring Then

w1.Sheets(tabname1).Range("a1").Offset(currentPosi tionW1, 0).Value =
"Auto"
counter = counter + 1
End If
currentPositionW2 = currentPositionW2 + 1
currentCellVal =
w2.Sheets(tabname2).Range("a2").Offset(currentPosi tionW2, 0).Value
Loop
currentPositionW1 = currentPositionW1 + 1
Next


MsgBox ("" & counter & " Items Set as AutoSource")

End Sub

Search Column for matches to a Range of Data, then modify cell
 

On Jun 7, 10:03 am, wrote:
Hey I need a script to do the following. I have part of the script
written but I want to see if there is a way to compare the data in the
column to an range of data in a seperate worksheet. I need this
dynamic since people might add categories to this worksheet (changing
who the assigments go to). Basically I need the script to compare
every cell in a column to a category list and then change a different
field (name field) to the person with those categories. Categories and
name are in one worksheet (A column is name, B and C have hte
categories).

Below is the script I have, but I need the comparision to basically
compare the cell to a range instead of a single value. Is there an
easy way to do this without adding a third loop? Thanks.

Right now this script just replaces the field with "auto" but we need
the script to use the person's name which the category is assigned
to.

Sub searchAndReplace()

Dim currentPositionW1 As Integer
Dim currentPositionW2 As Integer
Dim PathName1 As String
Dim PathName2 As String
Dim w1 As Workbook
Dim w2 As Workbook
Dim searchstring As String
Dim counter As Integer
Dim currentCellVal As String
Dim i As Integer
Dim file1 As String
Dim file2 As String
Dim tabname1 As String
Dim tabname2 As String
Dim tempString As String

tempString = Range("D7").Value
file1 = Range("D6").Value
file2 = Range("D4").Value
PathName1 = Range("D5").Value
PathName2 = Range("D3").Value
tabname1 = tempString
tabname2 = "AutoSource"
Set w1 = Workbooks.Open(Filename:=PathName1 & file1)
Set w2 = Workbooks.Open(Filename:=PathName2 & file2)

Sheets(tabname2).Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Selection.AutoFilter Field:=10

currentPositionW1 = 1
For i = 0 To 1 Step 0
searchstring =
w1.Sheets(tabname1).Range("f1").Offset(currentPosi tionW1, 0).Value
If searchstring = "" Then Exit For
currentPositionW2 = 1
currentCellVal =
w2.Sheets(tabname2).Range("a1").Offset(currentPosi tionW2, 0).Value

Do While currentCellVal < ""

If currentCellVal = searchstring Then

w1.Sheets(tabname1).Range("a1").Offset(currentPosi tionW1, 0).Value =
"Auto"
counter = counter + 1
End If
currentPositionW2 = currentPositionW2 + 1
currentCellVal =
w2.Sheets(tabname2).Range("a2").Offset(currentPosi tionW2, 0).Value
Loop
currentPositionW1 = currentPositionW1 + 1
Next

MsgBox ("" & counter & " Items Set as AutoSource")

End Sub


I guess no one has any ideas?