Just testing.

Code:Sub Oops() Application.Calculation = xlCalculationAutomatic End Sub

_____ Workbook:20SepSample3.xlsm( Using Excel 2007 32 bit )

Worksheet:

_{Row}\^{Col} A B C D E F G H I1 ICR NotInH24 1MTH 3MTH 6MTH 1YR 3YR 5YR20.61 1.52 7.14 0.92 2.88 3.47 4.03 21.1130.73 -0.01 5.02 -2.43 -0.48 2.97 5.36 15.2740.8 0.99 7.63 8.76 11.18 6.56 5.95 43.2450.22 0.38 0.67 3.41 3.93 5.42 4.63 26.52FundSelection

https://imgur.com/SnM2jm2

<blockquote class="imgur-embed-pub" lang="en" data-id="SnM2jm2"><a href="https://imgur.com/SnM2jm2">View post on imgur.com</a></blockquote><script async src="//s.imgur.com/min/embed.js" charset="utf-8"></script>

https://i.imgur.com/SnM2jm2.jpg

The text that you have entered is too long (27539 characters). Please shorten it to 25000 characters long.

Hi.

The problem is solved here, but I am just adding some stuff, out of passing interest…

I am not 100% sure about this, but I think Excel finds that problem giving Cells(i, 0) syntactically OK, ref!!.

But it can’t find a range object in row 1 that is one column to the left of column Ain a worksheetref!!

I think it is that problem that chucks up the error.

A bit more info, just out of interest… _

_... I am considering two different things which it is very easy to get mixed up

Excel referencing spreadsheet cells, which is mostly a “relative referencing thing” of one sort or another, even if it might not appear to be at first glance, ref!!

_ … How Excel takes arrays from ranges and pastes arrays into ranges

First, a quick review of Excel spreadsheets and its conventional doings with arrays…

Excel, when doing interactions with spreadsheet ranges and arrays, has a general convention of considering spreadsheet range values in a 2 D array, the first dimension refers typically to the horizontal row, and the second dimension refers typically to the vertical column. ( Conventionally/ usually the first row and first column is taken as indicia 1, (it does not necessarily always have to bewhen talking about the arraysassociated with a 2 D range Area ) ).

So pseudo like

Arr(row,column) = {1 To n1, 1 To n2}

As OptionBase1 said, conventionally the first row and first column is taken as indicia 1. Note this is when referencing cellsin an entire spreadsheet- There is a small subtlety there ref!!. We are talking there about referencing spreadsheet cells, which is actually a relative thing as explained in ref!!

**Also you need to be a bit careful when taking in or pasting out an array into a spreadsheet since its handled a bit differently as regard indicia:

Excel will conventionally make an array filled from a spreadsheet as starting at first and second dimension ( pseudo “row” and “column” ) indicia of 1 , regardless of where the range is.

The other way around is similar , but a bit different. It doesn’t matter where your indicia start. If you paste your 2D array out into a spreadsheet, it starts always at top left of the range where you paste it to: Top left is top left regardless of what you have chosen to be the start indicia of the 2D array that you are pasting out.

Now, .. How a spreadsheet handles 1 D arrays / relevance to this Thread

_..It seems that a lot of computer things in general that give out arrays of 1 dimension, often have their first integer indicia thing set at 0, at least by default, ref##

I suppose technically speaking a 1 D array has no orientation, but some of us often think conveniently in our human minds of it as looking “horizontal” or like in a row. Excel goes along with that way of thinking sometimes, and in many things to do with a spreadsheet , if you pass a 1 D array, (even one with a first indicia thing set at 0 or any other value ) , then it does the same as it would if you gave it a 2 D array where the firstdimensionhad adimensionof 1, ( Note: I saiddimensionof 1 there, not indicia ) . Saying the same thing in more simple, (not necessarily perfectly technically correct ) terms: In spreadsheet interactions, Excel will often take a 1 D array as if it was a2 D , 1 “row”array, or2D 1 “horizontal”array,

Pseudo {0 to n2} taken as if it were {1 To 1, 1 To n2+1},

or

pseudo {0 to n2} taken as if it were {7 To 7, 1 To n2+1}

or

pseudo {0 to n2} taken as if it were {9 To 9, 65 To n2+65}

or

pseudo {0 to n2} taken as if it were {8 To 8, 0 To n2 }

etc.. etc..

( using n2 rather than n is just a personal convention of mine. It helps me keep my brain orientated in the perception that its referring to something to do with a column number in a row, and it either is associated with , or is pseudo equivalent to, a second dimension in a 2D array )

( Unfortunately Excel VBA has some String functions that came from VB which insist on a 1 D array: That can be a Bummer – since those functions wont take any of those2 D , 1 “row”arrays instead )

Coming back to the relevance to the OPs issue, or similar issues…

In simple terms, if you had a 1 D array with values in it, ( even one starting at indicia 0 ), which you wanted to paste as a row in consecutive cells into a spreadsheet range, then you can do it directly in a single code line, pseudo like

___MyRnge = My1DArray()

The code snippet below perhaps explains it a bit better, if you run it from the VB Editor in step ( key F8 ) mode .

Code:' https://www.vbforums.com/showthread.php?889021-Error-1004-Application-defined-or-object-defined-error-VBA-Excel Sub OneD_TwoD_Arrays() Rem 0 worksheets info, use arbritrary worksheet for demos.. Dim Ws As Worksheet: Set Ws = ActiveSheet ' You don't have to do this. You can forget it and miss out all the Ws. bits But that can catch you out sometimes, when you might end up referrencing a range in a different worksheet to that which you thought you were Rem 1 make arbritrary 1 D array '1_a) long way Dim arr1D_a(0 To 4) As String Let arr1D_a(0) = "BigJob": arr1D_a(1) = "Baveria": arr1D_a(2) = "Computer": arr1D_a(3) = "OldOne": arr1D_a(4) = "42" '1_b) quick way using VBA Array function , https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/array-function Dim arr1D_b() As Variant ' The array needs to be dynamic and needs to have Variant type, because....._ _.. the next code line function returns a field of elements which are housed in elements of Variant type , so the type must be Variant regardles of what types are withing the elements or we will get type mismatch errors , and _.. the working of the function is such that it sends infomation to size the thing taking it: If we have a fixed size array as in _b then Excel will error becuse Excel will insist on wanting to do this sizing of any recieving array, which it can't do if the array is fixed size. Let arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42") ' This returns us a 5 element array starting at indicia 0 So we have now arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42" Rem 2 paste arrays into spreadsheet Ws.Cells.ClearContents Let Ws.Range("C1:G1").Value = arr1D_a() ' Top left of where the 5 column, single row is pasted is cell C1 Let Ws.Range("D2:H2").Value = arr1D_b() ' Top left of where the 5 column, single row is pasted is cell D2 Rem 3 Excel Spreadsheet array interaction: '3a) usually Excel makes a 2D array from a range with indicia starting at (1, 1) Dim arr2D1Row_a() As Variant ' In the next code line, I am uisng the .Value property applied to a multi cell rectangular single area range. When i do that, .Value is programmed to return me an array of values which as far as it knows might be number values or text values, or formulas or blanks or vb errors. To acccomadte the possibility of us distinguisching later what type we have, all elelments in the returned array are Variant because only Variant can "hold" all the different types in it. (a number can go in a number type variable, (Single Double Long Integer) or a Variant type variable; a text can go in a String type or a Variant type etc. etc). So, because Excel is programmed to return an array of Variant type Elements, I must decalre my array, arr2D1Row_a() , as variant types, or else i will get a type mismatch error at the next code line Let arr2D1Row_a() = Ws.Range("D2:H2").Value ' Excel decides the array type in this situation. It will always be a 2D 1 "row" array {1 To 1, 1 To 5} Nothing you can do will influence this '3b) When pasting out, top left is always top left of the range you specify, Excel doesn't care what indicia you've got in the array being pasted out Let Ws.Range("E3:I3").Value = arr2D1Row_a() ' Top left of where the 5 column, single row is pasted is cell E3 Dim arr2D1Row_b(7 To 7, 42 To 47) As String ' A 2D 1 "row" array with arbritrary inidia: I can decide this but ...._ Excel doesn't care what indicia you have chosen when pasting out Let arr2D1Row_b(7, 42) = "BigJob": arr2D1Row_b(7, 43) = "Baveria": arr2D1Row_b(7, 44) = "Computer": arr2D1Row_b(7, 45) = "OldOne": arr2D1Row_b(7, 46) = "42" Let Ws.Range("F4:J4").Value = arr2D1Row_b() ' Top left is always top left, cell F4 in this case. ...._ Excel doesn't care what indicia you have chosen when pasting out End Sub

Alan

ref## In VBA, you can change the typical start indicia of 1 dimensional producing things from the default 0 to 1

One way to do that is to write the Username of a certain VB forums member at the top of your code module like this

In the case of the macro example above, that will make no change what so ever on the results.Code:OptionBase1

There is a small change to the workings: It is in the code line that uses the VBA Array function,

arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42")

Previously it gave me this:

arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42"

https://i.imgur.com/5e151Tf.jpg

Now it gives me this:

arr1D1_b(1) = "BigJob": arr1D_b(2) = "Baveria": arr1D_b(3) = "Computer": arr1D_b(4) = "OldOne": arr1D_b(5) = "42"

https://i.imgur.com/SnM2jm2.jpg

( I get that Watch Window if I highlight any of the arr1D_b() bits of thee coding and then hit keys Shift+F9.

Having done that, then after I have stepped past the code line that fills that array, the contents will be shown in that Watch Window after hitting that+symbol )

**There is no effect on any of the pasting out done in the macro, because top left is always top left of the range you specify, and Excel doesn’t care what indicia you’ve got in the array being pasted out, ( be it a 1 D array , or even a wildly indicied 2 D array, as in the last example in the macro )

ref !! It is a bit more complicated if you are referencing a range somewhere in a spreadsheet, since then you can “go backwards from the origin”. Like

Range("B10").Cells(0, 0) is like Range("A9")

or

Range("B10").Cells(-1, 0) is like Range("A8").

Something like,

Range("B10").Cells(0, -1)

will give you your Error 1004 - Application-defined or object-defined error VBA Excel. In this example its because you are trying to go 2 columns back from column B