Results 1 to 8 of 8

Thread: Using Countifs & Weekdays

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. 05-04-2017, 04:53 AM #6
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by RickS650 View Post
    I have now run the macro recorder on the weekday part and got the correct answer:

    Sub Macro1()
    ' Macro1 Macro
    Range("T2").Select
    Selection.FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"
    Debug.Print Range("T2")
    ' Result is 71 - correct
    End Sub
    You rarely need to select

    Range("T2").FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"
    Quote Originally Posted by RickS650 View Post
    but I would like to put the result into a variable (say x) but, of course, I can't because it is an array. This is now personal between me and this macro..... I WILL win!
    You can evaluate the formula

    x = Application.Evaluate("=SUMPRODUCT(--(WEEKDAY(A2:A100)=2),B2:B100)")
    Last edited by Aussiebear; 04-27-2023 at 12:15 PM. Reason: Adjusted the code tags
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Tags for this Thread

Posting Permissions

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

Forum Rules