To add a formula from excel to VBA-excel

G

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
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.
 
Last edited:
Sort by date Sort by votes

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
No it calculates the values in every row, based on the last used cell in col A
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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
Click to expand...

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.
 
Upvote 0
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.
Click to expand...

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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
Click to expand...
@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.
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Disabling All VBA code from worksheet and/or workbook
Replies
2
Views
336
hazmat
H
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
346
Trixterz
T
J
  • Question Question
Cannot close temporary file and delete in VBA for Excel coding
Replies
2
Views
732
jolor69
J
S
  • Question Question
How to change scope (Workbook to Sheet) of Formula Name after duplicated with Sheet?
Replies
8
Views
1K
SunnySchindler
S
K
  • Solved
VBA round value to 2 decimals
Replies
3
Views
819
RoryA
RoryA

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

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