Combine 2 Macro Codes

[Dokat]

Hi,

I have 2 macro codes i need to combine. They are VBA codes for sumifs and a sumif formula. Is there anyway to combine two codes in one macro code. Thanks

Sub SUMIFS()


Dim i As Variant

Dim condition As Range


For i = 8 To 17


Sheets("HDD").Cells(i, 7) = WorksheetFunction.SUMIFS(Sheets("Source").Range("av:av"), Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(i, 5), Sheets("Source").Range("CF:CF"), Sheets("HDD").Cells(i, 6))

Sheets("HDD").Cells(i, 8) = WorksheetFunction.SUMIFS(Sheets("Source").Range("aw:aw"), Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(i, 5), Sheets("Source").Range("CF:CF"), Sheets("HDD").Cells(i, 6))



Next i


End Sub


Sub SUMIF()


Dim i As Variant

Dim condition As Range


For i = 7 To 7


Sheets("HDD").Cells(i, 7) = WorksheetFunction.SUMIF(Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(i, 5), Sheets("Source").Range("av:av"))

Sheets("HDD").Cells(i, 8) = WorksheetFunction.SUMIF(Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(i, 5), Sheets("Source").Range("aw:aw"))


Next i


End Sub

 

[vletm]

Dokat - combine ... like this

Sub SUMIFS()

Dim i As Variant

Sheets("HDD").Cells(7, 7) = WorksheetFunction.SUMIF(Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(7, 5), Sheets("Source").Range("av:av"))

Sheets("HDD").Cells(7, 8) = WorksheetFunction.SUMIF(Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(7, 5), Sheets("Source").Range("aw:aw"))

For i = 8 To 17

Sheets("HDD").Cells(i, 7) = WorksheetFunction.SUMIFS(Sheets("Source").Range("av:av"), Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(i, 5), Sheets("Source").Range("CF:CF"), Sheets("HDD").Cells(i, 6))

Sheets("HDD").Cells(i, 8) = WorksheetFunction.SUMIFS(Sheets("Source").Range("aw:aw"), Sheets("Source").Range("cb:cb"), Sheets("HDD").Cells(i, 5), Sheets("Source").Range("CF:CF"), Sheets("HDD").Cells(i, 6))

Next i

EndSub

 

[Dokat]

Unfortunately it didnt work gave an error message. thanks

 

[vletm]

Dokat
Where error?
Which line?
Did Your 'original two codes' work?
Your '2nd part of code' no need that for i ... next i loop, just edit those i's to 7.

 

[Dokat]

Yes the original two codes worked. I used the i in the second part of the code as to define where the results go. Are you saying if i remove i's from cells and just leave 7 it will know its column number 7? Thanks

 

[NARAYANK991]

Hi ,

Try this :

Sub SUMIFS()
    Const TOTALSROW = 16
    Dim i As Long
  
    With Sheets("HDD")
        .Cells(TOTALSROW, 7) = WorksheetFunction.SUMIF(Sheets("Source").Range("cb:cb"), .Cells(TOTALSROW, 5), Sheets("Source").Range("av:av"))

        .Cells(TOTALSROW, 8) = WorksheetFunction.SUMIF(Sheets("Source").Range("cb:cb"), .Cells(TOTALSROW, 5), Sheets("Source").Range("aw:aw"))

        For i = 1 To 10
            .Cells(TOTALSROW + i, 7) = WorksheetFunction.SUMIFS(Sheets("Source").Range("av:av"), Sheets("Source").Range("cb:cb"), .Cells(TOTALSROW + i, 5), Sheets("Source").Range("CF:CF"), .Cells(TOTALSROW + i, 6))

            .Cells(TOTALSROW + i, 8) = WorksheetFunction.SUMIFS(Sheets("Source").Range("aw:aw"), Sheets("Source").Range("cb:cb"), .Cells(TOTALSROW + i, 5), Sheets("Source").Range("CF:CF"), .Cells(TOTALSROW + i, 6))
        Next i
    End With
End Sub

Narayan

 

[Dokat]

Thank you Narayank...Can you please explain me how the code works?

 

[NARAYANK991]

Hi ,

The code is almost the same as what you posted ; I have only changed the references to suit the data ranges in your file.

Otherwise I have not added / modified / removed anything.

Your totals are in row 16 , which is why I have declared a Constant called TOTALSROW and put it to 16.

Since your output range starts from row 17 , the loop counter i starts from 1 and increments till 10 , since there are 10 companies in your output range.

Since the loop counter starts from 1 , the constant TOTALSROW is added to it to get the row references as 17 , 18 , 19 ,..., 24 , 25 , 26.

The following usage :

With Sheets("HDD")

eliminates the necessity to repeat the sheet reference Sheets("HDD") everywhere within the With ... End With. Instead , we can prefix the range reference with the period symbol .

Thus a reference such as .Range("G30") will actually refer to Sheets("HDD").Range("G30") ; a reference such as .Cells(i,8) will refer to Sheets("HDD").Cells(i,8)

Narayan

 

[Dokat]

That explains...I was wondering why you limited i loops at 10. Its because of the number of companies. So i can technically increase the ceiling from 10 to 40 if i need to. Thank you so much for the clarification.