Is the Activecell within a range
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Is the Activecell within a range

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi! I'm new to the list so I hope I do things correctly. If not please bare with me. I have searched the archives but I could only find similar questions and not the answers. I must be doing something wrong.

    I would like to programmatically find out if the activecell is within a certain range and if it is I would like other code to execute. Here is the code that I have now. It is failing in the first 3 lines I believe.

    Dim Actvcel As Range
    Dim WallARange As Range
    Dim Width As Double

    Set Actvcel = ActiveCell
    Set WallARange = Worksheets("Layout").Range("WallA")

    If Actvcel.Parent.Parent.Name = WallARange.Parent.Parent.Name Then
    If Actvcel.Parent.Name = WallARange.Parent.Name Then
    If Union(Actvcel, WallARange).Address = WallARange.Name Then

    If Actvcel.Value = "hwt" Or Actvcel = "hwa" Then
    Width = InputBox("Please enter the width for this HVAC_
    Window", "HVAC Window Width")
    i = ActiveCell.Range("WallARange").Rows.Count
    Worksheets("Formulas").Range("DO2:DO26").Rows_
    (i).Value = Width
    End if
    End if
    End if
    End if

    Many Thanks in Advance

    Keith

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If Not Intersect(ActiveCell,Range("A1:A10")) is Nothing then
    MsgBox "Is in the A1:A10 range"
    else
    MsgBox "Where's this cell ???"
    End If

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Keith
    There is not too much wrong except fro the
    range reference when using Union will refer
    to an array of ranges...wallarange will refer
    to a range referencing your wbook name.

    If I could suggest another method.
    Try this....not too sure of all your requiesites but ....


    Dim Actvcel As Range
    Dim WallARange As Range
    Dim OK As Range
    Dim Width As Double
    Dim i As Integer

    Set Actvcel = ActiveCell
    Set WallARange = Worksheets("Layout").Range("WallA")

    On Error Resume Next
    Set OK = Application.Intersect(Actvcel, WallARange)
    If Err Then Exit Sub 'wrong range/sheet

    If Not OK Is Nothing Then
    If Actvcel.Value = "hwt" Or Actvcel = "hwa" Then
    Width = Application.InputBox("Please enter the width for this HVAC Window", "HVAC Window Width", Type:=1)
    If Width = 0 Then Exit Sub
    i = WallARange.Rows.Count
    Worksheets("Formulas").Range("DO2:DO26").Rows(i).Value = Width
    End If
    End If





    _________________
    Kind Regards,
    Ivan F Moala
    http://<font color="green"><a href="...r Construction

    [ This Message was edited by: Ivan F Moala on 2002-04-12 14:40 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com