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!
.