Custom Worksheet Function - Absolute...?

I have created the following worksheet function. 1) I would like to make the
"Table_Array" an absolute addressing as the default, regardless of what is
typed in and 2) if possible, allow it to be editted to relative addressing if
desired.

Public Function iiVlookup(Lookup_Value, Table_Array As Range, _
Col_Index_Num As Integer, Optional Range_Lookup As Boolean = False, Optional
IsErrorValue = "")

' Uses the vlookup function but returns a user defined value (IsErrorValue)
if the vlookup results in an error message.

iiVlookup = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_Lookup)


If IsError(IfIserrorVlookup) Then

iiVlookup = IsErrorValue

Else

iiVlookup = iiVlookup

End If

End Function

Not sure what you mean by absolute and relative addressing in
Table_Array.

Absolute and Relative only really apply to how the range is specified
when it is called from the worksheet. A range itself doesn't have any
Absolute or Relative property.

If you mean that you want to have a default table (say "Sheet2!A:x"),
you could use something like

Public Function iiVlookup( _
ByVal Lookup_Value As Variant, _
ByRef Table_Array As Variant, _
ByVal Col_Index_Num As Integer, _
Optional ByVal Range_Lookup As Boolean = False, _
Optional ByVal IsErrorValue = "") As Variant

' Uses the vlookup function but returns a user defined value
' (IsErrorValue) if the vlookup results in an error message.

If Not TypeOf Table_Array Is Range Then _
Set Table_Array = ThisWorkbook.Sheets( _
"Sheet2").Columns(1).Resize(, Col_Index_Num)
iiVlookup = Application.VLookup( _
Lookup_Value, Table_Array, Col_Index_Num, Range_Lookup)
If IsError(iiVlookup) Then iiVlookup = IsErrorValue
End Function

which allows you to call the function as

=iiVLOOKUP(A1, , 2, FALSE, "Error Value")

to use the default table, or

=iiVLOOKUP(A1, J:K, 2, FALSE, "Error Value")

to override the default table.


If I've completely missed the mark, post back with more information
about what you're looking for.


In article ,
Rebecca_SUNY wrote:

I have created the following worksheet function. 1) I would like to make the
"Table_Array" an absolute addressing as the default, regardless of what is
typed in and 2) if possible, allow it to be editted to relative addressing if
desired.

Public Function iiVlookup(Lookup_Value, Table_Array As Range, _
Col_Index_Num As Integer, Optional Range_Lookup As Boolean = False, Optional
IsErrorValue = "")

' Uses the vlookup function but returns a user defined value (IsErrorValue)
if the vlookup results in an error message.

iiVlookup = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_Lookup)


If IsError(IfIserrorVlookup) Then

iiVlookup = IsErrorValue

Else

iiVlookup = iiVlookup

End If

End Function

It is not a default table. Rather it is in response to coping the formula
after the first input.

If I use the formula as follows in B1

=iiVLOOKUP(A1, E1:F50, 2, FALSE, "")

but then I want to copy down the column from B1 to B2 through B25, the
reference to the table array will change to E2:F51, etc. I want it to stay
the same (absolute) without having to tell it to by using F4 or entering the
$ signs. I was hoping there was a way in the function to change the default
to absolute ($E$1:$F$50) but allow the user to edit the formula to change it
if desired ($E$1:F50).


"JE McGimpsey" wrote:

Not sure what you mean by absolute and relative addressing in
Table_Array.

Absolute and Relative only really apply to how the range is specified
when it is called from the worksheet. A range itself doesn't have any
Absolute or Relative property.

If you mean that you want to have a default table (say "Sheet2!A:x"),
you could use something like

Public Function iiVlookup( _
ByVal Lookup_Value As Variant, _
ByRef Table_Array As Variant, _
ByVal Col_Index_Num As Integer, _
Optional ByVal Range_Lookup As Boolean = False, _
Optional ByVal IsErrorValue = "") As Variant

' Uses the vlookup function but returns a user defined value
' (IsErrorValue) if the vlookup results in an error message.

If Not TypeOf Table_Array Is Range Then _
Set Table_Array = ThisWorkbook.Sheets( _
"Sheet2").Columns(1).Resize(, Col_Index_Num)
iiVlookup = Application.VLookup( _
Lookup_Value, Table_Array, Col_Index_Num, Range_Lookup)
If IsError(iiVlookup) Then iiVlookup = IsErrorValue
End Function

which allows you to call the function as

=iiVLOOKUP(A1, , 2, FALSE, "Error Value")

to use the default table, or

=iiVLOOKUP(A1, J:K, 2, FALSE, "Error Value")

to override the default table.


If I've completely missed the mark, post back with more information
about what you're looking for.


In article ,
Rebecca_SUNY wrote:

I have created the following worksheet function. 1) I would like to make the
"Table_Array" an absolute addressing as the default, regardless of what is
typed in and 2) if possible, allow it to be editted to relative addressing if
desired.

Public Function iiVlookup(Lookup_Value, Table_Array As Range, _
Col_Index_Num As Integer, Optional Range_Lookup As Boolean = False, Optional
IsErrorValue = "")

' Uses the vlookup function but returns a user defined value (IsErrorValue)
if the vlookup results in an error message.

iiVlookup = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_Lookup)


If IsError(IfIserrorVlookup) Then

iiVlookup = IsErrorValue

Else

iiVlookup = iiVlookup

End If

End Function

The function will only see what is passed to it.

So call it like this:

=iiVLOOKUP(A1, $E$1:$F$50, 2, False, "")

In article ,
Rebecca_SUNY wrote:

It is not a default table. Rather it is in response to coping the formula
after the first input.

If I use the formula as follows in B1

=iiVLOOKUP(A1, E1:F50, 2, FALSE, "")

but then I want to copy down the column from B1 to B2 through B25, the
reference to the table array will change to E2:F51, etc. I want it to stay
the same (absolute) without having to tell it to by using F4 or entering the
$ signs. I was hoping there was a way in the function to change the default
to absolute ($E$1:$F$50) but allow the user to edit the formula to change it
if desired ($E$1:F50).