To add a formula from excel to VBA-excel

[GirishDhruva]

Hi Everyone,

I am trying to calculate the difference between two values and multiply with payable days and sum up all those values.

Like,

here is my sample formula for one cell
=+ROUND(((C3-AH3)*BM3)/30,0)+ROUND(((C3-AH3)*BM3)/31,0)+ROUND(((C3-AH3)*BM3)/30,0)+ROUND(((C3-AH3)*BM3)/31,0)+ROUND(((C3-AH3)*BM3)/31,0)+ROUND(((C3-AH3)*BM3)/30,0)+ROUND(((C3-AH3)*BM3)/31,0)+ROUND(((C3-AH3)*BM3)/30,0)+ROUND(((C3-AH3)*BM3)/31,0)+ROUND(((C3-AH3)*BM3)/31,0)+ROUND(((C3-AH3)*BM3)/28,0)+ROUND(((C3-AH3)*BM3)/31,0)

here is my sample workbook
https://www.dropbox.com/s/lrkjhs85j3ep3lj/Sum_MrExcel.xlsm?dl=0

In workbook the highlighted header's Column(A,B,C...) will have the formula but that formula shouldn't be viewed in workbook.

From the above formula i need to calculate for each components(A,B,C....... in attached workbook), so if i find for each component the code will become lengthy,so please guide me how i can use the code in VBA.

Can this be done.

Regards,
Dhruva.

 

[Fluff]

Your formula can be reduced to
=ROUND(((C3-AH3)*BM3)/30,0)*4+ROUND(((C3-AH3)*BM3)/31,0)*7+ROUND(((C3-AH3)*BM3)/28,0)

Do you want a macro to just put the values into the cells, rather than the formula?

 

[GirishDhruva]

if i could have a macro, that would be more helpful.
and instead of manually providing month count, we can give reference from Pay days (Once if u check my work book you will understand)
AND Sorry i Provided wrong formula, below is my correct formula

=+ROUND(((C3-AH3)*BM3)/30,0)+ROUND(((C3-AH3)*BN3)/31,0)+ROUND(((C3-AH3)*BO3)/30,0)+ROUND(((C3-AH3)*BP3)/31,0)+ROUND(((C3-AH3)*BQ3)/31,0)+ROUND(((C3-AH3)*BR3)/30,0)+ROUND(((C3-AH3)*BS3)/31,0)+ROUND(((C3-AH3)*BT3)/30,0)+ROUND(((C3-AH3)*BU3)/31,0)+ROUND(((C3-AH3)*BV3)/31,0)+ROUND(((C3-AH3)*BW3)/28,0)+ROUND(((C3-AH3)*BX3)/31,0)

 

[Fluff]

Can you please answer my question?

 

[GirishDhruva]

Your formula can be reduced to
=ROUND(((C3-AH3)*BM3)/30,0)*4+ROUND(((C3-AH3)*BM3)/31,0)*7+ROUND(((C3-AH3)*BM3)/28,0)

Do you want a macro to just put the values into the cells, rather than the formula?

Sorry and Yes i need a macro just to put the values.

 

[Fluff]

In that case, how about

Sub GirishDhruva()
    With Range("BZ3:DC31")
        .Formula = "=ROUND(((C3-AH3)*BM3)/30,0)+ROUND(((C3-AH3)*BN3)/31,0)+ROUND(((C3-AH3)*BO3)/30,0)+ROUND(((C3-AH3)*BP3)/31,0)+ROUND(((C3-AH3)*BQ3)/31,0)+ROUND(((C3-AH3)*BR3)/30,0)+ROUND(((C3-AH3)*BS3)/31,0)+ROUND(((C3-AH3)*BT3)/30,0)+ROUND(((C3-AH3)*BU3)/31,0)+ROUND(((C3-AH3)*BV3)/31,0)+ROUND(((C3-AH3)*BW3)/28,0)+ROUND(((C3-AH3)*BX3)/31,0)"
        .Value = .Value
    End With
End Sub

 

[GirishDhruva]

Thanks and Sorry to inform you.
i think i have not provided the complete problem.

