formula in VB not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several columns of figures with totals at the bottom. The leftmost
column is the grand total. I need to calculate what percentages the subtotals
are of the grand total.
This code sums the column, and works fine:
Dim per1 As String
per1 = "=sum(" & r3 & ":" & r4 & ")"
ActiveCell.formula = per1

This code 'should' calculate the percentage, but the debugger sticks on the
final line:
Dim per2 As String
per2 = "=sum(" & r3 & ":" & r4 & ")/(=sum(" & r1 & ":" & r2 & ")/100)"
ActiveCell.formula = per2

What have I done?
 
per2 = "=sum(" & r3 & ":" & r4 & ")/(sum(" & r1 & ":" & r2 & ")/100)"

That is, no "=" sign before the 2nd Sum. Of course I don't know if your r1
and r1 are right.
You can always test the formula by entering it on the worksheet first and
then read it in VBA with the .Formula property
 
Hi Niek. Thanks. That's fixed it!

Niek Otten said:
per2 = "=sum(" & r3 & ":" & r4 & ")/(sum(" & r1 & ":" & r2 & ")/100)"

That is, no "=" sign before the 2nd Sum. Of course I don't know if your r1
and r1 are right.
You can always test the formula by entering it on the worksheet first and
then read it in VBA with the .Formula property
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

writing formulas in VB - referring to the values contained in cell 4
Help in code Steve G wrote to move data from 4 columns to 21 columns 9
Start on select cell 2
Union only works for sometimes. 3
Array Formula 11
Private Sub Worksheet_Change doesn't work? 2
% of subtotal needed in pivot table 2
Open Another File to Search and Replace Text 1

Back
Top