ExcelBanter - Copying array formula to a range of cells...

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Copying array formula to a range of cells... (https://www.excelbanter.com/excel-programming/319719-copying-array-formula-range-cells.html)

Copying array formula to a range of cells...

Hello,

EXCEL 2003 --

I have the following formula:

={index('SHEET1'!$A$1:$AK$12000,MATCH(C2&"",'SHEET 1'!$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.

Copying array formula to a range of cells...

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Copying array formula to a range of cells...

Thanks...

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

"Sharad" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Copying array formula to a range of cells...

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.
-----Original Message-----
Thanks...

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

"Sharad" wrote:

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

*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!

.

Copying array formula to a range of cells...

.FormulaArray = < string representation of formula

will create an array formula.

--
Regards,
Tom Ogilvy

wrote in message
...
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.
-----Original Message-----
Thanks...

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

"Sharad" wrote:

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

*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!

.

Copying array formula to a range of cells...

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("D2:D11789").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

"Tom Ogilvy" wrote:

.FormulaArray = < string representation of formula

will create an array formula.

--
Regards,
Tom Ogilvy

wrote in message
...
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.
-----Original Message-----
Thanks...

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

"Sharad" wrote:

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

*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!

.

Copying array formula to a range of cells...

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

"Dennis G." wrote in message
...
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("D2:D11789").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

"Tom Ogilvy" wrote:

.FormulaArray = < string representation of formula

will create an array formula.

--
Regards,
Tom Ogilvy

wrote in message
...
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.
-----Original Message-----
Thanks...

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

"Sharad" wrote:

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

*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!

.

Copying array formula to a range of cells...

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.

"Sharad Naik" wrote:

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

"Dennis G." wrote in message
...
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("D2:D11789").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

"Tom Ogilvy" wrote:

.FormulaArray = < string representation of formula

will create an array formula.

--
Regards,
Tom Ogilvy

wrote in message
...
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.
-----Original Message-----
Thanks...

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

"Sharad" wrote:

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

*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!

.