AW: Sverweis Makro mit variablen
22.06.2015 13:01:01
Benny
Hi Beverly,
die Basis ist die Kombination aus Sverweis und Wahl im Original =SVERWEIS(G15&"USD";WAHL({1.2};'R:\Allgemein\Allgemein BK\Wechselkurse\[WK.xlsx]Tabelle1'!$C2:$C99999&'R:\Allgemein\Allgemein BK\Wechselkurse\[WK.xlsx]Tabelle1'!$D2:$D99999;'R:\Allgemein\Allgemein BK\Wechselkurse\[WK.xlsx]Tabelle1'!$E2:$E99999);2;FALSCH)
Der ganze Quelltext:
Public DateZ
Public WKZ
Public FKZ
Public wkmin
Public wkmax
Option Explicit
Private Const DollarFormat As String = "#,##0.00 [$USD]"
Private Const GBPFormat As String = "#,##0.00 [$GBP]"
Private Const SGDFormat As String = "#,##0.00 [$SGD]"
Private Const MYRFormat As String = "#,##0.00 [$MYR]"
Private Const JPYFormat As String = "#,##0.00 [$JPY]"
Private Const EuroFormat As String = "#,##0.00 $"
Private esel As String
Sub WechselKursEinfuegen()
'Application.ScreenUpdating = False
Dim ZielWb As Workbook
Set ZielWb = ThisWorkbook
Dim Quelle As String
Workbooks.Open "R:\Allgemein\Allgemein BK\Wechselkurse\WK.xlsx"
Quelle = "[WK.xlsx]Tabelle1!"
ZielWb.Activate
Dim i As Long, Datei As String
For i = wkmin To wkmax
Select Case Range(FKZ & i).NumberFormat
Case DollarFormat
Range("H" & i).Select
Selection.FormulaArray = _
"=VLOOKUP Range("" & DateZ & "" & i) &""USD"",CHOOSE({1,2}, " & Quelle & " R2C3: _
R99999C3& " & Quelle & " R2C4:R99999C4, " & Quelle & " R2C5:R99999C5),2,FALSE)"
Case SGDFormat
Range("H" & i).Select
Selection.FormulaArray = _
"=VLOOKUP(RC[-1]&""SGD"",CHOOSE({1,2}, " & Quelle & " R2C3:R99999C3& " & Quelle & " _
R2C4:R99999C4, " & Quelle & " R2C5:R99999C5),2,FALSE)"
Case MYRFormat
Range("H" & i).Select
Selection.FormulaArray = _
"=VLOOKUP(RC[-1]&""MYR"",CHOOSE({1,2}, " & Quelle & " R2C3:R99999C3& " & Quelle & " _
R2C4:R99999C4, " & Quelle & " R2C5:R99999C5),2,FALSE)"
Case GBPFormat
Range("H" & i).Select
Selection.FormulaArray = _
"=VLOOKUP(RC[-1]&""GBP"",CHOOSE({1,2}, " & Quelle & " R2C3:R99999C3& " & Quelle & " _
R2C4:R99999C4, " & Quelle & " R2C5:R99999C5),2,FALSE)"
Case JPYFormat
Range("H" & i).Select
Selection.FormulaArray = _
"=VLOOKUP(RC[-1]&""JPY"",CHOOSE({1,2}, " & Quelle & " R2C3:R99999C3& " & Quelle & " _
R2C4:R99999C4, " & Quelle & " R2C5:R99999C5),2,FALSE)"
Case EuroFormat
Range("H" & i).Value = 1
End Select
Next
Workbooks("WK.xlsx").Close False
End Sub