Is the Activecell within a range

[KeithGrindle]

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

[Juan Pablo González]

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

[Ivan F Moala]

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 ]