formula convert to vba function

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
I was wondering this...

I want to possibly change this formula to a function in vba?
I have12 sheets (months) and say 600 rows of data which below formula must act on in each individual sheet.
My thought is, one place only for formula instead of 600*12 places.
When row gets populated e function would be called and place value into cell.

Is this a fair approach/idea ?

Stumbling block is, joining sum product and sum if worksheet functions vba???

=IF(SUMPRODUCT(SUMIF(Data!$A$3:$A$250,Aug!D4:M4,Data!$C$3:$C$250))=0,"",SUMPRODUCT(SUMIF(Data!$A$3:$A$250,Aug!D4:M4,Data!$C$3:$C$250)))
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Too complicated for me to get the macro calculating the value without going through a cell, so my macro would rather paste the value of a dummy cell:

For example
Aug!D4:M4 can be called VarRange using define name in formula tab and call DumCell your dummyCell. So in your dummy cell you have the formula
Code:
[LEFT][COLOR=#333333][FONT=Verdana]=IF(SUMPRODUCT(SUMIF(Data!$A$3:$A$250,VarRange,Data!$C$3:$C$250))=0,"",SUMPRODUCT(SUMIF(Data!$A$3:$A$250,VarRange,Data!$C$3:$C$250)))[/FONT][/COLOR][/LEFT]
and then lets say you want the value in N5 to be calculated from formula Aug!D5:M5:
Code:
ActiveWorkbook.Names("VarRange").RefersToR1C1 = "=Aug!R5C4:R5C13"
[LEFT][COLOR=#222222][FONT=Tahoma]Range("E5")=DumCell.value[/FONT][/COLOR][/LEFT]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,225,190
Messages
6,183,455
Members
453,160
Latest member
DaveM_26

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