With command

[tiredofit]

I have values in cells A1 to B2 on Sheet1.

I want to read those values into an array and paste onto Sheet2.

Why is it that this works:

    Dim MyArray() As Variant
    
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Sheet2.Cells(1, 1).CurrentRegion.Delete
    Sheet2.Cells(1, 1).Resize(UBound(MyArray(), 1), UBound(MyArray(), 2)).Value = MyArray()

but this fails:

    Dim MyArray() As Variant
    
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
        
    With Sheet2.Cells(1, 1)

        .CurrentRegion.Delete
        .Resize(UBound(MyArray(), 1), UBound(MyArray(), 2)).Value = MyArray()

    End With

wth the error message:

    Run-time error '434'

    Object required

Thanks

_{^{<strike>
</strike>}}

 

[RoryA]

The With statement holds a reference to Cells(1, 1). You then delete that entire block of cells, so the reference is invalid from that point on, but you then try to resize it, which causes the error. It's essentially like you used a variable:

    Dim MyArray() As Variant
    
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
    dim rg as range
    set rg = Sheet2.Cells(1, 1)

        rg.CurrentRegion.Delete
        ' rg is now invalid, because you deleted it, so the next line will raise an error
        rg.Resize(UBound(MyArray(), 1), UBound(MyArray(), 2)).Value = MyArray()

 

[tiredofit]

Thanks.

With the "With command" I am still trying to think of it like factorising in maths (ie ab+ac = a(b+c) )but I guess it's not exactly 100% the same!

 

[RoryA]

Not exactly, no! Think of it more like an implicit variable.

 

[tiredofit]

Thanks, will do.