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.

--
Rick (MVP - Excel)


"Juan Correa" <(E-Mail Removed)> wrote in message
news:1E2C1A99-346E-4709-BF06-(E-Mail Removed)...
> 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

Try:

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

Tom

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" <(E-Mail Removed)> wrote in message
> news:1E2C1A99-346E-4709-BF06-(E-Mail Removed)...
> > 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 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" <(E-Mail Removed)> wrote in message
news:FC3CD7CB-BA3E-4AE8-B8A7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:1E2C1A99-346E-4709-BF06-(E-Mail Removed)...
>> > 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
>>
>> .
>>