PDA

View Full Version : Sumif not working



cvilla7174
04-30-2019, 11:34 AM
Hello , I am trying to do a sumif but the problem is what if i try to use the sheet code name it won't work, only if i use the sheet name, here is what i mean by that





sumifmacro Macro
'
'
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('ancma 4-7-2019'!R3C2:R173C2,april!RC9,'ancma 4-7-2019'!R3C5:R173C5)"
Range("E5").Select
End Sub



now if I use the same formula but trying to use the sheet codename it doesn't work why?


Sub mysumif()
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=sumif('sheet56'!R3C2:R173C2,sheet57!RC9,'sheet56'!R3C5:R173C5)"
Range("E5").Select
End Sub


now is there a way to use a variable that I can create to reference either the worksheet name or code name as variables?

keebellah
10-25-2021, 11:09 PM
To reference a worksheet you can add a variable and set it to that worksheet



sumifmacro Macro'
'Dim ws As Worksheet
Set ws = workshets("whatever name") or use Set ws=Sheet1
ws.Range("E4").ActiveCell.FormulaR1C1 = _
"=SUMIF('ancma 4-7-2019'!R3C2:R173C2,april!RC9,'ancma 4-7-2019'!R3C5:R173C5)"
End Sub


DO NOT SELECT just refer to a range

georgiboy
10-27-2021, 02:44 AM
Another way of looking at it might be:


Sub Test()
Dim tmpWS As Worksheet
Dim tmpWS2 As Worksheet
Dim CriteriaRng_ As Range
Dim Criteria_ As String
Dim SumRng_ As Range

' worksheet code name
Set tmpWS = Sheet56
Set tmpWS2 = Sheet57

' worksheet front end name
' Set tmpWS = Sheets("Sheet name here")
' Set tmpWS2 = Sheets("Sheet name here")

With tmpWS
Set CriteriaRng_ = .Range(.Cells(3, 2), .Cells(173, 2))
Set Criteria_ = tmpWS2.Cells(3, 9).Value
Set SumRng_ = .Range(.Cells(3, 5), .Cells(173, 5))
End With

ActiveCell = Application.SumIf(CriteriaRng_, Criteria_, SumRng)

End Sub

Hope this helps