在偏移量中使用行变量会导致"Empty"

Question

我有下面的代码，但由于某种原因，"myRng.Offset(LastRow， 0(。值"为"空"，因此不执行计算，并且由于除以 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

新编辑 - 将 FXval 从 Long 更改为 Variant - 代码现在可以完美运行！ 感谢Jeeped的回答和Chris Neilson的建议

Answer 1

你的数学错了。您从第 1 行开始并偏移总行数，因此最终超出填充值 1 行。

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