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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
Hi. Do the indirect like this:
Code:
Sheets("Pantebreve").Range(Sheets("Pantebreve").Range("A1000"))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,215
Messages
5,836,053
Members
430,403
Latest member
RoToR

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
Top