Excel VBA run time error 450 from referencing a range directly rather than through an intermediate variable

user_11496946 0
Viewed 270

I am getting a Run Time Error 450 when I try to reference a value in a range directly, but if I use an intermediate variable it works. I do not understand why so I am worried I will get the error again at some point in the future without knowing why.

I have tried using a With ... End With block and it still does not work when I reference the range directly.

This code gives the error

Public Sub Test_PT()
Dim lol As String

    lol = Worksheets(1).PivotTables("PivotTable2").RowRange(2, 1).Value
    MsgBox (lol)

End Sub

While this code works fine

Public Sub Test_PT()
Dim lol As String
Dim rng As Range

    Set rng = Worksheets(1).PivotTables("PivotTable2").RowRange
    lol = rng(2, 1).Value

    MsgBox (lol)

End Sub

I do not know why it works in the 2nd code block but not in the first. I have tried variations of which level to set an intermediate variable. If I create a pivot table variable and set this appropriately, the code runs fine, but if I create a worksheet variable and try to reference the same range using the worksheet variable it does not work.

While I can get this chunk of code working by using an intermediate variable, I really want to understand WHY I need to do this in case it comes up in other contexts.

vba excel
1 Answers

The RowRange property returns a Range object, but it doesn't take any arguments, so you can't pass any in.

lol = Worksheets(1).PivotTables("PivotTable2").RowRange.Cells(2, 1).Value

works fine for me, as does

lol = Worksheets(1).PivotTables("PivotTable2").RowRange()(2, 1).Value
user_4788840
Related