=+ROUND(((C3-AH3)*$BM3)/30,0)+ROUND(((C3-AH3)*$BN3)/31,0)+ROUND(((C3-AH3)*$BO3)/30,0)+ROUND(((C3-AH3)*$BP3)/31,0)+ROUND(((C3-AH3)*$BQ3)/31,0)+ROUND(((C3-AH3)*$BR3)/30,0)+ROUND(((C3-AH3)*$BS3)/31,0)+ROUND(((C3-AH3)*$BT3)/30,0)+ROUND(((C3-AH3)*$BU3)/31,0)+ROUND(((C3-AH3)*$BV3)/31,0)+ROUND(((C3-AH3)*$BW3)/28,0)+ROUND(((C3-AH3)*$BX3)/31,0)

This formula will not be same for all the cells, it will be changing based on the columns and rows.

Like it should count the total number of values in column "A"(like - lastrow = Cells(Rows.Count, "A").End(xlUp).row) based on that, from columns(C-AF) will contain new values and from columns(AH-BK) will contain Old values and total payable days would be in columns(BM-BX).
so like,
in cell BZ3 my value should calculate with above formula
in cell BZ4 my formula would be :

=+ROUND(((C4-AH4)*$BM4)/30,0)+ROUND(((C4-AH4)*$BN4)/31,0)+ROUND(((C4-AH4)*$BO4)/30,0)+ROUND(((C4-AH4)*$BP4)/31,0)+ROUND(((C4-AH4)*$BQ4)/31,0)+ROUND(((C4-AH4)*$BR4)/30,0)+ROUND(((C4-AH4)*$BS4)/31,0)+ROUND(((C4-AH4)*$BT4)/30,0)+ROUND(((C4-AH4)*$BU4)/31,0)+ROUND(((C4-AH4)*$BV4)/31,0)+ROUND(((C4-AH4)*$BW4)/28,0)+ROUND(((C4-AH4)*$BX4)/31,0)

in cell BZ5 my formula would be:

=+ROUND(((C5-AH5)*$BM5)/30,0)+ROUND(((C5-AH5)*$BN5)/31,0)+ROUND(((C5-AH5)*$BO5)/30,0)+ROUND(((C5-AH5)*$BP5)/31,0)+ROUND(((C5-AH5)*$BQ5)/31,0)+ROUND(((C5-AH5)*$BR5)/30,0)+ROUND(((C5-AH5)*$BS5)/31,0)+ROUND(((C5-AH5)*$BT5)/30,0)+ROUND(((C5-AH5)*$BU5)/31,0)+ROUND(((C5-AH5)*$BV5)/31,0)+ROUND(((C5-AH5)*$BW5)/28,0)+ROUND(((C5-AH5)*$BX5)/31,0)

in similar way it should calculate till lastrow

in cell CA3 my formula should be:

=+ROUND(((D3-AI3)*$BM3)/30,0)+ROUND(((D3-AI3)*$BN3)/31,0)+ROUND(((D3-AI3)*$BO3)/30,0)+ROUND(((D3-AI3)*$BP3)/31,0)+ROUND(((D3-AI3)*$BQ3)/31,0)+ROUND(((D3-AI3)*$BR3)/30,0)+ROUND(((D3-AI3)*$BS3)/31,0)+ROUND(((D3-AI3)*$BT3)/30,0)+ROUND(((D3-AI3)*$BU3)/31,0)+ROUND(((D3-AI3)*$BV3)/31,0)+ROUND(((D3-AI3)*$BW3)/28,0)+ROUND(((D3-AI3)*$BX3)/31,0)

Like the above based on column numbers it should calculate the values.

And sorry if i am still not clear.

Regards,
Dhruva

 

[Fluff]

In that case simply edit the formula in the code I supplied to add the $ signs wherever they need to go.

 

[GirishDhruva]

But my row count will not be same every time..

 

[Fluff]

In that case how about

Sub GirishDhruva()
    With Range("BZ3:DC" & Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=ROUND(((C4-AH4)*$BM4)/30,0)+ROUND(((C4-AH4)*$BN4)/31,0)+ROUND(((C4-AH4)*$BO4)/30,0)+ROUND(((C4-AH4)*$BP4)/31,0)+ROUND(((C4-AH4)*$BQ4)/31,0)+ROUND(((C4-AH4)*$BR4)/30,0)+ROUND(((C4-AH4)*$BS4)/31,0)+ROUND(((C4-AH4)*$BT4)/30,0)+ROUND(((C4-AH4)*$BU4)/31,0)+ROUND(((C4-AH4)*$BV4)/31,0)+ROUND(((C4-AH4)*$BW4)/28,0)+ROUND(((C4-AH4)*$BX4)/31,0)"
        .Value = .Value
    End With
End Sub