Convert formulas (SUMIFS) to VBA code

S

sofas

Active Member
Joined
Sep 11, 2022
Messages
468
Office Version
  1. 2019
Platform
  1. Windows
Code: 
Set WS = Sheet1
 lr = WS.Range("B" & Rows.Count).End(xlUp).row

range("d6:d"&lr")'formula'=IF($B6<>"";TEXT($C6;"DDD");"")

range("C8:C"&lr")'formula'=IF($B8<>"";C6+1;"-")

RANGE E:AM 'formula'
=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;E$5)

=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;F$5)

=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;G$5)

'Up to the AM column'

=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;AM$5)


RANGE E4:AM4 'formula'

range("E4)formula =SUBTOTAL(109;E6:E"&lr)

 column AN formula range("AN6:N"&lr")'formula

=SUM($E6:$AM6)
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
sofas said:
Code: 
Set WS = Sheet1
 lr = WS.Range("B" & Rows.Count).End(xlUp).row

range("d6:d"&lr")'formula'=IF($B6<>"";TEXT($C6;"DDD");"")

range("C8:C"&lr")'formula'=IF($B8<>"";C6+1;"-")

RANGE E:AM 'formula'
=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;E$5)

=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;F$5)

=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;G$5)

'Up to the AM column'

=SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;AM$5)


RANGE E4:AM4 'formula'

