Excel VBA Transpose an array and declare to another sheet range

Question

I have some Excel VBA code which generates an array based on inputs/calculations and then enters the values onto another sheet.

I have this working for one version of my spreadsheet which contains all of the calculations on one sheet using:

Range(Cells(4, "T"), Cells(4 + ball, "T")).Value = Application.Transpose(lon)

However, when I try to use this on another workbook which enters the values onto another sheet in the same location using:

Sheets("Calculations").Range(Cells(4, "T"), Cells(4 + ball, "T")).Value = Application.Transpose(lon)

I get an error stating: "Run-time error '1004': Application-defined or object-defined error". I have looked this up and also doing some debug.print of values in my array to ensure I understand the values and the length, believe my issue is with how I am declaring the range. However, I cannot seem to figure out the correct implementation.

As a work-around I have a For loop pasting the values into the cell but it seems to me there would be a cleaner way to do this.

For n = 1 To ball
    Sheets("Calculations").Cells(4 + n, "T").Value = lon(n)
Next n

Thanks for your help!

Siddharth Rout · Accepted Answer · 2015-08-03 20:15:00Z

3

Your cells object is not fully qualified.

While your range is referring to Sheets("Calculations") in Sheets("Calculations").Range but the Cells are referring to the ActiveSheet.

You can either do

Sheets("Calculations").Range(Sheets("Calculations").Cells(4, "T"), Sheets("Calculations").Cells(4 + ball, "T")).Value=Application.Transpose(lon)

or (Notice the dots before Cells Object)

With Sheets("Calculations")
    .Range(.Cells(4, "T"), .Cells(4 + ball, "T")).Value = Application.Transpose(lon)
End With

answered Aug 3, 2015 at 20:15

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BruceWayne Over a year ago

Just to piggy back on the comment about the dots before .cells. It's important you don't forget those, otherwise you could get some errors when using multiple worksheets. I've had too many headaches caused by simply not adding the . when using Range() and With() statements.

user2916836 Over a year ago

Wow. Thank you for your help. I would have never realized that extra reference on the cells. I took your first suggestion just so that when I am looking at the code in the future it will be clear what I am referencing and also like BruceWayne notes, I would probably miss those "." when using them in the future.

Collectives™ on Stack Overflow

Excel VBA Transpose an array and declare to another sheet range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related