Selection in data range

I have a data range called AssayDescList that is imported from a database in
column C. How can I programatically check if the ActiveCell is placed within
that data range?

Here is what I came up with so far:

Sub AssayListInsert()
Dim Row As Integer, Cell As Range, AssayDescList As Name
Row = 7
Do While Not Range("B" & Row) = ""
Row = Row + 1
Loop
For Each Cell In Range(AssayDescList)
If Cell.Address = ActiveCell.Address Then
Range("B" & Row).Value = Left(ActiveCell, 4)
Exit Sub
End If
Next
End Sub

Can anyone help me correct this or even suggest an easier way?

Thanks
The Doctor

Hello, Doc;
Obviously the loop to search the cells will take some time; here is another
way using the Intersect method to see if the activecell and the data range
overlap:

Sub AssayListInsert()
Dim Row As Integer, InRange As Range, AssayDescList As Name
Row = 7
Do While Not Range("B" & Row) = ""
Row = Row + 1
Loop

' New part below:
Set InRange = Intersect(ActiveCell, Range(AssayDescList))
If Not(InRange Is Nothing) Then Range("B" & Row).Value =
Left(ActiveCell, 4)

End Sub

"I'm not a doctor, but I play one on TV"
K Dales

"Dr.Schwartz" wrote:

> I have a data range called AssayDescList that is imported from a database in
> column C. How can I programatically check if the ActiveCell is placed within
> that data range?
>
> Here is what I came up with so far:
>
> Sub AssayListInsert()
> Dim Row As Integer, Cell As Range, AssayDescList As Name
> Row = 7
> Do While Not Range("B" & Row) = ""
> Row = Row + 1
> Loop
> For Each Cell In Range(AssayDescList)
> If Cell.Address = ActiveCell.Address Then
> Range("B" & Row).Value = Left(ActiveCell, 4)
> Exit Sub
> End If
> Next
> End Sub
>
> Can anyone help me correct this or even suggest an easier way?
>
> Thanks
> The Doctor
>

One way: You can use the Intersect operator.
If Not Intersect(ActiveCell, Range(AssayDescList)) Is Nothing returns True
if the ActiveCell is within that range. Your code would look like this:

If Not Intersect(ActiveCell, Range(AssayDescList)) Is Nothing Then
'Code if ActiveCell is in the range
Else
'Code if it isn't in the range
End If

If the Intersect operator is not available in your older version of Excel,
then you might look at some of these things:
Range(AssayDescList)(1).Row is the first row of the range.
Range(AssayDescList)( Range(AssayDescList).Count.Row is the row of the last
cell of the range.
HTH Otto
"Dr.Schwartz" <[email protected]> wrote in message
news:[email protected]...
>I have a data range called AssayDescList that is imported from a database
>in
> column C. How can I programatically check if the ActiveCell is placed
> within
> that data range?
>
> Here is what I came up with so far:
>
> Sub AssayListInsert()
> Dim Row As Integer, Cell As Range, AssayDescList As Name
> Row = 7
> Do While Not Range("B" & Row) = ""
> Row = Row + 1
> Loop
> For Each Cell In Range(AssayDescList)
> If Cell.Address = ActiveCell.Address Then
> Range("B" & Row).Value = Left(ActiveCell, 4)
> Exit Sub
> End If
> Next
> End Sub
>
> Can anyone help me correct this or even suggest an easier way?
>
> Thanks
> The Doctor
>

I'm sorry, but I get an error 400 in the Set InRange = Intersect(ActiveCell,
Range(AssayDescList)) line.

Any ideas?


"K Dales" wrote:

> Hello, Doc;
> Obviously the loop to search the cells will take some time; here is another
> way using the Intersect method to see if the activecell and the data range
> overlap:
>
> Sub AssayListInsert()
> Dim Row As Integer, InRange As Range, AssayDescList As Name
> Row = 7
> Do While Not Range("B" & Row) = ""
> Row = Row + 1
> Loop
>
> ' New part below:
> Set InRange = Intersect(ActiveCell, Range(AssayDescList))
> If Not(InRange Is Nothing) Then Range("B" & Row).Value =
> Left(ActiveCell, 4)
>
> End Sub
>
> "I'm not a doctor, but I play one on TV"
> K Dales
>
> "Dr.Schwartz" wrote:
>
> > I have a data range called AssayDescList that is imported from a database in
> > column C. How can I programatically check if the ActiveCell is placed within
> > that data range?
> >
> > Here is what I came up with so far:
> >
> > Sub AssayListInsert()
> > Dim Row As Integer, Cell As Range, AssayDescList As Name
> > Row = 7
> > Do While Not Range("B" & Row) = ""
> > Row = Row + 1
> > Loop
> > For Each Cell In Range(AssayDescList)
> > If Cell.Address = ActiveCell.Address Then
> > Range("B" & Row).Value = Left(ActiveCell, 4)
> > Exit Sub
> > End If
> > Next
> > End Sub
> >
> > Can anyone help me correct this or even suggest an easier way?
> >
> > Thanks
> > The Doctor
> >

