Create function to find a value in an array in seperate worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jag - 30 May 2008 04:09 GMT

I am looking to create a VB fuction to use a stock reference in a cell
(A) from one workbook, to reference the correct column of information
corresponding to cell A and return the contents of the cell based its
3rd element and the 4th element.
Example:
Sheet1
A
B C
IBM
1/10 112.34

=ATMSTRIKE(A1,B1,C1) would return from the "B" column in Sheet2 "WIB
US 1/10 C110 Equity" by looking up 3 things 1) IBM,
2) the "1/10" as the 3rd element
3) and the smallest absolute value of the difference between
the number in the 4th element and number in C1
which is 112.34

Sheet2
A
B C
dell    ibm    msft
DLQ US 11 P22 Equity IBM US 6 C75 Equity    MSQ US 6 C24
Equity
DLQ US 11 P23 Equity IBM US 6 C80 Equity    MSQ US 6 C25
Equity
VPZ US 1 C10 Equity    IBM US 7 P90 Equity    MSQ US 7
C22.50 Equity
VPZ US 1 C15 Equity    IBM US 10 P90 Equity    MSQ US 7
C24 Equity
VPZ US 1 C17.50 Equity IBM US 1 P110 Equity    MSQ US 1 C40
Equity
WDQ US 1/10 C17.50 Equity IBM US 1 P115 Equity    MSQ US 1 C42.50
Equity
WDQ US 1/10 C20 Equity WIB US 1/10 C100 Equity WMF US 1/10
C35 Equity
WDQ US 1/10 C25 Equity WIB US 1/10 C110 Equity WMF US 1/10
C37.50 Equity
    WIB US 1/10 C120 Equity WMF US 1/10
C40 Equity
    WIB US 1/10 C125 Equity

I would assume you would use multiple functions to create one such as:

Function ExtractElement (txt,n,Seperator)
Dim AllElements As Variant
AllElements = Split(txt,Seperator)
ExtractElement = AllElements(n-1)
End Function

and also Match and Loops, just having hard time putting it all
together

Thanks in advance,
JAG

Reply to this Message

Joel - 30 May 2008 12:43 GMT

Function ATMSTRIKE(Company As String, _
Fraction As String, Equity As Single)

With Sheets("Sheet2")
'get company column, search for company name in row 1
Set c = .Rows(1).Find(what:=Company, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
LastRow = .Cells(Rows.Count, c.Column).End(xlUp).Row
RowCount = 2
ATMSTRIKE = ""
Do While RowCount <= LastRow
Element = .Cells(RowCount, c.Column)
'split fields by spaces
SplitElement = Split(Element)
SplitFraction = SplitElement(2)
SplitEquity = SplitElement(3)
'remove first letter and convert to a number
SplitEquity = Val(Mid(SplitEquity, 2))
If SplitFraction = Fraction Then
If BestElement = "" Then
BestEquity = SplitEquity
ATMSTRIKE = Element
Else
If Abs(SplitEquity - Equity) < _
Abs(BestEquity - Equity) Then

BestEquity = SplitEquity
ATMSTRIKE = Element

End If
End If
End If
RowCount = RowCount + 1
Loop
Else
ATMSTRIKE = "Company: " & Company & " Not found"
End If
End With

End Function

> I am looking to create a VB fuction to use a stock reference in a cell
> (A) from one workbook, to reference the correct column of information
[quoted text clipped - 51 lines]
> Thanks in advance,
> JAG

Reply to this Message