Copy array

Kofod

New Member
Joined
Mar 4, 2019
Messages
2
Hey Guys,

Haven't worked with VBA in Excel for years and its slightly frustrating...(considering throwing my computer out the window at this point)

here is the super ridiculous simple thing i want to do:

Sub Array_Formula()
Sheets("Pantebreve").Range([indirect("A1000"]).Formula = Sheets("Amortizationsberegner").Range("B7:NN11").Formula
End Sub

A1000 in the "Pantebreve" sheet contains this: ER22:TD26
This value is relative to a dropdown of names, so can't be hardcoded.
The start number is the crucial one "ER22".
"TD22" is simply calculated to make the same range as B7:NN11

Im getting this:
Run time error '1004':
Application-defined or object-defined error

Sorry for such a noob question, but its driving me nuts.

Thanks!!!!!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi. Do the indirect like this:
Code:
Sheets("Pantebreve").Range(Sheets("Pantebreve").Range("A1000"))
 
Upvote 0

Forum statistics

Threads
1,212,042
Messages
6,105,569
Members
447,972
Latest member
carrieann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top