I'm fairly good with handling data through .Value, but for this specific case I need to work through .Formula, and I'm struggling.

The basic concept is as follows:

1) Fill all needed cells with =formul() (formul being a user defined function)

2) if I need to add an item there, I check if the right two characters of the .formula property match "()"

a) If it does, it's the first item I need to add, and I replace the last ")" with raming!J104) (raming is the name of the tab, column J is fixed, the row number is what I'm filling in)

b) If it doesn't match, it's not the first, and I need to add ;"+";raming!J104)

So the cell contents should be like:

=formul()

=formul(raming!J104)

=formul(raming!J104;"+";raming!J108)

Given the fact I need to force the quotation marks in there, how would I build that from the .formula property? And can I work through 'Replace()' to do so?

EDIT:

I just realised I have a second such problem. I have a variating (but trackable) number of non-consecutive colums of which I have to make a sum of the values.

Assume I need, for each row, to make the sum of the elements in columns 10,13,16,19,.. (so basically 7+3*i, with i an integer ranging from 1 to the amount of 'chapters' I'm dealing with. How would I go and create the formula for each row? (FormulaR1C1 and Formula are both fine, though I don't know if the R1C1 format allows you to used variables within)

The basic concept is as follows:

1) Fill all needed cells with =formul() (formul being a user defined function)

2) if I need to add an item there, I check if the right two characters of the .formula property match "()"

a) If it does, it's the first item I need to add, and I replace the last ")" with raming!J104) (raming is the name of the tab, column J is fixed, the row number is what I'm filling in)

b) If it doesn't match, it's not the first, and I need to add ;"+";raming!J104)

So the cell contents should be like:

=formul()

=formul(raming!J104)

=formul(raming!J104;"+";raming!J108)

Given the fact I need to force the quotation marks in there, how would I build that from the .formula property? And can I work through 'Replace()' to do so?

EDIT:

I just realised I have a second such problem. I have a variating (but trackable) number of non-consecutive colums of which I have to make a sum of the values.

Assume I need, for each row, to make the sum of the elements in columns 10,13,16,19,.. (so basically 7+3*i, with i an integer ranging from 1 to the amount of 'chapters' I'm dealing with. How would I go and create the formula for each row? (FormulaR1C1 and Formula are both fine, though I don't know if the R1C1 format allows you to used variables within)

Last edited: