Copying array formula to a range of cells...

[Guest]

Hello,

EXCEL 2003 --

I have the following formula:

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET1'!$D$1:$D$12000&'SHEET1'!$E$1:$E$12000,0),30)}

This s located in cell b2 and in cell c2:j2 I have the same formula with a
different number at the end.

Issue:

I need to copy these cells B2:J2 to the following range
B4:J12500 -- However, when I do this the computer stop responding and excel
seems to crash. I've done this for the past 3 days leaving the computer
running for a maximum of 4 hours.

Help. Is there anyway to do this quicker.

Thanks
Dennis G.

 

[Sharad]

Hi Dennis,

When you want to copy the formula in b2 to b4:
Do you want in b4 'Match(C2&' part to remain C2 or it should be C4 ?
(And smillar for C4: to J4)

If remain C2 (& simillar for C4to J4)then try following code :
Sub TryThis()
Dim i As Integer
For i = 2 To 9
With Sheet1
.Range(.Cells(4, i), .Cells(12500, i)).Formula = _
.Cells(2, i).Formula
End With
Next i
End Sub

Or if it should be C4 (& simillar for C4 to J4) try following code:

Sub OrTryThis()
Dim i As Integer, strFormula As String
For i = 2 To 9
With Sheet1
.Range(.Cells(4, i), .Cells(6, i)).Formula = _
.Cells(2, i).Formula
strFormula = .Cells(6, i).Formula
.Range(.Cells(4, i), .Cells(6, i)).Formula = strFormula
End With
Next i
End Sub

It won't take more than 2 to 3 seconds to finish.

Sharad

 

[Guest]

Thanks...

I need it to be C4... I will test this.

 

[Guest]

Sharad,

Tested and it seems to work. However, the formula that I
have in B2 is an Array Formula. When I do the OrTryThis()
I'm getting #VALUE.. How do I edit to make it an array
formula?

Thanks
Dennis G.

 

[Tom Ogilvy]

.FormulaArray = < string representation of formula >

will create an array formula.

 

[Guest]

Tom,

I modified the response provided by Sharad.. I am still getting a 1004
error, I am on XL2000 on W98.

Lost and Confused
(Dennis G)



'Sharad


With Sheet1
.Range("B2:B11789").FormulaArray = "=INDEX('MIA
PPRRVU05'!$A:$AI,MATCH($G2&"",'MIA PPRRVU05'!$A:$A&'MIA
PPRRVU05'!$B:$B,0),30)"
.Range("C2:C11789").FormulaArray = "=INDEX('MIA
PPRRVU05'!$A$8:$AI$13219,MATCH($G11789&"",'MIA PPRRVU05'!$A$8:$A$13219&'MIA
PPRRVU05'!$B$8:$B$13219,0),28)"
.Range("D211789").FormulaArray = "=INDEX('MIA
PPRRVU05'!$A$8:$AI$13219,MATCH($G11789&"",'MIA PPRRVU05'!$A$8:$A$13219&'MIA
PPRRVU05'!$B$8:$B$13219,0),34)"
.Range("e2:e11789").FormulaArray = "=INDEX('MIA
PPRRVU05'!$A$8:$AI$13219,MATCH($G11789&"",'MIA PPRRVU05'!$A$8:$A$13219&'MIA
PPRRVU05'!$B$8:$B$13219,0),32)"
.Range("F2:F11789").FormulaArray = " =INDEX('MIA
PPRRVU05'!$A$8:$AI$13219,MATCH($G11789&"",'MIA PPRRVU05'!$A$8:$A$13219&'MIA
PPRRVU05'!$B$8:$B$13219,0),35)"
End With

End Sub

 

[Sharad Naik]

Hi Dennis,
Then try this:-

Sub ThenTryThis()
Dim i As Integer, strFormula As String
For i = 2 To 9
With Sheet1
.Range(.Cells(4, i), .Cells(6, i)).FormulaArray = _
.Cells(2, i).FormulaArray
strFormula = .Cells(6, i).FormulaArray
.Range(.Cells(4, i), .Cells(11789, i)).FormulaArray = strFormula
End With
Next i
End Sub

Sharad

 

[Guest]

Sharad,

Thanks -- It copied the exact formula, Where do I modify it so it would copy
Match(c4, -- on row 4, c5 on row 5, etc)

Thanks
Dennis G.