ExcelBanter - Equivalent of Vlookup function programmatically

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Equivalent of Vlookup function programmatically (https://www.excelbanter.com/excel-programming/380441-equivalent-vlookup-function-programmatically.html)

Equivalent of Vlookup function programmatically

Hi there,
I can see I am not the only one into currencies questions, thanks to
John and the following macro I now have proper tickers.
My goal is now to be able to input the currency rate associated with
the curreny code.
I could do a Vlookup as =VLOOKUP(N2,Currencies!A2:C43,3)
but I need to do it programatically and I recon I am lost for that one
.... any ideas please?
Kind regards
Daniel

Sub Get_Currency_Code_for_Financial_Conversion()

Dim Currency1 As Variant
Dim Currency2 As Variant
Dim CurrencyCode As Variant

For i = 2 To Last_Row(Sheets("Stocks").Columns("A:A")) + 1
Currency1 = Sheets("Characteristics").Range("G" & i)
Currency2 = Sheets("Characteristics").Range("H" & i)
If Sheets("Characteristics").Range("G" & i) <
Sheets("Characteristics").Range("H" & i) Then CurrencyCode =
UCase(Currency1) & UCase(Currency2) & " Curncy" Else CurrencyCode = ""
Sheets("Characteristics").Range("N" & i) = CurrencyCode

End Sub

Equivalent of Vlookup function programmatically

Thats perfect, once again a big thank you

On Jan 3, 3:09 pm, "Don Guillett" wrote:
You can use
application.vlookup(range("n2"),worksheets("curren cies").range("a1:c43"),3,0)
or look in the vba help index for FIND and FINDNEXT (good example) and
OFFSET.

--
Don Guillett
SalesAid Software
wrote in oglegroups.com...

Hi there,
I can see I am not the only one into currencies questions, thanks to
John and the following macro I now have proper tickers.
My goal is now to be able to input the currency rate associated with
the curreny code.
I could do a Vlookup as =VLOOKUP(N2,Currencies!A2:C43,3)
but I need to do it programatically and I recon I am lost for that one
... any ideas please?
Kind regards
Daniel

Sub Get_Currency_Code_for_Financial_Conversion()

Dim Currency1 As Variant
Dim Currency2 As Variant
Dim CurrencyCode As Variant

For i = 2 To Last_Row(Sheets("Stocks").Columns("A:A")) + 1
Currency1 = Sheets("Characteristics").Range("G" & i)
Currency2 = Sheets("Characteristics").Range("H" & i)
If Sheets("Characteristics").Range("G" & i) <
Sheets("Characteristics").Range("H" & i) Then CurrencyCode =
UCase(Currency1) & UCase(Currency2) & " Curncy" Else CurrencyCode = ""
Sheets("Characteristics").Range("N" & i) = CurrencyCode

End Sub

Equivalent of Vlookup function programmatically