Help with formulas in functions

[oomeheadhurts]

Hello, I have very long and complicated formulas dveloping in my cells, and I was wondering if I can write a helper function to handle this.

My stab at the function looks something like

Function lookupMatch(name1 As Variant) As Variant
    Dim rng1 As Range
    rng1.Name = name1
    
    lookupMatch = "=IF(ISERROR(gethyperlink(INDIRECT(""Options!""&ADDRESS(MATCH(INDEX(rng1,B3,1),rng1,0),1,4,TRUE)))),IF(ISBLANK(INDEX(rng1,B3,1)),"""",INDEX(rng1,B3,1)),HYPERLINK(gethyperlink(INDIRECT(""Options!""&ADDRESS(MATCH(INDEX(rng1,B3,1),tblA,0),1,4,TRUE))),IF(ISBLANK(INDEX(rng1,B3,1)),"""",INDEX(rng1,B3,1))))"

End Function

Can anyone tell me what I'm doing wrong cause when I enter

=lookupMatch(table1)

i get a cheeky #value! response from Excel.

 

[Rofeu]

Hi,

I've not delved into your function, but one thing sticks out. The way your incorporated your variables makes your function see them as a string. i.e. rng1. It will take "rng1" in your formula and not the actual value that rng1 has. You need to put them outside of the formula-string: example "INDEX(" & rng1 & ",B3,1)".

What exactly are you trying to accomplish with this formula?
I'm inclined to believe there will be an easier way to do it.

Cheers,

Roel

 

[oomeheadhurts]

Hi Thanks for getting back. OK a little background on the formula. I have a Combo box with various option. Based on the combo box selection, a look-up on a table (from a different worksheet - "Options") is performed, and the appropriate information, including the hyperlink is extracted.

What I have now on the sheet is a number of different such combo boxes, but the formula for looking up the corresponding table is the same.

What I want to do then is to have a function that wraps this formula and takes in only the table name as an input, and returns the corresponding text (and the hyperlink) form the table look-up.

I've only started looking at the VBA option yesterday so I wouldn't be surprised if what I posted earlier was complete garbage.

The exact formula currently being used (without the funny double double quote) is

=IF(ISERROR(gethyperlink(INDIRECT("Options!"&ADDRESS(MATCH(INDEX(caseA,B3,1),caseA,0),1,4,TRUE)))),IF(ISBLANK(INDEX(caseA,B3,1)),"",INDEX(caseA,B3,1)),HYPERLINK(gethyperlink(INDIRECT("Options!"&ADDRESS(MATCH(INDEX(caseA,B3,1),caseA,0),1,4,TRUE))),IF(ISBLANK(INDEX(caseA,B3,1)),"",INDEX(caseA,B3,1))))

 

[firefytr]

Hmm, why the cross-post?

..

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1152797646

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[firefytr]

.. aahh .. hit the button too early..

".. or is that you?"

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[oomeheadhurts]

Ah thats where I signed up!

Computing services did a patch install overnight and on reboot lost my bookmarks. Well now, its not exactly a cross post - that's a different issue to this one. In any case thanks for posting the other link.

 

[Rofeu]

Hi,

I had some time to look into this. First of all (I completely overlooked this earlier):

you will never get the result of your formula this way. The cell value will be the formula itself.

Another thing:

where does the value of B3 come from?
(as far as I can determine: MATCH(INDEX(caseA,B3,1),caseA,0) = B3)

Anyway, give the following a try. I think it does what you want and if not exactly, you have a good starting point:

Function LookupMatch(name1 As Variant) As String

Application.Volatile

Dim LinkAddress As String
Dim FoundRange As Range

Set FoundRange = Sheets("Options").Cells(WorksheetFunction.Match(Cells(3, 2).Value, Sheets("Options").Range(name1.Name), False), 1)

On Error Resume Next
LinkAddress = FoundRange.Hyperlinks.Item(1).Address
On Error GoTo 0

If Not LinkAddress = vbNullString Then
    Application.Caller.Parent.Hyperlinks.Add Application.Caller, LinkAddress
Else
    Application.Caller.Hyperlinks.Delete
End If

LookupMatch = FoundRange.Value

Set FoundRange = Nothing

End Function

Cheers,

Roel

 

[oomeheadhurts]

Hi Rofeu! That almost did the trick. the only problem was that FoundRange was assuming that the source table started from A1 which it doesn't So it was picking up the wrong option. I've modified it to work but still have some issues. The updated code is below. The issue is that the code works so long as the source worksheet ("Options") and the data are not hidden. This kind of defeats the purpose as I want to keep that worksheet hidden due to sensetive details not being offered.


The B3 you asked is where the combo box with the options and I have included this as part of the input parameters now.

Function findOption(sourceTable As Variant, comboSelection As Range) As String

Application.Volatile

Dim LookUpIdx As Long
Dim LookUpArray As Range
Dim LookupString As String
Dim FoundMatchCell As Range
Dim FoundRange As Range
Dim LinkAddress As String

LookUpIdx = Sheets("Specs").Cells(comboSelection.Row, comboSelection.Column).Value
Set LookUpArray = Sheets("Options").Range(sourceTable.Name)

LookupString = WorksheetFunction.Index(sourceTable, LookUpIdx, 0)

Set FoundMatchCell = LookUpArray.Find(LookupString, LookIn:=xlValues)
Set FoundRange = Sheets("Options").Cells(FoundMatchCell.Row, FoundMatchCell.Column)

On Error Resume Next
LinkAddress = FoundRange.Hyperlinks.Item(1).Address
On Error GoTo 0

If Not LinkAddress = vbNullString Then
    Application.Caller.Parent.Hyperlinks.Add Application.Caller, LinkAddress
Else
    Application.Caller.Hyperlinks.Delete
End If

findOption= FoundRange.Value

Set FoundRange = Nothing

End Function

Typical usage would be =findOption(caseA,B3)

 

[Rofeu]

Hmm, I tried my code and it doesn't matter if the sheet is hidden or not. The only piece of code that might require a unhidden sheet is the Find. You might want to try to get the FoundMatchCell by using worksheetfunctions.

Cheers,

Roel