Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific values in three columns of a ws
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific values in three columns of a ws
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 "BigPig" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific values in three columns of a ws
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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find specific values in three columns of a ws
Hi Joel,
That's exactly what I was looking for. Thank you very much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking along a row to find specific values | Excel Discussion (Misc queries) | |||
find a specific or several values in a massive datasheet | Excel Worksheet Functions | |||
Adding values in columns with a specific indicator | Excel Worksheet Functions | |||
What function will find and multiply specific values in excel? | Excel Worksheet Functions | |||
How can i find specific values along a trendline? | Charts and Charting in Excel |