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

ItI don't understand what you're trying to do. A picture or two might help. Based on your general description, I have the feeling there's a much simpler solution. — Doug Glancy
– Doug Glancy, Commented Apr 23, 2016 at 17:25
@DougGlancy imgur.com/xJlcimb The macro is meant to take data from the left and use the historical exchange rates on the right to covert the amount (at the moment, I'm just trying to get the rate, not complete the actual mathematical conversion). — 1937827
– 1937827, Commented Apr 23, 2016 at 17:29
That would make sense as a way to simplify the macro significantly. However, if there is a way to modify what I've already written, rather than starting again, I'd rather do that. Am I calling Set Table_Date = Table_Date.Offset(1, 0) and Set Table_Rate = Table_Rate.Offset(1, 0) in the right spot? — 1937827
– 1937827, Commented Apr 23, 2016 at 17:50

score 1 · Accepted Answer · 2016-04-23 19:26:03Z

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.

edited Apr 23, 2016 at 19:26

answered Apr 23, 2016 at 17:59

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

1937827 Over a year ago

Thank you. I tried swapping this in, but it didn't work. Can you please confirm that this replaces everything between (and including) If Table_Currency = "USD" Then and Loop End Sub? Do I need to change any other aspects of the code to make it work?

user4039065 Over a year ago

Yes, you are likely going to have to change other parts of your code. The above should help legibility though. I'm afraid I do not know enough about your original data layout to speculate further. The above pointed out the most glaring inconsistency (to my eyes at least) with a solution towards getting the offsets correct on subsequent loops.

1937827 Over a year ago

So, just to confirm (sorry for the stupid questions), the code you wrote checks the cell containing the currency and then runs through the dates to find the correct rate on that day? Will it also then move to the next cell? I would need to modify the variables I originally defined to match what you've written?

user4039065 Over a year ago

The code I provided takes 92 lines of your code (everything inside the outer Do While ... Loop) and compresses it down to about 35 lines by introducing a Select Case and removing repetitious code lines by moving them to a centralized location. Your code did not work to begin with and there is no sample data so some of what I provided is guesswork. You need to step through the code (repeatedly tap [F8]) while keeping an eye on variables (with Watches and/or Locals window) and logic flow. If something doesn't run through as you think it should, make adjustments and restart the whole [F8] process

Collectives™ on Stack Overflow

Offset function creating error (VBA)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related