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
"jag" wrote:
> 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
>
|
Similar Threads
