To add a formula from excel to VBA-excel

GirishDhruva · Oct 24, 2019

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.

GirishDhruva · Oct 28, 2019

Sorry for late reply.

It only calculates the values which are in 4th row right??????

Regards,
Dhruva.

Fluff · Oct 28, 2019

No it calculates the values in every row, based on the last used cell in col A

GirishDhruva · Oct 29, 2019

Thank you so much full.
You are genius...........!

Regards,
Dhruva

Fluff · Oct 29, 2019

You're welcome & thanks for the feedback

GirishDhruva · Nov 8, 2019

Fluff said:

In that case how about

Code:

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

For the above code can we add a condition that if the value found only in current month(for only the month which cell B1 is mentioned) then only for those cells can we add the below formula, but for the other rows the formula should be same as @Fluff provided.

Code:

=+IF($CV5>0,ROUND((ROUND(AK5*($CV$3-$CV5)/$CV$3,0)+ROUND(D5*$CV5/$CV$3,0))*($CV$3-$BT5)/$CV$3,0)-ROUND((D5*($CV$3-$BT5)/$CV$3),0)+ROUND(AK5*$CG5/$CV$3,0),0)+IF($CW5>0,ROUND((ROUND(AK5*($CW$3-$CW5)/$CW$3,0)+ROUND(D5*$CW5/$CW$3,0))*($CW$3-$BU5)/$CW$3,0)-ROUND((D5*($CW$3-$BU5)/$CW$3),0)+ROUND(AK5*$CH5/$CW$3,0),0)+IF($CX5>0,ROUND((ROUND(AK5*($CX$3-$CX5)/$CX$3,0)+ROUND(D5*$CX5/$CX$3,0))*($CX$3-$BV5)/$CX$3,0)-ROUND((D5*($CX$3-$BV5)/$CX$3),0)+ROUND(AK5*$CI5/$CX$3,0),0)+IF($CY5>0,ROUND((ROUND(AK5*($CY$3-$CY5)/$CY$3,0)+ROUND(D5*$CY5/$CY$3,0))*($CY$3-$BW5)/$CY$3,0)-ROUND((D5*($CY$3-$BW5)/$CY$3),0)+ROUND(AK5*$CJ5/$CY$3,0),0)+IF($CZ5>0,ROUND((ROUND(AK5*($CZ$3-$CZ5)/$CZ$3,0)+ROUND(D5*$CZ5/$CZ$3,0))*($CZ$3-$BX5)/$CZ$3,0)-ROUND((D5*($CZ$3-$BX5)/$CZ$3),0)+ROUND(AK5*$CK5/$CZ$3,0),0)+IF($DA5>0,ROUND((ROUND(AK5*($DA$3-$DA5)/$DA$3,0)+ROUND(D5*$DA5/$DA$3,0))*($DA$3-$BY5)/$DA$3,0)-ROUND((D5*($DA$3-$BY5)/$DA$3),0)+ROUND(AK5*$CL5/$DA$3,0),0)+IF($DB5>0,ROUND((ROUND(AK5*($DB$3-$DB5)/$DB$3,0)+ROUND(D5*$DB5/$DB$3,0))*($DB$3-$BZ5)/$DB$3,0)-ROUND((D5*($DB$3-$BZ5)/$DB$3),0)+ROUND(AK5*$CM5/$DB$3,0),0)+IF($DC5>0,ROUND((ROUND(AK5*($DC$3-$DC5)/$DC$3,0)+ROUND(D5*$DC5/$DC$3,0))*($DC$3-$CA5)/$DC$3,0)-ROUND((D5*($DC$3-$CA5)/$DC$3),0)+ROUND(AK5*$CN5/$DC$3,0),0)+IF($DD5>0,ROUND((ROUND(AK5*($DD$3-$DD5)/$DD$3,0)+ROUND(D5*$DD5/$DD$3,0))*($DD$3-$CB5)/$DD$3,0)-ROUND((D5*($DD$3-$CB5)/$DD$3),0)+ROUND(AK5*$CO5/$DD$3,0),0)+IF($DE5>0,ROUND((ROUND(AK5*($DE$3-$DE5)/$DE$3,0)+ROUND(D5*$DE5/$DE$3,0))*($DE$3-$CC5)/$DE$3,0)-ROUND((D5*($DE$3-$CC5)/$DE$3),0)+ROUND(AK5*$CP5/$DE$3,0),0)+IF($DF5>0,ROUND((ROUND(AK5*($DF$3-$DF5)/$DF$3,0)+ROUND(D5*$DF5/$DF$3,0))*($DF$3-$CD5)/$DF$3,0)-ROUND((D5*($DF$3-$CD5)/$DF$3),0)+ROUND(AK5*$CQ5/$DF$3,0),0)+IF($DG5>0,ROUND((ROUND(AK5*($DG$3-$DG5)/$DG$3,0)+ROUND(D5*$DG5/$DG$3,0))*($DG$3-$CE5)/$DG$3,0)-ROUND((D5*($DG$3-$CE5)/$DG$3),0)+ROUND(AK5*$CR5/$DG$3,0),0)

