VB formulae that adds variable number of cells

[ackey]

Afternoon all

I have a problem with inserting a formula using VBA, which is part of a bigger project

If I was to do this manually, the spreadsheet would look like the following

A1 B1 C1 D1 E1
=b1+c1+d1+e1 Data1 Data2 Data3 Data4 etc

The issue I have is that the number of columns of data is variable. I cannot use SUM as later on in the code I will insert extra columns. I have no problem writing code to calculate the number of columns that would need to be added (and assign that to a variable), however, I am unable to work out how to write the simple excel formula within VB that only adds up the actual number of columns that have data

Thanks in advance

Simon

 

[Scott Huish]

I understand you are inserting columns, but why can't you use SUM.

If your are summing B1:E1 and then insert 2 columns after column B, wouldn't you then want it to sum B1:G1?

Please clarify.

 

[Norie]

Simon

I agree with HOTPEPPER.

Why can't you use SUM?

 

[ackey]

I cannot use SUM as the extra columns I am inserting are inbetween B1 and E1 in this example, and I do not want to include them in the calculation

 

[Scott Huish]

Do you actually need to insert a formula in the cell or could your VBA code write the result to the cell before it inserts the columns?