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!!!!!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

steve the fish

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

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,353
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top