With command

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,080
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:

Rich (BB code):
    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:

Rich (BB code):
    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:

Rich (BB code):
    Run-time error '434'

    Object required
Thanks

<strike>
</strike>
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,146
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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:

Code:
    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()
 
Last edited:

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,080
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,146
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Not exactly, no! Think of it more like an implicit variable.
 

Forum statistics

Threads
1,077,649
Messages
5,335,482
Members
399,018
Latest member
KathyMoUHC

Some videos you may like

This Week's Hot Topics

Top