Offset function creating error (VBA)

Question

The code below works for the first row of the table, but the FX_Rate and FX_Date offset functions keep leading to errors and the Table_Date and Table_Rate offset functions don't work at all (all in each ElseIf for each currency). Can someone please tell me what I'm doing wrong? I have the feeling I just have some offset functions in the wrong areas of the code, but I might be way off.

The code is meant to take the data in the table, match a date to the FX info and return the exchange rate on that day, then move on to the next entry in the table until it hits a blank cell.

I apologise if this is a stupid question or has been asked before - I couldn't find an answer.

Sub Convert()

Dim Table_Date As Range
Set Table_Date = Range("B12")

Dim FX_Date As Range
Set FX_Date = Range("L11")

Dim Table_Rate As Range
Set Table_Rate = Range("E12")

Dim FX_Rate As Range
Set FX_Rate = Range("M11")

Dim Table_Currency As Range
Set Table_Currency = Range("D12")

Dim x As Integer
Dim y As Integer
x = 2
y = 2

Do While x > 1
    If Table_Date = "" Then
        x = -1
    Else
        If Table_Currency = "USD" Then
            Do While y > 1
                If Table_Date = FX_Date Then
                    Table_Rate = FX_Rate
                    y = -1
                    x = -1
                Else
                    Set FX_Date = FX_Date.Offset(1, 0)
                    Set FX_Rate = FX_Rate.Offset(1, 0)
                End If
            Loop
            Set Table_Date = Table_Date.Offset(1, 0)
            Set Table_Rate = Table_Rate.Offset(1, 0)
        ElseIf Table_Currency = "EUR" Then
            Set FX_Rate = FX_Rate.Offset(0, 2)
            Do While y > 1
                If Table_Date = FX_Date Then
                    Table_Rate = FX_Rate
                    y = -1
                    x = -1
                Else
                    Set FX_Date = FX_Date.Offset(1, 0)
                    Set FX_Rate = FX_Rate.Offset(1, 0)
                End If
            Loop
            Set Table_Date = Table_Date.Offset(1, 0)
            Set Table_Rate = Table_Rate.Offset(1, 0)
        ElseIf Table_Currency = "JPY" Then
            Set FX_Rate = FX_Rate.Offset(0, 1)
            Do While y > 1
                If Table_Date = FX_Date Then
                    Table_Rate = FX_Rate
                    y = -1
                    x = -1
                Else
                    Set FX_Date = FX_Date.Offset(1, 0)
                    Set FX_Rate = FX_Rate.Offset(1, 0)
                End If
            Loop
            Set Table_Date = Table_Date.Offset(1, 0)
            Set Table_Rate = Table_Rate.Offset(1, 0)
        ElseIf Table_Currency = "SGD" Then
            Set FX_Rate = FX_Rate.Offset(0, 4)
            Do While y > 1
                If Table_Date = FX_Date Then
                    Table_Rate = FX_Rate
                    y = -1
                    x = -1
                Else
                    Set FX_Date = FX_Date.Offset(1, 0)
                    Set FX_Rate = FX_Rate.Offset(1, 0)
                End If
            Loop
            Set Table_Date = Table_Date.Offset(1, 0)
            Set Table_Rate = Table_Rate.Offset(1, 0)
        ElseIf Table_Currency = "HKD" Then
            Set FX_Rate = FX_Rate.Offset(0, 6)
            Do While y > 1
                If Table_Date = FX_Date Then
                    Table_Rate = FX_Rate
                    y = -1
                    x = -1
                Else
                    Set FX_Date = FX_Date.Offset(1, 0)
                    Set FX_Rate = FX_Rate.Offset(1, 0)
                End If
        Loop
        Set Table_Date = Table_Date.Offset(1, 0)
        Set Table_Rate = Table_Rate.Offset(1, 0)
    ElseIf Table_Currency = "NZD" Then
        Set FX_Rate = FX_Rate.Offset(0, 5)
        Do While y > 1
            If Table_Date = FX_Date Then
                Table_Rate = FX_Rate
                y = -1
                x = -1
            Else
                Set FX_Date = FX_Date.Offset(1, 0)
                Set FX_Rate = FX_Rate.Offset(1, 0)
            End If
        Loop
        Set Table_Date = Table_Date.Offset(1, 0)
        Set Table_Rate = Table_Rate.Offset(1, 0)
    Else
        Table_Rate = "Not a valid currency"
        y = -1
        x = -1
    End If
End If
Loop

End Sub

Answer 1

Assuming that you get through one loop where the first loop is HKD then FX_Rate is already offset 6 columns. You cannot use that as a base point for a subsequent offset without resetting the base point. Also, a Select Case statement would be better than all of your repetitious code.

This is a short rewrite of the section of your original where a lot of the repetitious code resides.

    Select Case LCase(Table_Currency)
        Case "usd"
            Set FX_Rate = Cells(FX_Rate.Row, "M")    'no offset from base point
        Case "eur"
            Set FX_Rate = Cells(FX_Rate.Row, "O")    'offset(0, 2)
        Case "jpy"
            Set FX_Rate = Cells(FX_Rate.Row, "N")    'offset(0, 1)
        Case "sgd"
            Set FX_Rate = Cells(FX_Rate.Row, "Q")    'offset(0, 4)
        Case "hkd"
            Set FX_Rate = Cells(FX_Rate.Row, "S")    'offset(0, 6)
        Case "nzd"
            Set FX_Rate = Cells(FX_Rate.Row, "R")    'offset(0, 5)
        Case Else
            Set FX_Rate = Nothing
    End Select

    If Not FX_Rate Is Nothing Then
        Do While y > 1
            If Table_Date = FX_Date Then
                Table_Rate = FX_Rate
                y = -1
                x = -1
            Else
                Set FX_Date = FX_Date.Offset(1, 0)
                Set FX_Rate = FX_Rate.Offset(1, 0)
            End If
        Loop
        Set Table_Date = Table_Date.Offset(1, 0)
        Set Table_Rate = Table_Rate.Offset(1, 0)
    Else
        Table_Rate = "Not a valid currency"
        y = -1
        x = -1
    End If

Taking a wider look at your image of sample data and what you are trying to accomplish, it seems that your whole process could be written as something like this.

Sub currencyConversionRates()
    Dim rw As Long, x As Variant, y As Variant

    With Worksheets("Sheet3")
        For rw = 12 To .Cells(Rows.Count, "B").End(xlUp).Row
            x = Application.Match(.Cells(rw, "D").Value2, .Rows(5), 0)
            y = Application.Match(.Cells(rw, "B").Value2, .Columns(12), 0)
            If Not (IsError(x) Or IsError(y)) Then
                .Cells(rw, "E") = .Cells(y, x).Value2
            Else
                .Cells(rw, "E") = "Not a valid currency"
            End If
        Next rw
    End With
End Sub

But for all intents and purposes, that can also be reduced by putting the following formula into E12,

=IF(AND(LEN(D12), B12>=$L$11), VLOOKUP(B12,L:R, MATCH(D12, L$5:R$5, 0), FALSE), "Not a valid currency")

... and filling down.