Check if value does not exist in range.
 

Hello

I have a small question for the gurus he

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

Check if value does not exist in range.
 

'I removed the column at the end
Set c = Cells.Find(What:="SomeColumnLable")
if c is nothing then
'enter your code here
else
column_Number = c.column
end if


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=186470

Excel Live Chat

Check if value does not exist in range.
 

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.

--
Rick (MVP - Excel)


"Juan Correa" wrote in message
...
Hello

I have a small question for the gurus he

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

Check if value does not exist in range.
 

Try:

Cells.Find(What:="").Column

Tom

Check if value does not exist in range.
 

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Juan Correa" wrote in message
...
Hello

I have a small question for the gurus he

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

.

Check if value does not exist in range.
 

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.

--
Rick (MVP - Excel)


"Juan Correa" wrote in message
...
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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Juan Correa" wrote in message
...
Hello

I have a small question for the gurus he

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

.