Using named range in user defined function

Factotum

Board Regular
Joined
May 14, 2015
Messages
118
I've run into a dead-end on this one, and I'm quite a novice when it comes to VBA. I've got a user defined function which is essentially a vlookup, but it returns cell comments also. I got this function from someone on this board a long time ago, but I'm sorry I don't know who to give the credit to. Anyway, the function is below:

Code:
Option Explicit

Function VlookupComment(LookupValue As Variant, LookupTable As Range, _
    ColumnNumber As Long, MatchType As Boolean) As Variant
    Application.Volatile True
    Dim res As Variant 'could be an error
    Dim myLookupCell As Range
    res = Application.Match(LookupValue, LookupTable.Columns(1), MatchType)
    If IsError(res) Then
        VlookupComment = "Not Found"
    Else
        Set myLookupCell = LookupTable.Columns(ColumnNumber).Cells(1)(res)
        VlookupComment = myLookupCell.Value
        With Application.Caller
            If .Comment Is Nothing Then
                 'do nothing
            Else
                .Comment.Delete
            End If
            If myLookupCell.Comment Is Nothing Then
                 'no comment, do nothing
            Else
                .AddComment Text:=myLookupCell.Comment.Text
            End If
        End With
    End If
End Function

I'm trying to use named ranges in this function as in:
Code:
 ActiveCell.FormulaR1C1 = "=VLOOKUPCOMMENT(NamedRange1,NamedRange2,2,FALSE)"
This method works great when using a regular vlookup function, but not with the user defined function. My end goal is to pull user input from one spreadsheet to a new, updated spreadsheet, I'm trying to make it somewhat user-proof so it can deal with a user adding or deleting columns.

I have considered using a loop along the lines of:
Code:
dim c as range

for each c in range ("NamedRange1")
      c.FormulaR1C1 = "=VlookupComment(first cell of NamedRange1 +1,first cell of NamedRange2+1,2,false) 
next

Each time through the loop would take the next cell in the named ranges, but I don't know how to make this happen. Any suggestions on how to make this UDF work with named ranges? Thanks in advance!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,224,550
Messages
6,179,458
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top