Putting Formula into VBA

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
203
Office Version
  1. 2003 or older
Platform
  1. Windows
I'm actually having problems with a formula disappearing after calculation. I enter the formula and it calculates. Next in the cell is the calculation but no formula. So I would like to put the formula below to be entered, calculated, copied and pasted as the calculation via a Command button. Murphy's Law ensued. Now I'm having problems with the formula in VBA. It is a bit lengthy and I've entered problems into VBA before but this one is giving be a headache. I've tried the Create Macro but all I get is 'Unable to record'. Any help would be appreciated. I have six more formulas just like it.

Excel Formula:
=IF('Group Graphs'!$P$4='Group Graphs'!$R$11,SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$B$12:$C$88"),$P$4))+SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$Q$12:$R$88"),$P$4))+SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$AF$12:$AG$88"),$P$4))+SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$B$12:$C$88"),$R$12))+SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$Q$12:$R$88"),$R$12))+SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$AF$12:$AG$88"),$R$12)),SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$B$12:$C$88"),$P$4))+SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$Q$12:$R$88"),$P$4))+SUMPRODUCT(COUNTIF(INDIRECT("'"&Emps&"'!$AF$12:$AG$88"),$P$4)))
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
try
VBA Code:
Sub sbFormula()
    ActiveCell.FormulaR1C1 = _
        "=IF('Group Graphs'!R4C16='Group Graphs'!R11C18,SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$B$12:$C$88""),R4C16))+SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$Q$12:$R$88""),R4C16))+SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$AF$12:$AG$88""),R4C16))+SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$B$12:$C$88""),R12C18))+SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$Q$12:$R$88" & _
        """),R12C18))+SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$AF$12:$AG$88""),R12C18)),SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$B$12:$C$88""),R4C16))+SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$Q$12:$R$88""),R4C16))+SUMPRODUCT(COUNTIF(INDIRECT(""'""&Emps&""'!$AF$12:$AG$88""),R4C16)))" & _
        ""
End Sub
 
Upvote 0
Solution
Great! What's weird is that I don't see any difference from what you have and what I tried! Must have a displaced comma or bracket somewhere. Sigh. Thank you so much! You've given me something I can learn from.
 
Upvote 0
Glad to help.
Just to record the Macro of copying your formula into some cell, you will get the VBA code.
 
Upvote 0
I tried that using the macro recorder and I keep getting the "Unable to record" error message. Maybe it's my 2003 version.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top