Regards,
Dhruva.

GirishDhruva · Nov 8, 2019

GirishDhruva said:

For the above code can we add a condition that if the value found only in current month(for only the month which cell B1 is mentioned) then only for those cells can we add the below formula, but for the other rows the formula should be same as @Fluff provided.

Code:

=+IF($CV5>0,ROUND((ROUND(AK5*($CV$3-$CV5)/$CV$3,0)+ROUND(D5*$CV5/$CV$3,0))*($CV$3-$BT5)/$CV$3,0)-ROUND((D5*($CV$3-$BT5)/$CV$3),0)+ROUND(AK5*$CG5/$CV$3,0),0)+IF($CW5>0,ROUND((ROUND(AK5*($CW$3-$CW5)/$CW$3,0)+ROUND(D5*$CW5/$CW$3,0))*($CW$3-$BU5)/$CW$3,0)-ROUND((D5*($CW$3-$BU5)/$CW$3),0)+ROUND(AK5*$CH5/$CW$3,0),0)+IF($CX5>0,ROUND((ROUND(AK5*($CX$3-$CX5)/$CX$3,0)+ROUND(D5*$CX5/$CX$3,0))*($CX$3-$BV5)/$CX$3,0)-ROUND((D5*($CX$3-$BV5)/$CX$3),0)+ROUND(AK5*$CI5/$CX$3,0),0)+IF($CY5>0,ROUND((ROUND(AK5*($CY$3-$CY5)/$CY$3,0)+ROUND(D5*$CY5/$CY$3,0))*($CY$3-$BW5)/$CY$3,0)-ROUND((D5*($CY$3-$BW5)/$CY$3),0)+ROUND(AK5*$CJ5/$CY$3,0),0)+IF($CZ5>0,ROUND((ROUND(AK5*($CZ$3-$CZ5)/$CZ$3,0)+ROUND(D5*$CZ5/$CZ$3,0))*($CZ$3-$BX5)/$CZ$3,0)-ROUND((D5*($CZ$3-$BX5)/$CZ$3),0)+ROUND(AK5*$CK5/$CZ$3,0),0)+IF($DA5>0,ROUND((ROUND(AK5*($DA$3-$DA5)/$DA$3,0)+ROUND(D5*$DA5/$DA$3,0))*($DA$3-$BY5)/$DA$3,0)-ROUND((D5*($DA$3-$BY5)/$DA$3),0)+ROUND(AK5*$CL5/$DA$3,0),0)+IF($DB5>0,ROUND((ROUND(AK5*($DB$3-$DB5)/$DB$3,0)+ROUND(D5*$DB5/$DB$3,0))*($DB$3-$BZ5)/$DB$3,0)-ROUND((D5*($DB$3-$BZ5)/$DB$3),0)+ROUND(AK5*$CM5/$DB$3,0),0)+IF($DC5>0,ROUND((ROUND(AK5*($DC$3-$DC5)/$DC$3,0)+ROUND(D5*$DC5/$DC$3,0))*($DC$3-$CA5)/$DC$3,0)-ROUND((D5*($DC$3-$CA5)/$DC$3),0)+ROUND(AK5*$CN5/$DC$3,0),0)+IF($DD5>0,ROUND((ROUND(AK5*($DD$3-$DD5)/$DD$3,0)+ROUND(D5*$DD5/$DD$3,0))*($DD$3-$CB5)/$DD$3,0)-ROUND((D5*($DD$3-$CB5)/$DD$3),0)+ROUND(AK5*$CO5/$DD$3,0),0)+IF($DE5>0,ROUND((ROUND(AK5*($DE$3-$DE5)/$DE$3,0)+ROUND(D5*$DE5/$DE$3,0))*($DE$3-$CC5)/$DE$3,0)-ROUND((D5*($DE$3-$CC5)/$DE$3),0)+ROUND(AK5*$CP5/$DE$3,0),0)+IF($DF5>0,ROUND((ROUND(AK5*($DF$3-$DF5)/$DF$3,0)+ROUND(D5*$DF5/$DF$3,0))*($DF$3-$CD5)/$DF$3,0)-ROUND((D5*($DF$3-$CD5)/$DF$3),0)+ROUND(AK5*$CQ5/$DF$3,0),0)+IF($DG5>0,ROUND((ROUND(AK5*($DG$3-$DG5)/$DG$3,0)+ROUND(D5*$DG5/$DG$3,0))*($DG$3-$CE5)/$DG$3,0)-ROUND((D5*($DG$3-$CE5)/$DG$3),0)+ROUND(AK5*$CR5/$DG$3,0),0)

