需求：编写基于日期查找、按币种偏移列数的VLOOKUP VBA宏

Got it, let's turn that manual VLOOKUP drag into a robust VBA macro that adapts to the currency in column F. Your current test code is a good start, but we need to add dynamic column mapping and automate the fill-down for all rows.

Here's a complete, customizable solution that handles different currencies, avoids errors, and processes your entire dataset automatically:

Sub DynamicFXLookup()
    Dim wsRecon As Worksheet
    Dim wsRates As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currencyColMap As Object ' Stores currency-to-column index mappings
    Dim lookupDate As Variant
    Dim currency As String
    Dim targetCol As Integer
    Dim rate As Variant
    
    ' Set worksheet references for easier access
    Set wsRecon = ThisWorkbook.Sheets("Recon")
    Set wsRates = ThisWorkbook.Sheets("FX Rates")
    
    ' Create a dictionary to map currencies to their column numbers in FX Rates
    ' **UPDATE THESE VALUES TO MATCH YOUR ACTUAL FX RATES SHEET!**
    Set currencyColMap = CreateObject("Scripting.Dictionary")
    With currencyColMap
        .Add "EUR", 2   ' Example: EUR is in column B (2nd column) of FX Rates
        .Add "USD", 3   ' USD is in column C (3rd column)
        .Add "GBP", 4   ' GBP is in column D (4th column)
        .Add "JPY", 5   ' Add more currencies as needed
    End With
    
    ' Find the last row with data in column A (date column) of Recon sheet
    lastRow = wsRecon.Cells(wsRecon.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each data row (start at row 2 assuming row 1 is header)
    For i = 2 To lastRow
        lookupDate = wsRecon.Cells(i, "A").Value ' Get the date to look up
        currency = wsRecon.Cells(i, "F").Value   ' Get the currency from column F
        
        ' Check if we have a column mapping for this currency
        If currencyColMap.Exists(currency) Then
            targetCol = currencyColMap(currency)
            
            ' Use Application.VLookup to handle errors gracefully
            rate = Application.VLookup(lookupDate, wsRates.Range("A:K"), targetCol, False)
            
            ' Write the rate to your target column (here we use column G)
            If Not IsError(rate) Then
                wsRecon.Cells(i, "G").Value = rate
            Else
                wsRecon.Cells(i, "G").Value = "N/A" ' Show if no rate found
            End If
        Else
            wsRecon.Cells(i, "G").Value = "Unknown Currency" ' Handle unlisted currencies
        End If
    Next i
    
    MsgBox "Currency rate lookup completed successfully!", vbInformation
End Sub

Key Details & Customization Tips: #

• Currency Column Mapping: The currencyColMap dictionary is where you link each currency to its column index in the FX Rates sheet. For example, if your CAD rates are in column 6 (F), add .Add "CAD", 6 to the list.
• Dynamic Row Range: The macro automatically finds the last row of data in column A, so it works even if your dataset grows or shrinks.
• Error Handling: Using Application.VLookup instead of WorksheetFunction.VLookup lets us catch cases where a date/currency combination isn't found, instead of crashing the macro. We show "N/A" or "Unknown Currency" for clarity.
• Target Column: The macro writes results to column G (wsRecon.Cells(i, "G")). Change this to any column letter (like "H" or "I") to match your needs.

How to Use: #

1. Open your Excel file, press Alt + F11 to open the VBA editor.
2. Insert a new module (Right-click your workbook in the Project pane > Insert > Module).
3. Paste the code above.
4. Update the currencyColMap section to match your FX Rates sheet's column layout.
5. Run the macro (press F5 in the editor, or assign it to a button in your sheet for easier access).

内容的提问来源于stack exchange，提问作者James