Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if value in list
I have a column in a hidden worksheet that has a bunch of values
vertically downward. Say I have a variable that stores a single value. I want to see if that value exists in that column in another worksheet. How would I do this in VBA? So if Worksheet "Sheet2" has in Column "G" the values vertically JOE, SAM, PETE, MIKE, BILL, SUE (this list could change, grow, shrink) and I have a variable that has "JIM" in it, I want to see if it's in Column "G" returning whether it is or not. Kind of like If UCase(VariableName) in "JOE,SAM,PETE,MIKE,BILL,SUE" then .... Thanks. JR |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if value in list
Dim res as Variant
res = Application.Match(VariableName,worksheets("Sheet2" ).Range("G:G"),0) if not iserror(res) then msgbox "Match found" Else msgbox "Match not found End if You can also use find set rng = Worksheets("Sheet2").Range("G:G").Find( _ What:=VariableName) if not rng is nothing then msgbox "Match found" else msgbox "Match Not found" End if -- Regards, Tom Ogilvy "John" wrote in message m... I have a column in a hidden worksheet that has a bunch of values vertically downward. Say I have a variable that stores a single value. I want to see if that value exists in that column in another worksheet. How would I do this in VBA? So if Worksheet "Sheet2" has in Column "G" the values vertically JOE, SAM, PETE, MIKE, BILL, SUE (this list could change, grow, shrink) and I have a variable that has "JIM" in it, I want to see if it's in Column "G" returning whether it is or not. Kind of like If UCase(VariableName) in "JOE,SAM,PETE,MIKE,BILL,SUE" then ... Thanks. JR |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if value in list
Thanks Tom. The samples below work good. If I wanted to use the Find
method, not sure if using Application. to use macro functionality is good or bad, however is there a way to force a find match on the enter word? So if one of the cells in "G" have "sam" and the VariableName is "sa", I do not want it to match. It should only match if there is an exact match. Won't really be an issue for what I'm doing, however like to make sure things do not run astray for whatever reason. Thanks. JR "Tom Ogilvy" wrote in message ... Dim res as Variant res = Application.Match(VariableName,worksheets("Sheet2" ).Range("G:G"),0) if not iserror(res) then msgbox "Match found" Else msgbox "Match not found End if You can also use find set rng = Worksheets("Sheet2").Range("G:G").Find( _ What:=VariableName) if not rng is nothing then msgbox "Match found" else msgbox "Match Not found" End if -- Regards, Tom Ogilvy "John" wrote in message m... I have a column in a hidden worksheet that has a bunch of values vertically downward. Say I have a variable that stores a single value. I want to see if that value exists in that column in another worksheet. How would I do this in VBA? So if Worksheet "Sheet2" has in Column "G" the values vertically JOE, SAM, PETE, MIKE, BILL, SUE (this list could change, grow, shrink) and I have a variable that has "JIM" in it, I want to see if it's in Column "G" returning whether it is or not. Kind of like If UCase(VariableName) in "JOE,SAM,PETE,MIKE,BILL,SUE" then ... Thanks. JR |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if value in list
John
If I have understood you correctly, then add the following to Tom's code: set rng = Worksheets("Sheet2").Range("G:G").Find( _ What:=VariableName, LookIn:=XlValues, _ LookAt:=xlWhole, MatchCase:=False) if not rng is nothing then msgbox "Match found" else msgbox "Match Not found" End if -- Best Regards Leo Heuser Followup to newsgroup only please. "John" skrev i en meddelelse om... Thanks Tom. The samples below work good. If I wanted to use the Find method, not sure if using Application. to use macro functionality is good or bad, however is there a way to force a find match on the enter word? So if one of the cells in "G" have "sam" and the VariableName is "sa", I do not want it to match. It should only match if there is an exact match. Won't really be an issue for what I'm doing, however like to make sure things do not run astray for whatever reason. Thanks. JR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check to see if a value exists in a list? | Excel Worksheet Functions | |||
DROPDOWN LIST AND CHECK BOX | Excel Discussion (Misc queries) | |||
Combo/List Box and Check Box | Excel Discussion (Misc queries) | |||
Check 2 different list get associated value from a column | Excel Worksheet Functions | |||
check numbers in a list | Excel Discussion (Misc queries) |