Get Formula Range in a Cell

[dluhut]

Hello,

Say I have a formula in A1 as

Please Login or Register  to view this content.

In Excel VBA, how can I extract those ranges? Which is

A2:C100,B8,C10,A1-:B10,C1,C2,C3,C4,E10,E20,E30

In the above case?

[leelnich]

Hi dluhut- This macro uses the DirectPrecedents method to extract the addresses of any cells the ActiveCell depends on.
In this example, it just shows them in a msgbox.

Please Login or Register  to view this content.

NOTE: This approach has limitations. DirectPrecedents only finds cells on the ActiveSheet. Ranges on other Sheets are missed.
Also, if a formula uses G1 and G2 separately, DirectPrecedents shows them as one range G1:G2.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[dluhut]

Thanks leelnich!

But when I change Range("A1") to Range("AQ398") for example, it didn't give me any result.

FYI, Range("AQ398").Formula = SUMIFS($Z398:$AP398,$Z$5:$AP$5, AQ$5)

[leelnich]

I just updated the code (02:45 pm) to act on the currently selected cell ("ActiveCell"). Try that, and read the notes at the bottom. It's NOT perfect!

[mehmetcik]

Try this Userdefined Function.


Use Formula "=ExtractRanges(A1)" where A1 is A string or a cell address

Please Login or Register  to view this content.