Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Copying array formula to a range of cells...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 123
Default 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!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default 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!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default 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!

.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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!

.



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default 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!

.




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 212
Default 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!

.






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default 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!

.







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Redefine range of cells, array formula returns NA Ashley Excel Discussion (Misc queries) 4 February 24th 07 08:54 PM
Problem copying formula to range of cells Bob DePass Setting up and Configuration of Excel 0 July 22nd 05 02:09 AM
Copying an Array Formula montagu Excel Discussion (Misc queries) 1 July 15th 05 07:30 PM
Copying an array formula... Dennis G. Excel Worksheet Functions 12 January 3rd 05 02:49 AM
Quick way of copying Range to (sub)Array ? John Mitchell Excel Programming 2 September 16th 04 03:43 AM


All times are GMT +1. The time now is 04:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement