Sum Formula in VBA with Variables

[StuartWhi]

Hi Experts,

Sorry this one has me stumped and I’m sure it’s simple,

I would like to sum a column which has been created via VBA selecting different sections so the column “letter” it’s in and the length or number of rows will vary. Due to this I’m using the variables from the VBA code within the formula to be placed in the cell.
Formula assuming no variables,
[f22] =SUM(F7:F21)
Formula using variables,

Cells(MainCount + 7, ColCountCop - 1).Formula = "=sum(" & Range(Cells(7, ColCountCop - 1), Cells(MainCount + 6, ColCountCop - 1)) & ")"

The cell selection is fine,
- The error is, Run-time error ‘13’: Type mismatch
- I’m not able to see what’s wrong with it…
As mentioned the values of MainCount and ColCountCop will change depending on user selection, but in this test case they are MainCount = 15, ColCountCop = 7.

I assume it’s related to the “Range(Cells( x, y)….” with the variables x and y not being in a format that a formula is expecting i.e.,
- This would be setting only number but range formulas require cell references to be letters and numbers format.

I look forward to any ideas you guys might have…

Thanks,

Stuart.

 

[pgc01]

Hi Stuart

Try:

Cells(MainCount + 7, ColCountCop - 1).Formula = "=sum(" & Range(Cells(7, ColCountCop - 1), Cells(MainCount + 6, ColCountCop - 1))[B][COLOR=#ff0000].Address[/COLOR][/B] & ")"

 

[StuartWhi]

Hi Stuart

Try:

Cells(MainCount + 7, ColCountCop - 1).Formula = "=sum(" & Range(Cells(7, ColCountCop - 1), Cells(MainCount + 6, ColCountCop - 1))[B][COLOR=#ff0000].Address[/COLOR][/B] & ")"

Hi pgc01,

Thanks a million works a treat.
- Looking at "addresses" in help it appears to be used in a formula to reference row and columns by a number, so as it's placed above in the VBA section not the formula section does it convert the row column number reference back to letter number format?

If you have time to update me it would be great if not no issue it's working great.

Stuart.

 

[pgc01]

You're welcome. Thanks for the feedback

... as it's placed above in the VBA section not the formula section does it convert the row column number reference back to letter number format?

To see what Address returns, execute this statement when the variables are already initialised:

MsgBox Range(Cells(7, ColCountCop - 1), Cells(MainCount + 6, ColCountCop - 1)).Address</pre>

If you still have doubts post back.

 

[StuartWhi]

Hi PGC,

Thanks I should have thought to do that, interesting to see it uses a locked cell location.

Regards,

Stuart.

 

[pgc01]

... interesting to see it uses a locked cell location.

Hi Stuart

It does not necessarily use a locked cell location.
If you check the help for the Address property of the Range object you'll see that that's just the default.
Address accepts several parameters, that let you define the options you want for the address format.

Please check.