Consulting

Results 1 to 3 of 3

Thread: Sumif not working

  1. #1

    Sumif not working

    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?
    Last edited by Aussiebear; 04-27-2023 at 12:25 PM. Reason: Reduced the whitespace

  2. #2
    VBAX Regular keebellah's Avatar
    Joined
    May 2011
    Location
    The Netherlands
    Posts
    19
    Location
    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
    Cheers,
    Hans
    "May the code be with you ... If it isn't... start debugging!"
    www.hcandts.com

  3. #3
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,146
    Location
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •