Run-Time 1004 Array formula help

[dmurphy1991]

Hi,

i keep getting the above error when trying to run this last bit of my code:

Dim laddercount As Double
Dim x, y As Double
laddercount = Evaluate("COUNTA(R:R)-1")

For y = 19 To laddercount + 19
For x = 7 To x + laddercount
Cells(x, y).Select
Selection.FormulaArray = "=INDEX(arrayrange,SMALL(IF(arrayrange = (Range(y & ""$6"")),ROW(ladderrange)),ROW(Cells(x,x),2))"


Next x
Next y



Where the ranges have been pre-set further up in the code, can post full code if necessary.

Any ideas why it isn't working?

Many thanks

 

[Jonmo1]

Can you post what you intend the formula to actually look like in the cell?
Are arrayrange and ladderrange Named Ranges, or ranges created in the code ?

 

[dmurphy1991]

Hi full code below:

Sub Autoladder()
'
'finds starting point of ladder list'
Dim ladderstart, rangeend, arrayrange As Range
Set ladderstart = Sheets("Sheet1").Range("6:6").Find(What:="New proposed ladder", Lookat:=xlPart, MatchCase:=False).Offset(1, 0)
ladderstart.Select
Range(Selection, Selection.End(xlDown)).Copy
Range("R7").PasteSpecial xlPasteValues


Set rangeend = Sheets("Sheet1").Range("6:6").Find(What:="New proposed sub ladder", Lookat:=xlPart, MatchCase:=False).End(xlDown)
Set arrayrange = Range(ladderstart, rangeend)
Set ladderrange = Range(ladderstart, ladderstart.End(xlDown))

'finds End point of index array'
'Set rangeend = Range("6:6").Find(What:="New proposed sub ladder", Lookat:=xlWhole, MatchCase:=False).Offset(1, 0)


'removes duplicate ladders and transposes them to form individual ladder applicable sub-ladder headings'
Range("R7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RemoveDuplicates Columns:=1, Header:=xlNo
Range("R7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("S6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

'Names the new list of ladders as "Ladders"'
Range("R7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "Ladders"


'Counts number of ladders'
Dim laddercount As Double
Dim x, y As Double
laddercount = Evaluate("COUNTA(R:R)-1")

For y = 19 To laddercount + 19
For x = 7 To x + laddercount
Cells(x, y).Select
Selection.FormulaArray = "=INDEX(arrayrange,SMALL(IF(arrayrange = Range(y & ""6""),ROW(ladderrange)),ROW(Cells(x,x),2))"


Next x
Next y




End Sub

My end goal is to create a list of ladders, then a list of sub ladders for each ladder. therefore the array returns all the different sub ladder strings to be listed under the appropriate ladder to which they belong.

Essentially i am automating a dependent data validation.

Thanks

 

[Jonmo1]

Try

Selection.FormulaArray = "=INDEX(arrayrange,SMALL(IF(" & arrayrange.Address & "="& Cells(6,y).Address & ",ROW(" & ladderrange.Address & ")),ROW(" & Cells(x,x).Address & "),2))"

 

[dmurphy1991]

UNfortunately that still returns the same error, just doesn't seem to like it :/

 

[Jonmo1]

Ok, can you post what the formula should end up looking like once it's in the cell?

 

[dmurphy1991]

I'm working from an array formula example i found which works on the same principle which uses this formula:
=INDEX($A$1:$B$19,SMALL(IF($A$1:$A$19=$E$1,ROW($A$1:$A$19)),ROW(1:1)),2)

Which produces the below, howeve as this is looking up 'Face' this works on the prmise the string in E1 is what it searches for, whereas i want mine to reference each column's hgeading and search for that in the same array, but just can't get it to work. Soryr i know this isn't 100% helpful, i'm trying to wrap my head around arrays at the same time!:
[TABLE="width: 320"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lookup:[/TD]
[TD]face[/TD]
[/TR]
[TR]
[TD]Eye[/TD]
[TD]Brow[/TD]
[TD][/TD]
[TD]Matches:[/TD]
[TD]BB/CC/Tinted[/TD]
[/TR]
[TR]
[TD]Eye[/TD]
[TD]Liner[/TD]
[TD][/TD]
[TD][/TD]
[TD]Blusher[/TD]
[/TR]
[TR]
[TD]Eye[/TD]
[TD]Mascara [/TD]
[TD][/TD]
[TD][/TD]
[TD]Bronzer[/TD]
[/TR]
[TR]
[TD]Eye[/TD]
[TD]Shadow[/TD]
[TD][/TD]
[TD][/TD]
[TD]Concealer[/TD]
[/TR]
[TR]
[TD]Eye[/TD]
[TD]Other[/TD]
[TD][/TD]
[TD][/TD]
[TD]Foundation[/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD="colspan: 2"]BB/CC/Tinted[/TD]
[TD][/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD]Blusher[/TD]
[TD][/TD]
[TD][/TD]
[TD]Powder[/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD]Bronzer[/TD]
[TD][/TD]
[TD][/TD]
[TD]Primer[/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD="colspan: 2"]Concealer[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD="colspan: 2"]Foundation[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD]Other[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD]Powder[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Face[/TD]
[TD]Primer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lip[/TD]
[TD]Lip[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nail[/TD]
[TD]Enamel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nail[/TD]
[TD="colspan: 2"]Treatment[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nail[/TD]
[TD]Other[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD]Other[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="5"></colgroup>[/TABLE]

 

[Jonmo1]

First, you have to concatenate the Address Properites of each range into the formula.
As I tried to do in the last post (but I forgot the first one).

But assuming we do that..
OK, you want this..

=INDEX($A$1:$B$19,SMALL(IF($A$1:$A$19=$E$1,ROW($A$1:$A$19)),ROW(1:1)),2)

But the code as you have it would produce

=INDEX($A$1:$B$19,SMALL(IF($A$1:$A$19=$E$1,ROW($A$1:$A$19)),ROW($A$1),2))

I don' think $A$1 instead of 1:1 would be a problem, but the misplaced ) would be.

Try

Selection.FormulaArray = "=INDEX(" & arrayrange.Address & ",SMALL(IF(" & arrayrange.Address & "=" & Cells(6, y).Address & ",ROW(" & ladderrange.Address & ")),ROW(" & Rows(x).Address(0, 0) & ")),2)"