Get the Range where Array Formula was defined

[lao_tseng]

Hi EXCEL veterans,

I want to learn (by using EXCEL Visual Basic):

(1) How do I get all the ranges where array formulas were defined?
(2) How do I modify those array formulae and put them back as array
formulae too?

Thank you in advance.

Shyang-Wen

[Prashant]

Hello Lao
Please Put Your Required Data in A range suppose in A2.

A
1 Client 
2  ICNEZT3001S4IN20060519-WO-022-BSHNAG001



Now to go to Vba Editor and paste this formula in Module.As it is
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Sub Count_and_Read_Range()
Dim Counter As Integer
Dim curCell As Variant
For Counter = 2 To 20
Set curCell = Worksheets("Sheet1").Cells(Counter, 1)
MsgBox curCell.FormulaLocal
If curCell = "" Then Counter = 20
Next Counter
End Sub
Sub Select_Range_and_apply_formula()
'''we are putting formula =right(a2,10)
'''''syntax-->ActiveCell.FormulaR1C1 = "YOUR FORMULA"
Sheets("sheet1").Range("C2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],10)"
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

[Prashant]

In range A1 to A2 Suppose u have a value,Like
A
Client 
2  ICNEZT3001S4IN20060519-WO-022-BSHNAG001



Type the code in Module As following
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit
Sub Count_and_Read_Range()
Dim Counter As Integer
Dim curCell As Variant
For Counter = 2 To 20
Set curCell = Worksheets("Sheet1").Cells(Counter, 1)
MsgBox curCell.FormulaLocal
If curCell = "" Then Counter = 20
Next Counter
End Sub
Sub Select_Range_and_apply_formula()
'''we are putting formula =right(a2,10)
'''''syntax-->ActiveCell.FormulaR1C1 = "YOUR FORMULA"
Sheets("sheet1").Range("C2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],10)"
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''





On Apr 15, 4:21 am, lao_tseng <sts...@bastiontechnologies.com> wrote: