Pass Target.Address to Function to see if it falls within a range

[Nathan_S]

I apologize for my noobness with VBA upfront. I'm attempting to teach myself VBA while adding some code to a project at work. All help is greatly appreciated.

I'm creating a form that will be filled out by others that may not be as familiar with the material as I am. I'm attempting to code in pop-up messages that can help guide users through the process. Many questions are dependent upon other questions. For example, if question 1.1 is answered with a "No" then questions 1.1.1 through 1.1.4 (4 questions) will all get auto-filled with "N/A".

If someone attempts to click on question 1.1.1 and change the answer from "N/A" to something else, I want to pop up a message that prompts them to first change question 1.1. There are a lot of different pieces to this puzzle that I'm working on, but the part I'm needing help with is being able to pass Target.Address to a function to then return the address for the main question. I have about a 120 questions I'm dealing with and about 40 questions with sub-questions. So, I'm attempting to create a function that will evaluate if the value someone is attempting to change is for a sub-question and if it is, I want it to return the main question address that is related to the sub-question. I'll use that address in the pop-up message. I'm working on proof-of-concept at this point with question 1.1 (answered at $K$8)

Here's what I have, but it gives me a Run-time error '424': Object required error on line 2:

Private Function RangeFinder(ByVal RangeID As Variant) As Variant
    If Not Application.Intersect(RangeID, Range("K8:K12")) Is Nothing Then
        Set RangeFinder = Range("$K$8") 
    Else
        Set RangeFinder = Range("$Z$1")  'This is just for testing purposes
    End If
    MsgBox RangeFinder   'This is just for testing purposes
End Function

I'm calling the function from a change procedure with the following:

RangeFinder (Target.Address)

 

[MoshiM]

I'm calling the function from a change procedure with the following:

RangeFinder (Target.Address)

You're supplying a string with this not a range.

Either supply a range ( ie: Target )or use range(RangeID) in your intersect function.

 

[Nathan_S]

When I tried passing RangeFinder (Target), I got the same error.

When I changed it to Range(RangeID) as seen below, it produced a different result than what I was looking for. Instead of returning the address "$K$8" it returned "No", which is the value that is stored at that address location.

Private Function RangeFinder(ByVal RangeID As Variant) As Variant
    If Not Intersect(Range(RangeID), Range("K8:K12")) Is Nothing Then
        Set RangeFinder = Range("$K$8")
    Else
        Set RangeFinder = Range("$Z$1")
    End If
    MsgBox RangeFinder
End Function

How do I return the address location instead of the value? I know I could return a string value with the address, but I want to set the RangeFinder variable with the address location of "$K$8". How do I do that?

 

[MoshiM]

How do I return the address location instead of the value? I know I could return a string value with the address, but I want to set the RangeFinder variable with the address location of "$K$8". How do I do that?

This is for passing target as a range into the function. Message box will show the cell address of rangefinder. Also the dollar signs aren't necessary.

Private Function RangeFinder(ByVal RangeID As range) As Variant

    If Not Intersect(RangeID, Range("K8:K12")) Is Nothing Then
        Set RangeFinder = Range("K8")
    Else
        Set RangeFinder = Range("Z1")
    End If
    MsgBox RangeFinder.address

End Function

 

[Nathan_S]

Thanks.