Using a row variable in offset results in "Empty"

Question

I have the below code, but for some reason, the "myRng.Offset(LastRow, 0).Value" is "Empty" and therefore the calculations are not performed and I receive the Overflow error due to a divide by 0.

Dim ws1 As Worksheet, wb1 As Workbook, LastRow As Long, LastCol As Long, myCol As String
Dim RowTest As Long, myRng As Range, FXwb As Workbook, ws2 As Worksheet
Dim FXrng As Range, FXval As Variant, Cur As String

LastRow = ws1.Range("B" & Rows.Count).End(xlUp).Row
Set myRng = Range("1:1").Find("LocalCurrency")
If myRng.Offset(LastRow, 0).Value <> "USD" Then
    Cur = myRng.Offset(LastRow, 0).Value
    FXwb.Activate
    Set FXrng = Range("C:C").Find(Cur)
    FXval = FXrng.Offset(0, 1).Value
    wb1.Activate
    Set myRng = Range("1:1").Find("Commitment (USD)")
    myRng.Offset(LastRow, 0).Value = myRng.Offset(LastRow, -1).Value / FXval
    Set myRng = Range("1:1").Find("Funding (USD)")
    myRng.Offset(LastRow, 0).Value = myRng.Offset(LastRow, -1).Value / FXval
    Set myRng = Range("1:1").Find("Adjusted Valuation (USD)")
    myRng.Offset(LastRow, 0).Value = myRng.Offset(LastRow, -1).Value / FXval
    Else
    Set myRng = Range("1:1").Find("Commitment (USD)")
    myRng.Offset(LastRow, 0).Value = myRng.Offset(LastRow, -1).Value
    Set myRng = Range("1:1").Find("Funding (USD)")
    myRng.Offset(LastRow, 0).Value = myRng.Offset(LastRow, -1).Value
    Set myRng = Range("1:1").Find("Adjusted Valuation (USD)")
    myRng.Offset(LastRow, 0).Value = myRng.Offset(LastRow, -1).Value
End If

New edit - changed FXval from Long to Variant - code works perfectly now! thanks to Jeeped for the answer and Chris Neilson for the suggestions

FWIW, your use of Find is problematic. Because you haven't specified anything other than the What parameter, you might not get the results you expect. The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method (either the user in Excel or with VBA). If you do not specify values for these arguments the next time you call the method, the saved values are used. Also, you are using implicit references to the ActiveSheet — chris neilsen
– chris neilsen, Commented Sep 13, 2018 at 1:18

user4039065 · Accepted Answer · 2018-09-12 21:34:57Z

1

Your maths are wrong. You are starting at row 1 and offsetting the total number of rows so you end up 1 row beyond the populated values.

myRng.Offset(LastRow - 1, 0).Value
'alternate
ws1.cells(LastRow, myRng.column).Value

answered Sep 12, 2018 at 21:34

user4039065

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

5 Comments

AJames Over a year ago

would that be true throughout the snippet?

user4039065 Over a year ago

If you want the last populated cell in each 'found' column then yes, it would be true throughout. Perhaps the alternate I offered makes more sense.

AJames Over a year ago

yeah - i just added "-1" to all of them and it did work, but now the FXval = FXrng.Offset(0, 1).Value throws off Object variable not set error. Should this be a variant or string? the data is a number

user4039065 Over a year ago

It would appear that cur cannot be found in Range("C:C").

AJames Over a year ago

was looking at the wrong column - should have been B not C - I'm all set

Collectives™ on Stack Overflow

Using a row variable in offset results in "Empty"

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related