VBA to calculate value of the countifs formula and paste it directly into a cell

  • Hi


    Please bear with me I am a novice.


    I want to be able to simply paste the calculated value of the below countifs formula into a new cell on another worksheet in the same workbook.


    The formula I started looks at a column of a large number of dates and then calculates the number of dates that are within the current month (i.e. February).


    Is there a simple way of taking the calculated value of the formula and pasting it directly into a cell on another worksheet where I want it.



    Code
    1. Dim i As Long
    2. With Sheets("SearchResults")
    3. i = WorksheetFunction.CountIfs(.Columns("J:J"), ">=" & WorksheetFunction.EoMonth(Date, -1) + 1, .Columns("J:J"), "<=" & WorksheetFunction.EoMonth(Date, 0))
    4. End With
  • Re: VBA to calculate value of the countifs formula and paste it directly into a cell


    I dont think you need a "Paste" function. You simply set the cell value to the calculated result you have. I presume that the variable "i" contains your result so it would look like:

    Code
    1. Dim i As Long
    2. With Sheets("SearchResults")
    3. i = WorksheetFunction.CountIfs(.Columns("J:J"), ">=" & WorksheetFunction.EoMonth(Date, -1) + 1, .Columns("J:J"), "<=" & WorksheetFunction.EoMonth(Date, 0))
    4. End With
    5. worksheets("YourNewWorkSheet").range("A5").value = i 'adjust sheet name and range to suit yourself.


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________