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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

J
  • Question
Cannot close temporary file and delete in VBA for Excel coding
Replies
2
Views
490
jolor69
J
S
  • Question
How to change scope (Workbook to Sheet) of Formula Name after duplicated with Sheet?
Replies
8
Views
662
SunnySchindler
S
K
  • Solved
VBA round value to 2 decimals
Replies
3
Views
662
RoryA
RoryA
D
  • Solved
Syntax error - Excel formulas in VBA code
Replies
5
Views
220
kevin9999
K
Guna13
  • Solved
Vlookup and Index Match Formula Calculating Issue - running more than 20 Minutes for 3L lines
Replies
1
Views
596
Anthony47
A

Forum statistics

Threads
1,214,591
Messages
6,120,425
Members
448,961
Latest member
nzskater

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