+ Reply to Thread
Results 1 to 6 of 6

Selection in data range

  1. #1
    Dr.Schwartz
    Guest

    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


  2. #2
    K Dales
    Guest

    RE: Selection in data range

    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
    >


  3. #3
    Otto Moehrbach
    Guest

    Re: Selection in data range

    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
    >




  4. #4
    Dr.Schwartz
    Guest

    RE: Selection in data range

    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
    > >


  5. #5
    K Dales
    Guest

    RE: Selection in data range

    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
    > > >


  6. #6
    Dr.Schwartz
    Guest

    RE: Selection in data range

    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
    > > > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1