range("E4)formula =SUBTOTAL(109;E6:E"&lr)

 column AN formula range("AN6:N"&lr")'formula

=SUM($E6:$AM6)
Click to expand...
Hello. Please help me convert these formats to vba code. Thank you in advance
 
Upvote 0
You seem to be almost there so I think I am missing something.
You seem to be using the formula to look up Sheet1 but then seem to be putting the formula in sheet 1.
Can you please clarify what the formula and lookup sheets are preferably by name.
eg you are putting the formula in Sheet1 E:AM but that includes column J which is part of the lookup (sheet1!$J:$J).

Also everything apart from C starts at 6 while C starts at 8 is this an error ?
 
Upvote 0
Alex Blakenburg said:
You seem to be almost there so I think I am missing something.
You seem to be using the formula to look up Sheet1 but then seem to be putting the formula in sheet 1.
Can you please clarify what the formula and lookup sheets are preferably by name.
eg you are putting the formula in Sheet1 E:AM but that includes column J which is part of the lookup (sheet1!$J:$J).

Also everything apart from C starts at 6 while C starts at 8 is this an error ?
Click to expand...
Hello...

Alex Blakenburg​

Actually, I didn't pay attention. I'm searching in Sheet1 and I want to put the formulas in Sheet2.
Also everything apart from C starts at 6 while C starts at 8 is this an error ? No, it is not an error, but I manually enter the value of the two cells
 
Upvote 0
sofas said:
Hello...

Alex Blakenburg​

Actually, I didn't pay attention. I'm searching in Sheet1 and I want to put the formulas in Sheet2.
Also everything apart from C starts at 6 while C starts at 8 is this an error ? No, it is not an error, but I manually enter the value of the two cells
Click to expand...
Example: sheet2 range("E6) =SUMIFS(sheet1!$J:$J;sheet1!$B:$B;$B6;sheet1!$A:$A;$C6;sheet1!$D:$D;E$5)
 
Upvote 0
See if this works for you.

VBA Code: 
Sub InsertFormulas()
    Dim sumWS As Worksheet, dataWS As Worksheet
    Dim formSumIfs As String
    Dim sumLastRow As Long

    Set sumWS = Worksheets("Sheet2")
    Set dataWS = Worksheets("Sheet1")
  
    With sumWS
        sumLastRow = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("D6:D" & sumLastRow).Formula = "=IF($B6<>"""",TEXT($C6,""DDD""),"""")"
        .Range("C8:C" & sumLastRow).Formula = "=IF($B8<>"""",C6+1,"" - "")"
      
        formSumIfs = Replace("=SUMIFS(sheet1!$J:$J,sheet1!$B:$B,$B6,sheet1!$A:$A,$C6,sheet1!$D:$D,E$5)", _
                        "sheet1", "'" & dataWS.Name & "'")
        .Range("E6:AM" & sumLastRow).Formula = formSumIfs
    End With
End Sub
 
Upvote 0
Alex Blakenburg said:
See if this works for you.

VBA Code: 
Sub InsertFormulas()
    Dim sumWS As Worksheet, dataWS As Worksheet
    Dim formSumIfs As String
    Dim sumLastRow As Long

    Set sumWS = Worksheets("Sheet2")
    Set dataWS = Worksheets("Sheet1")
 
    With sumWS
        sumLastRow = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("D6:D" & sumLastRow).Formula = "=IF($B6<>"""",TEXT($C6,""DDD""),"""")"
        .Range("C8:C" & sumLastRow).Formula = "=IF($B8<>"""",C6+1,"" - "")"
     
        formSumIfs = Replace("=SUMIFS(sheet1!$J:$J,sheet1!$B:$B,$B6,sheet1!$A:$A,$C6,sheet1!$D:$D,E$5)", _
                        "sheet1", "'" & dataWS.Name & "'")
        .Range("E6:AM" & sumLastRow).Formula = formSumIfs
    End With
End Sub
Click to expand...
Very cool. Can we complete the code and add the rest while converting all to values?

VBA Code: 
RANGE E4:AM4 'formula'

range("E4)formula =SUBTOTAL(109;E6:E"&lr)

 column AN formula range("AN6:N"&lr")'formula

=SUM($E6:$AM6)
 
Upvote 0
See how you go with this.
I have only converted E:AM to values. Change the range on both sides of the = if required.

VBA Code: 
Sub InsertFormulas()
    Dim sumWS As Worksheet, dataWS As Worksheet
    Dim formSumIfs As String
    Dim sumLastRow As Long

    Set sumWS = Worksheets("Sheet2")
    Set dataWS = Worksheets("Sheet1")
   
    With sumWS
        sumLastRow = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("D6:D" & sumLastRow).Formula = "=IF($B6<>"""",TEXT($C6,""DDD""),"""")"
        .Range("C8:C" & sumLastRow).Formula = "=IF($B8<>"""",C6+1,"" - "")"
       
        formSumIfs = Replace("=SUMIFS(sheet1!$J:$J,sheet1!$B:$B,$B6,sheet1!$A:$A,$C6,sheet1!$D:$D,E$5)", _
                        "sheet1", "'" & dataWS.Name & "'")
        .Range("E6:AM" & sumLastRow).Formula = formSumIfs
       
        .Range("E4:AM4").Formula = Replace("=SUBTOTAL(109,E6:E~)", "~", sumLastRow)
        .Range("AN6:AN" & sumLastRow).Formula = "=SUM($E6:$AM6)"
       
        ' Convert to Values
        .Range("E6:AM" & sumLastRow).Value = .Range("E6:AM" & sumLastRow).Value         ' <--- Change the range as required
    End With
   
End Sub
 
Upvote 0
Solution
Alex Blakenburg said:
See how you go with this.
I have only converted E:AM to values. Change the range on both sides of the = if required.

VBA Code: 
Sub InsertFormulas()
    Dim sumWS As Worksheet, dataWS As Worksheet
    Dim formSumIfs As String
    Dim sumLastRow As Long

    Set sumWS = Worksheets("Sheet2")
    Set dataWS = Worksheets("Sheet1")
  
    With sumWS
        sumLastRow = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("D6:D" & sumLastRow).Formula = "=IF($B6<>"""",TEXT($C6,""DDD""),"""")"
        .Range("C8:C" & sumLastRow).Formula = "=IF($B8<>"""",C6+1,"" - "")"
      
        formSumIfs = Replace("=SUMIFS(sheet1!$J:$J,sheet1!$B:$B,$B6,sheet1!$A:$A,$C6,sheet1!$D:$D,E$5)", _
                        "sheet1", "'" & dataWS.Name & "'")
        .Range("E6:AM" & sumLastRow).Formula = formSumIfs
      
        .Range("E4:AM4").Formula = Replace("=SUBTOTAL(109,E6:E~)", "~", sumLastRow)
        .Range("AN6:AN" & sumLastRow).Formula = "=SUM($E6:$AM6)"
      
        ' Convert to Values
        .Range("E6:AM" & sumLastRow).Value = .Range("E6:AM" & sumLastRow).Value         ' <--- Change the range as required
    End With
  
End Sub
Click to expand...
Thank you very much. Always creative 👏👏👏
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
266
offthelip
O
J
  • Question
using sumifs function across multiple sheets
Replies
4
Views
242
KRice
K
S
  • Solved
Year from range of cells
Replies
9
Views
399
Fluff
Fluff
J
  • Question
Lookup value in table, return the cells above
Replies
6
Views
408
jgul
J
K
  • Question
Extracting data from 1 column into 2 columns & Mass Formula copying
Replies
1
Views
234
JamesCanale
JamesCanale

Forum statistics

Threads
1,214,393
Messages
6,119,261
Members
448,880
Latest member
aveternik

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