Check if value does not exist in range.

  • Thread starter Thread starter Juan Correa
  • Start date Start date
J

Juan Correa

Hello

I have a small question for the gurus here:

I have a spreadsheet with Data. The data is stored in columns A through P
with column labels in row 1. So my column lables are A1:P1

I know how to use:
Cells.Find(What:="SomeColumnLable").Column
To determine which column any particular bit of information is located at.

My question is:
Is there a way that I can use the Cells.Find to check if a column label does
not exist in the range?

Thanks
JC
 
You might find a call to this function simpler to use within your own
code...

Function IsColumnLabel(Lbl As String, Addr As String) As Boolean
Dim R As Range
On Error Resume Next
Set R = Range(Addr).Find(What:=Lbl, LookAt:=xlWhole, MatchCase:=False)
IsColumnLabel = Not R Is Nothing
End Function

Just pass the function the label text and the range address as a string. For
example...

MsgBox IsColumnLabel("SomeColumnLabel", "A1:P1")

and it will respond True if the label text exists within the specified range
and False otherwise.
 
Thank you all for your responses.

Joel's approach is the one that works best for my particular project where I
need to check for the non-existance of a particular label and execute code
based on that. So Joel gets the check-mark this time.

Thanks again
JC
 
You might want to consider substituting your exact range (A1:P1) in place of
joel's more general Cells reference just in case your last use of Find
searched by columns instead of by rows; otherwise the Find function will
search every cell in each column until it either finds the label text or
runs out of columns to search. Alternately, you can specify the xlByRows
parameter of the XlSearchOrder argument in joel's function call to make sure
it searches Row 1 first, although if the label is not there, joel's function
call will still do more work than if you specify the range directly as the
search would go through every cell on Row 1 until it runs out of columns.
 
Back
Top