CODEWITHSUNDEEP
vbaexcel
Kesko
Kesko

Reputation: 11

VBA SUMIF Sub not executing -

First time here - Soo be nice ;)

What im trying to do: When button is pushed - Adding a sumif formula by using a loop through a range of cells.

Dim wbAdv As Workbook
Sub TranferDataRawToAdv_Click()

    Set wbAdv = ThisWorkbook

    'addHBL
    'addOriginalHBLreq
    addSumIfToCells


End Sub

Sub addSumIfToCells()
    Dim Dept_Row As Long
    Dim Dept_Clm As Long

    Table1 = AdvData.range("L6:L20") 'Needs dynamic lookup
    Dept_Row = AdvData.range("Q6").Row
    Dept_Clm = AdvData.range("Q6").Column
    Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction

    For Each cl In Table1

         wbAdv.Worksheets("Advisering").Cells(Dept_Row, Dept_Clm) = wsFunc.SumIf(range("L:L"), range("L" & Dept_Row), range("N:N"))
         wbAdv.Worksheets("Advisering").Cells(Dept_Row, Dept_Clm + 1) = wsFunc.SumIf(range("L:L"), range("L" & Dept_Row), range("O:O"))
         wbAdv.Worksheets("Advisering").Cells(Dept_Row, Dept_Clm + 2) = wsFunc.SumIf(range("L:L"), range("L" & Dept_Row), range("P:P"))

         Dept_Row = Dept_Row + 1

     Next cl

End Sub

If I run the code by pressing F5 - it works an add the formula to the cells. But when clicked it only adds a 0.

Any help ideas ?

Thanks

Upvotes: 0

Views: 122

Answers (2)

Kesko
Kesko

Reputation: 11

First of: THANKS ALOT for the quick replies

I did the following:

Dim wbAdv As Workbook
Sub TranferDataRawToAdv_Click()

    Set wbAdv = ActiveWorkbook

    'addHBL
    'addOriginalHBLreq
    addSumIfToCells


End Sub


Sub addSumIfToCells()
    Dim Dept_Row As Long
    Dim Dept_Clm As Long

    Table1 = wbAdv.Worksheets("Advisering").range("L6:L1000") 'Needs dynamic lookup
    Dept_Row = wbAdv.Worksheets("Advisering").range("Q6").Row
    Dept_Clm = wbAdv.Worksheets("Advisering").range("Q6").Column
    Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction

    For Each cl In Table1

         wbAdv.Worksheets("Advisering").Cells(Dept_Row, Dept_Clm) = wsFunc.SumIf(wbAdv.Worksheets("Advisering").range("L:L"), wbAdv.Worksheets("Advisering").range("L" & Dept_Row), wbAdv.Worksheets("Advisering").range("N:N"))
         wbAdv.Worksheets("Advisering").Cells(Dept_Row, Dept_Clm + 1) = wsFunc.SumIf(wbAdv.Worksheets("Advisering").range("L:L"), wbAdv.Worksheets("Advisering").range("L" & Dept_Row), wbAdv.Worksheets("Advisering").range("O:O"))
         wbAdv.Worksheets("Advisering").Cells(Dept_Row, Dept_Clm + 2) = wsFunc.SumIf(wbAdv.Worksheets("Advisering").range("L:L"), wbAdv.Worksheets("Advisering").range("L" & Dept_Row), wbAdv.Worksheets("Advisering").range("P:P"))

         Dept_Row = Dept_Row + 1

     Next cl

End Sub

And it worked ! ;)

Upvotes: 0

Dubison
Dubison

Reputation: 768

you need to identify which worksheet you are addressing for ranges in the wsFunc.SumIf function.

wsFunc.SumIf(sheets("????").range("L:L"), sheets("????").range("L" & Dept_Row), _
sheets("????").range("P:P"))

UPDATE:

I just noticed that answer already given by @JEEPED as comment for original post. Sorry for repeating.

Upvotes: 1

Related Questions