Regards,
Dhruva.

Here is my workbook
https://www.dropbox.com/s/cpi0z54dy000zxf/Sum_MrExcel.xlsm?dl=0

In the above workbook, in highlighted cells only i need the new formula to be added

Regards,
Dhruva.

Fluff · Nov 8, 2019

How about

Code:

Sub GirishDhruva()
    Dim Fnd As Range
    Dim UsdRws As Long
    
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    With Range("BZ3:DC" & UsdRws)
        .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)"
        Set Fnd = Range("BM2:BX2").Find(Range("B1").Value, , , xlWhole, , , False, , False)
        If Fnd Is Nothing Then Exit Sub
        Range("BM2:BX" & UsdRws).AutoFilter Fnd.Column - 64, ">0"
        With .SpecialCells(xlVisible)
            .Formula = "=IF($BM16>0,ROUND((ROUND(AH16*($BM$1-$BM16)/$BM$1,0)+ROUND(C16*$BM16/$BM$1,0))*($BM$1-0)/$BM$1,0)-ROUND((C16*($BM$1-0)/$BM$1),0)+ROUND(AH16*0/$BM$1,0),0)+IF($BN16>0,ROUND((ROUND(AH16*($BN$1-$BN16)/$BN$1,0)+ROUND(C16*$BN16/$BN$1,0))*($BN$1-0)/$BN$1,0)-ROUND((C16*($BN$1-0)/$BN$1),0)+ROUND(AH16*0/$BN$1,0),0)+" & _
                "IF($BO16>0,ROUND((ROUND(AH16*($BO$1-$BO16)/$BO$1,0)+ROUND(C16*$BO16/$BO$1,0))*($BO$1-0)/$BO$1,0)-ROUND((C16*($BO$1-0)/$BO$1),0)+ROUND(AH16*0/$BO$1,0),0)+IF($BP16>0,ROUND((ROUND(AH16*($BP$1-$BP16)/$BP$1,0)+ROUND(C16*$BP16/$BP$1,0))*($BP$1-0)/$BP$1,0)-ROUND((C16*($BP$1-0)/$BP$1),0)+ROUND(AH16*0/$BP$1,0),0)+" & _
                "IF($BQ16>0,ROUND((ROUND(AH16*($BQ$1-$BQ16)/$BQ$1,0)+ROUND(C16*$BQ16/$BQ$1,0))*($BQ$1-0)/$BQ$1,0)-ROUND((C16*($BQ$1-0)/$BQ$1),0)+ROUND(AH16*0/$BQ$1,0),0)+IF($BR16>0,ROUND((ROUND(AH16*($BR$1-$BR16)/$BR$1,0)+ROUND(C16*$BR16/$BR$1,0))*($BR$1-0)/$BR$1,0)-ROUND((C16*($BR$1-0)/$BR$1),0)+ROUND(AH16*0/$BR$1,0),0)+" & _
                "IF($BS16>0,ROUND((ROUND(AH16*($BS$1-$BS16)/$BS$1,0)+ROUND(C16*$BS16/$BS$1,0))*($BS$1-0)/$BS$1,0)-ROUND((C16*($BS$1-0)/$BS$1),0)+ROUND(AH16*0/$BS$1,0),0)+IF($BT16>0,ROUND((ROUND(AH16*($BT$1-$BT16)/$BT$1,0)+ROUND(C16*$BT16/$BT$1,0))*($BT$1-0)/$BT$1,0)-ROUND((C16*($BT$1-0)/$BT$1),0)+ROUND(AH16*0/$BT$1,0),0)+" & _
                "IF($BU16>0,ROUND((ROUND(AH16*($BU$1-$BU16)/$BU$1,0)+ROUND(C16*$BU16/$BU$1,0))*($BU$1-0)/$BU$1,0)-ROUND((C16*($BU$1-0)/$BU$1),0)+ROUND(AH16*0/$BU$1,0),0)+IF($BV16>0,ROUND((ROUND(AH16*($BV$1-$BV16)/$BV$1,0)+ROUND(C16*$BV16/$BV$1,0))*($BV$1-0)/$BV$1,0)-ROUND((C16*($BV$1-0)/$BV$1),0)+ROUND(AH16*0/$BV$1,0),0)+" & _
                "IF($BW16>0,ROUND((ROUND(AH16*($BW$1-$BW16)/$BW$1,0)+ROUND(C16*$BW16/$BW$1,0))*($BW$1-0)/$BW$1,0)-ROUND((C16*($BW$1-0)/$BW$1),0)+ROUND(AH16*0/$BW$1,0),0)+IF($BX16>0,ROUND((ROUND(AH16*($BX$1-$BX16)/$BX$1,0)+ROUND(C16*$BX16/$BX$1,0))*($BX$1-0)/$BX$1,0)-ROUND((C16*($BX$1-0)/$BX$1),0)+ROUND(AH16*0/$BX$1,0),0)"
        End With
        ActiveSheet.AutoFilterMode = False
        .Value = .Value
    End With
