Return Arguments in cell formula as an array

rougean

New Member
Joined
Oct 11, 2011
Messages
5
I am having trouble finding a built in function the will allow me to collect a formula from a cell as an array of its arguments.

For example, is a cell has "=sum(A1,G4,L3)", i want an array of 3 with values A1, G4, L3.

My goal is to be able to change each argument as i please and return the new formula to the sheet.

Can anyone help with this?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

I don't think there is such a built-in function. You would need VBA.
 

rougean

New Member
Joined
Oct 11, 2011
Messages
5
Hi! Thanks for your quick response.

You are saying that there are no vba functions to do this and i would have to build one myself, correct?

I'm not too sure from the way you phrased your repsonse. Maybe i didn't ask a good enough question.

I want to make the changes to the cell formula from vba. I am hoping that there is a vba function to do this.

I know the range object has a formula and formulaarray property but none of these will do the job.

Is there any vba function that i can use to accomplish this task?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
To populate your array you would need to parse the formula. The Split function may help you do that.
 

Forum statistics

Threads
1,136,633
Messages
5,676,896
Members
419,657
Latest member
ExcelAl1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top