Hello, i am needing some suggesting and code examples. I have a list in
sheet 2 with 3 columns. (desc, desc2, and desc3). I would like to take
my string to search in sheet 1 [Cells(x,13).value] look for the match
in sheet 2 list, however it could be found in either 1st or 2nd column.
if it's found in column 2, i want to use the value in column 1 to be
the replacement in sheet 1 [Cells(x,13).value], if it's found in column
1 then just leave it alone. Also, there might be a 'blank' value in
[cells(x,13)], if so i just want to leave it alone, or actually want to
add the code:[Cells(i, 13).Interior.ColorIndex = 6].

I was thinking of another solution in the post Loops, ranges and
vlookup... but, i think if i just change the value to the primary or
[column 1] of the list in sheet 2, then i can use the vlookup easily.
I think that would be easier to accomplish than the previous post.

Any suggestions would be most helpful.


Keri~

This is the solution i got to work....
'////////////////////////////////////////////////////////////////
Dim x, FinalRow, rw As Long
Dim w As Worksheet
Dim s, r
Dim rng As Range
FinalRow = Cells(Rows.Count, 4).End(xlUp).Row
rw = Sheets("Project").Cells(65500, 1).End(xlUp).Row
Set w = Sheets("Project")
Dim MyColumn As String, Here As String
Set rng = w.Range("A2:B" & rw)
For x = 2 To FinalRow
Cells(x, 6).Select
s = Cells(x, 4).Value
If Cells(x, 6).Value = "" Then
For Each r In rng
If r.Value = s Then
'Cells(x, 6).Value = r.Value
Cells(x, 7).Value = r.Address
Cells(x, 6).Formula = "=VLOOKUP(" & Cells(x, 4).Address &
_
", ProjectList,3,True)"
Here = r.Address
MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here,
"$") - 2)
If MyColumn = "B" Then
'MsgBox "my column = B"
Cells(x, 4).Clear
Cells(x, 4).Value = r.Offset(0, -1).Value
Cells(x, 6).Formula = "=VLOOKUP(" & Cells(x, 4).Address
& _
", ProjectList,3,True)"
End If
Exit For
End If
Next r
End If
Next x

'///////////////////////////////////////////////////////////////