Is the active cell on a different sheet than the data range? That would
cause an error with the intersect method. If that is the issue, then you may
need to test the sheet that ActiveCell is on before running the other code;
e.g.

If ActiveSheet.Name = "MyDataSheet" Then...

"Dr.Schwartz" wrote:

> I'm sorry, but I get an error 400 in the Set InRange = Intersect(ActiveCell,
> Range(AssayDescList)) line.
>
> Any ideas?
>
>
> "K Dales" wrote:
>
> > Hello, Doc;
> > Obviously the loop to search the cells will take some time; here is another
> > way using the Intersect method to see if the activecell and the data range
> > overlap:
> >
> > Sub AssayListInsert()
> > Dim Row As Integer, InRange As Range, AssayDescList As Name
> > Row = 7
> > Do While Not Range("B" & Row) = ""
> > Row = Row + 1
> > Loop
> >
> > ' New part below:
> > Set InRange = Intersect(ActiveCell, Range(AssayDescList))
> > If Not(InRange Is Nothing) Then Range("B" & Row).Value =
> > Left(ActiveCell, 4)
> >
> > End Sub
> >
> > "I'm not a doctor, but I play one on TV"
> > K Dales
> >
> > "Dr.Schwartz" wrote:
> >
> > > I have a data range called AssayDescList that is imported from a database in
> > > column C. How can I programatically check if the ActiveCell is placed within
> > > that data range?
> > >
> > > Here is what I came up with so far:
> > >
> > > Sub AssayListInsert()
> > > Dim Row As Integer, Cell As Range, AssayDescList As Name
> > > Row = 7
> > > Do While Not Range("B" & Row) = ""
> > > Row = Row + 1
> > > Loop
> > > For Each Cell In Range(AssayDescList)
> > > If Cell.Address = ActiveCell.Address Then
> > > Range("B" & Row).Value = Left(ActiveCell, 4)
> > > Exit Sub
> > > End If
> > > Next
> > > End Sub
> > >
> > > Can anyone help me correct this or even suggest an easier way?
> > >
> > > Thanks
> > > The Doctor
> > >

No, I figured it out myself. The named range needed to be written like this:

Set InRange = Intersect(ActiveCell, Range("AssayDescList"))

Now it works beatifully!
Thanks/Doc

"K Dales" wrote:

> Is the active cell on a different sheet than the data range? That would
> cause an error with the intersect method. If that is the issue, then you may
> need to test the sheet that ActiveCell is on before running the other code;
> e.g.
>
> If ActiveSheet.Name = "MyDataSheet" Then...
>
> "Dr.Schwartz" wrote:
>
> > I'm sorry, but I get an error 400 in the Set InRange = Intersect(ActiveCell,
> > Range(AssayDescList)) line.
> >
> > Any ideas?
> >
> >
> > "K Dales" wrote:
> >
> > > Hello, Doc;
> > > Obviously the loop to search the cells will take some time; here is another
> > > way using the Intersect method to see if the activecell and the data range
> > > overlap:
> > >
> > > Sub AssayListInsert()
> > > Dim Row As Integer, InRange As Range, AssayDescList As Name
> > > Row = 7
> > > Do While Not Range("B" & Row) = ""
> > > Row = Row + 1
> > > Loop
> > >
> > > ' New part below:
> > > Set InRange = Intersect(ActiveCell, Range(AssayDescList))
> > > If Not(InRange Is Nothing) Then Range("B" & Row).Value =
> > > Left(ActiveCell, 4)
> > >
> > > End Sub
> > >
> > > "I'm not a doctor, but I play one on TV"
> > > K Dales
> > >
> > > "Dr.Schwartz" wrote:
> > >
> > > > I have a data range called AssayDescList that is imported from a database in
> > > > column C. How can I programatically check if the ActiveCell is placed within
> > > > that data range?
> > > >
> > > > Here is what I came up with so far:
> > > >
> > > > Sub AssayListInsert()
> > > > Dim Row As Integer, Cell As Range, AssayDescList As Name
> > > > Row = 7
> > > > Do While Not Range("B" & Row) = ""
> > > > Row = Row + 1
> > > > Loop
> > > > For Each Cell In Range(AssayDescList)
> > > > If Cell.Address = ActiveCell.Address Then
> > > > Range("B" & Row).Value = Left(ActiveCell, 4)
> > > > Exit Sub
> > > > End If
> > > > Next
> > > > End Sub
> > > >
> > > > Can anyone help me correct this or even suggest an easier way?
> > > >
> > > > Thanks
> > > > The Doctor
> > > >