[SOLVED] Run-tim error 1004 when use WorksheetFunction.Transpose in VBA but works ok if use excel TRANSPOSE function

Run-tim error 1004 when use WorksheetFunction.Transpose in VBA but works ok if use excel TRANSPOSE function

If I have the Seven Dwarfs names as strings in cells H1 to N1 and then select cells B1 to B7, then insert the formula
=TRANSPOSE(H1:N1)
and then Ctrl-Shift-Enter, then the Seven Dwarfs are inserted into the B column as expected.

However, if I try the same thing using VBA as below:

Then I get

Run-time error '1004'
Method 'Range' of object '_Global' failed.

    Option Explicit
    Option Base 1

    Sub TransposeArray()
    Dim A() As Variant
    Dim nr As Integer
    nr = 7
    ReDim A(nr) As Variant
    A(1) = "Doc"
    A(2) = "Grumpy"
    A(3) = "Happy"
    A(4) = "Sleepy"
    A(5) = "Bashful"
    A(6) = "Sneezy"
    A(7) = "Dopey"

    ' this line below fails with run-time error '1004'
    Range("A1:A " & nr) = WorksheetFunction.Transpose(A)

    ' below line WITHOUT Transpose works fine
    'Range("A1:G1") = A
    End Sub

I have tried this with Excel 2019 and an older version 2007. Same error.

What am I doing wrong?

Solution

You have an unnecessary space in the address:

Range("A1:A" & nr) = WorksheetFunction.Transpose(A)
'         ===