User Defined Funtion - if formula and intersect ranges

[C111]

Hello,

I would like to create a user defined function that will replicate the following logic:

=If (top cell in this column=cell in a named range, "yes","no")

Is this possible? Any ideas?
Thanks!

 

[p45cal]

very rough-and-ready:

Function NameAtTheTop()
NameAtTheTop = "no"
Set bb = Application.Caller.EntireColumn.Cells(1)
For Each nme In ThisWorkbook.Names
  Set rr = nme.RefersToRange
  If rr.Parent Is bb.Parent Then
    If Not Intersect(bb, nme.RefersToRange) Is Nothing Then
      NameAtTheTop = "yes"
      Exit Function
    End If
  End If
Next nme
End Function

used in a worksheet cell thus:
=NameAtTheTop()

 

[C111]

Hi p45cal,

Thank you so much for replying to my message
I am still quite new to vba/functions so I apologize if this may sound really basic...

Let's say I named a range (XX10:XX20) and called it: SURNAME. And if I had the name Charlotte within this range and also in cell A1.

And then I wrote: =NameAtTheTop() in cell A2 (I would hope that in cell A2 the word "no" would appear once I have used this UDF).

In order to make the code you created work, would I need to write the code as follows:

Function NameAtTheTop()
NameAtTheTop = "no"
Set bb = Application.Caller.EntireColumn.Cells(1)
For Each SURNAME In ThisWorkbook.Names
Set rr = SURNAME.RefersToRange
If rr.Parent Is bb.Parent Then
If Not Intersect(bb, SURNAME.RefersToRange) Is Nothing Then
NameAtTheTop = "yes"
Exit Function
End If
End If
Next SURNAME
End Function

And when I write =NameAtTheTop () in cell A2, what would I need to insert into the brackets?
Thanks again for your first reply - I really appreciate your help!
Kind regards,
C111

 

[C111]

hi p45cal,
I received an email to notify me that you updated the thread but I can't see any updates.. am I looking in the wrong place?
Thanks again!
C111

--------------------------------------------------------------------------------------------------------------------

Hi p45cal,

Thank you so much for replying to my message
I am still quite new to <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym>/functions so I apologize if this may sound really basic...

Let's say I named a range (XX10:XX20) and called it: SURNAME. And if I had the name Charlotte within this range and also in cell A1.

And then I wrote: =NameAtTheTop() in cell A2 (I would hope that in cell A2 the word "no" would appear once I have used this UDF).

In order to make the code you created work, would I need to write the code as follows:

Function NameAtTheTop()
NameAtTheTop = "no"
Set bb = Application.Caller.EntireColumn.Cells(1)
For Each SURNAME In ThisWorkbook.Names
Set rr = SURNAME.RefersToRange
If rr.Parent Is bb.Parent Then
If Not Intersect(bb, SURNAME.RefersToRange) Is Nothing Then
NameAtTheTop = "yes"
Exit Function
End If
End If
Next SURNAME
End Function

And when I write =NameAtTheTop () in cell A2, what would I need to insert into the brackets?
Thanks again for your first reply - I really appreciate your help!
Kind regards,
C111

 

[Marcelo Branco]

Hi,

You can use a named formula, no need of VBA

Try this

Select A2

Formulas > Name Manager > New

type in Name
NameAtTheTop

paste the formula below in Refers to:
=IF(COUNTIF(SURNAME,INDIRECT("R1C",0)),"No","Yes")

Ok

Then enter in any cell (not located at row 1)

=NameAtTheTop

Hope this is what you need.

M.

 

[p45cal]

hi p45cal,
I received an email to notify me that you updated the thread but I can't see any updates.. am I looking in the wrong place?
Thanks again!
C111

It will have been a spelling correction or some such. The board allows a few minutes to make corrections after posting a message. You probably never saw the first version.

Let's say I named a range (XX10:XX20) and called it: SURNAME. And if I had the name Charlotte within this range and also in cell A1.

And then I wrote: =NameAtTheTop() in cell A2 (I would hope that in cell A2 the word "no" would appear once I have used this UDF).

It becomes obvious that I interpreted your request exactly as written, but not as meant!

=If (top cell in this column=cell in a named range, "yes","no")

I took to mean that if the cell (the cell mind, not its value) at the very top of the column you wrote the formula in was a cell that was included in any Named Range (your SURNAME named range is one example of such) then it would return "yes".
You want to know if the Value in the cell at the top of the column you write the formula in is to be found in any of the cells in a given named range - is that right?
Do you really need a user-defined function for this? If so post back but a formula can do this:
=IF(COUNTIF(SURNAME,INDIRECT(ADDRESS(1,COLUMN()))) > 0,"yes","no")

This formula is quite restricting in that it only uses the topmost cell of the column the formula is written in.
A simpler formula would give you (a) more freedom to choose which cell to check and (b) you wouldn't have to have the formula in the same column!:
=IF(COUNTIF(SURNAME,F1) > 0,"yes","no")
where F1 was the cell being checked.

 

[C111]

Hello,

Thank you very much for your quick reply! The logic in the formula below is exactly what I need:

=IF(COUNTIF(SURNAME,INDIRECT(ADDRESS(1,COLUMN()))) > 0,"yes","no")

Sorry if my original request was unclear
I hope that you have a great day! Thank you for your help.