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

S

snowbrdpaint

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(currentPositionW1, 0).Value
If searchstring = "" Then Exit For
currentPositionW2 = 1
currentCellVal =
w2.Sheets(tabname2).Range("a1").Offset(currentPositionW2, 0).Value


Do While currentCellVal <> ""


If currentCellVal = searchstring Then

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


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

End Sub
 
S

snowbrdpaint

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(currentPositionW1, 0).Value
If searchstring = "" Then Exit For
currentPositionW2 = 1
currentCellVal =
w2.Sheets(tabname2).Range("a1").Offset(currentPositionW2, 0).Value

Do While currentCellVal <> ""

If currentCellVal = searchstring Then

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

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

End Sub
Click to expand...


I guess no one has any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find string from column A in Range("B2:F7") list the header of that column/s 5
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
Getting data from a closed wbook (adaptation of rondebruin's) 4
for every cell search identical in range, not whole column; how? 2
search a row for data then move to next row 4
copy data from a hidden column to another sheet 1
Formatting data based on a cell range 4
Importing Data to Excel 2007 from Access 2007 5

Top