Find specific values in three columns of a ws

[BigPig]

Hi All,

A part of a ws has three cells that hold: location, user, and office

The ws also holds a table of location data, which holds columns like
location, user and office. Typlically there is one user for one location, but
that is not always true.

What I am trying to do is find the values listed in the three cells
(location, user and office) in the ws table, and if true "do something like
found it", if false "do something else like didn't find it".

This is what I have so far, but I can't get it to loop through the rest of
the values to find an exact match.

Worksheets(1).Range("h1").ClearContents

Dim loc As String
loc = Worksheets(1).Range("g12").Value
Dim nm As String
nm = Worksheets(1).Range("h12").Value
Dim of As String
of = Worksheets(1).Range("i12").Value

With Worksheets(1)
Dim FoundCell As Range
Set FoundCell = .Range("a1:a500").Find(What:=loc, LookIn:=xlValues)
Dim name As Range
Set name = FoundCell.Offset(0, 1)
Dim off As Range
Set off = FoundCell.Offset(0, 2)

If FoundCell = loc And name = nm And off = of Then
Worksheets(1).Range("h1") = "do something like Found it"
Else
Worksheets(1).Range("h1") = "do something else like Didn't find
it"
End If
End With

Any help or advice would be greatly appreciated. Thanks.

 

[Don Guillett]

This is what I have so far, but I can't get it to loop through the rest of

the values to find an exact match.

Exactly what do you mean? Examples of right/wrong

 

[Joel]

Worksheets(1).Range("h1").ClearContents

Dim loc As String
loc = Worksheets(1).Range("g12").Value
Dim nm As String
nm = Worksheets(1).Range("h12").Value
Dim of As String
of = Worksheets(1).Range("i12").Value

With Worksheets(1)
For RowCount = 1 to 500
if Range("A" & RowCount) = loc then

name = Range("B" & RowCount)
off = Range("C" & RowCount)

If name = nm And off = of Then
Worksheets(1).Range("h1") = "do something like Found it"
Else
Worksheets(1).Range("h1") = "do something else like Didn't
find it"
End If
end if
next RowCount
End With

 

[BigPig]

Hi Joel,

That's exactly what I was looking for. Thank you very much!