End Sub

GirishDhruva · Nov 8, 2019

Fluff said:

How about

Code:

Sub GirishDhruva()
    Dim Fnd As Range
    Dim UsdRws As Long
    
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    With Range("BZ3:DC" & UsdRws)
        .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)"
        Set Fnd = Range("BM2:BX2").Find(Range("B1").Value, , , xlWhole, , , False, , False)
        If Fnd Is Nothing Then Exit Sub
        Range("BM2:BX" & UsdRws).AutoFilter Fnd.Column [COLOR=#ff0000]- 64[/COLOR], ">0"
        With .SpecialCells(xlVisible)
            .Formula = "=IF($BM[COLOR=#ff0000]16[/COLOR]>0,ROUND((ROUND(AH[COLOR=#ff0000]16[/COLOR]*($BM$1-$BM[COLOR=#ff0000]16[/COLOR])/$BM$1,0)+ROUND(C[COLOR=#ff0000]16[/COLOR]*$BM16/$BM$1,0))*($BM$1-0)/$BM$1,0)-ROUND((C16*($BM$1-0)/$BM$1),0)+ROUND(AH16*0/$BM$1,0),0)+IF($BN16>0,ROUND((ROUND(AH16*($BN$1-$BN16)/$BN$1,0)+ROUND(C16*$BN16/$BN$1,0))*($BN$1-0)/$BN$1,0)-ROUND((C16*($BN$1-0)/$BN$1),0)+ROUND(AH16*0/$BN$1,0),0)+" & _
                "IF($BO16>0,ROUND((ROUND(AH16*($BO$1-$BO16)/$BO$1,0)+ROUND(C16*$BO16/$BO$1,0))*($BO$1-0)/$BO$1,0)-ROUND((C16*($BO$1-0)/$BO$1),0)+ROUND(AH16*0/$BO$1,0),0)+IF($BP16>0,ROUND((ROUND(AH16*($BP$1-$BP16)/$BP$1,0)+ROUND(C16*$BP16/$BP$1,0))*($BP$1-0)/$BP$1,0)-ROUND((C16*($BP$1-0)/$BP$1),0)+ROUND(AH16*0/$BP$1,0),0)+" & _
                "IF($BQ16>0,ROUND((ROUND(AH16*($BQ$1-$BQ16)/$BQ$1,0)+ROUND(C16*$BQ16/$BQ$1,0))*($BQ$1-0)/$BQ$1,0)-ROUND((C16*($BQ$1-0)/$BQ$1),0)+ROUND(AH16*0/$BQ$1,0),0)+IF($BR16>0,ROUND((ROUND(AH16*($BR$1-$BR16)/$BR$1,0)+ROUND(C16*$BR16/$BR$1,0))*($BR$1-0)/$BR$1,0)-ROUND((C16*($BR$1-0)/$BR$1),0)+ROUND(AH16*0/$BR$1,0),0)+" & _
                "IF($BS16>0,ROUND((ROUND(AH16*($BS$1-$BS16)/$BS$1,0)+ROUND(C16*$BS16/$BS$1,0))*($BS$1-0)/$BS$1,0)-ROUND((C16*($BS$1-0)/$BS$1),0)+ROUND(AH16*0/$BS$1,0),0)+IF($BT16>0,ROUND((ROUND(AH16*($BT$1-$BT16)/$BT$1,0)+ROUND(C16*$BT16/$BT$1,0))*($BT$1-0)/$BT$1,0)-ROUND((C16*($BT$1-0)/$BT$1),0)+ROUND(AH16*0/$BT$1,0),0)+" & _
                "IF($BU16>0,ROUND((ROUND(AH16*($BU$1-$BU16)/$BU$1,0)+ROUND(C16*$BU16/$BU$1,0))*($BU$1-0)/$BU$1,0)-ROUND((C16*($BU$1-0)/$BU$1),0)+ROUND(AH16*0/$BU$1,0),0)+IF($BV16>0,ROUND((ROUND(AH16*($BV$1-$BV16)/$BV$1,0)+ROUND(C16*$BV16/$BV$1,0))*($BV$1-0)/$BV$1,0)-ROUND((C16*($BV$1-0)/$BV$1),0)+ROUND(AH16*0/$BV$1,0),0)+" & _
                "IF($BW16>0,ROUND((ROUND(AH16*($BW$1-$BW16)/$BW$1,0)+ROUND(C16*$BW16/$BW$1,0))*($BW$1-0)/$BW$1,0)-ROUND((C16*($BW$1-0)/$BW$1),0)+ROUND(AH16*0/$BW$1,0),0)+IF($BX16>0,ROUND((ROUND(AH16*($BX$1-$BX16)/$BX$1,0)+ROUND(C16*$BX16/$BX$1,0))*($BX$1-0)/$BX$1,0)-ROUND((C16*($BX$1-0)/$BX$1),0)+ROUND(AH16*0/$BX$1,0),0)"
        End With
        ActiveSheet.AutoFilterMode = False
        .Value = .Value
    End With
End Sub

@Fluff thanks but i have some doubts, (which i have highlighted)

In formula why you are using "16" particularly it might vary based on filters,like in other data if my filtered value starts from 4th row means, the formula would not be calculated properly.
what is that 64 used for.

Regards,
Dhruva.

Fluff · Nov 8, 2019

I just used your formula, try

Code:

Sub GirishDhruva()
    Dim Fnd As Range
    Dim UsdRws As Long
    
    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    With Range("BZ3:DC" & UsdRws)
        .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)"
        Set Fnd = Range("BM2:BX2").Find(Range("B1").Value, , , xlWhole, , , False, , False)
        If Fnd Is Nothing Then Exit Sub
        Range("BM2:BX" & UsdRws).AutoFilter Fnd.Column - 64, ">0"
        With Intersect(ActiveSheet.AutoFilter.Range.Offset(1).Resize(UsdRws - 2).EntireRow, Range("BZ:DC"))
            .Formula = "=IF($BM3>0,ROUND((ROUND(AH3*($BM$1-$BM3)/$BM$1,0)+ROUND(C3*$BM3/$BM$1,0))*($BM$1-0)/$BM$1,0)-ROUND((C3*($BM$1-0)/$BM$1),0)+ROUND(AH3*0/$BM$1,0),0)+IF($BN3>0,ROUND((ROUND(AH3*($BN$1-$BN3)/$BN$1,0)+ROUND(C3*$BN3/$BN$1,0))*($BN$1-0)/$BN$1,0)-ROUND((C3*($BN$1-0)/$BN$1),0)+ROUND(AH3*0/$BN$1,0),0)+" & _
                "IF($BO3>0,ROUND((ROUND(AH3*($BO$1-$BO3)/$BO$1,0)+ROUND(C3*$BO3/$BO$1,0))*($BO$1-0)/$BO$1,0)-ROUND((C3*($BO$1-0)/$BO$1),0)+ROUND(AH3*0/$BO$1,0),0)+IF($BP3>0,ROUND((ROUND(AH3*($BP$1-$BP3)/$BP$1,0)+ROUND(C3*$BP3/$BP$1,0))*($BP$1-0)/$BP$1,0)-ROUND((C3*($BP$1-0)/$BP$1),0)+ROUND(AH3*0/$BP$1,0),0)+" & _
                "IF($BQ3>0,ROUND((ROUND(AH3*($BQ$1-$BQ3)/$BQ$1,0)+ROUND(C3*$BQ3/$BQ$1,0))*($BQ$1-0)/$BQ$1,0)-ROUND((C3*($BQ$1-0)/$BQ$1),0)+ROUND(AH3*0/$BQ$1,0),0)+IF($BR3>0,ROUND((ROUND(AH3*($BR$1-$BR3)/$BR$1,0)+ROUND(C3*$BR3/$BR$1,0))*($BR$1-0)/$BR$1,0)-ROUND((C3*($BR$1-0)/$BR$1),0)+ROUND(AH3*0/$BR$1,0),0)+" & _
                "IF($BS3>0,ROUND((ROUND(AH3*($BS$1-$BS3)/$BS$1,0)+ROUND(C3*$BS3/$BS$1,0))*($BS$1-0)/$BS$1,0)-ROUND((C3*($BS$1-0)/$BS$1),0)+ROUND(AH3*0/$BS$1,0),0)+IF($BT3>0,ROUND((ROUND(AH3*($BT$1-$BT3)/$BT$1,0)+ROUND(C3*$BT3/$BT$1,0))*($BT$1-0)/$BT$1,0)-ROUND((C3*($BT$1-0)/$BT$1),0)+ROUND(AH3*0/$BT$1,0),0)+" & _
                "IF($BU3>0,ROUND((ROUND(AH3*($BU$1-$BU3)/$BU$1,0)+ROUND(C3*$BU3/$BU$1,0))*($BU$1-0)/$BU$1,0)-ROUND((C3*($BU$1-0)/$BU$1),0)+ROUND(AH3*0/$BU$1,0),0)+IF($BV3>0,ROUND((ROUND(AH3*($BV$1-$BV3)/$BV$1,0)+ROUND(C3*$BV3/$BV$1,0))*($BV$1-0)/$BV$1,0)-ROUND((C3*($BV$1-0)/$BV$1),0)+ROUND(AH3*0/$BV$1,0),0)+" & _
                "IF($BW3>0,ROUND((ROUND(AH3*($BW$1-$BW3)/$BW$1,0)+ROUND(C3*$BW3/$BW$1,0))*($BW$1-0)/$BW$1,0)-ROUND((C3*($BW$1-0)/$BW$1),0)+ROUND(AH3*0/$BW$1,0),0)+IF($BX3>0,ROUND((ROUND(AH3*($BX$1-$BX3)/$BX$1,0)+ROUND(C3*$BX3/$BX$1,0))*($BX$1-0)/$BX$1,0)-ROUND((C3*($BX$1-0)/$BX$1),0)+ROUND(AH3*0/$BX$1,0),0)"
        End With
        ActiveSheet.AutoFilterMode = False
        .Value = .Value
    End With
End Sub

GirishDhruva · Nov 8, 2019

@Fluff could you please answer for the above mentioned questions.

To add a formula from excel to VBA-excel

GirishDhruva

Active Member

GirishDhruva

Active Member

Excel Facts

Fluff

MrExcel MVP, Moderator

GirishDhruva

Active Member

Fluff

MrExcel MVP, Moderator

GirishDhruva

Active Member

GirishDhruva

Active Member

Fluff

MrExcel MVP, Moderator

GirishDhruva

Active Member

Fluff

MrExcel MVP, Moderator

GirishDhruva

Active Member

Similar threads

Forum statistics

Share this page

To add a formula from excel to VBA-excel

Active Member

Active Member

Excel Facts

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock