Loop through rows and columns in range and apply formula [on hold]












0












$begingroup$


I am needing to apply a countifs formula on each cell in range that includes rows and columns. I am able to make it work if it is for a specific range of rows within one column but not sure about how to apply the same to multiple columns without having to write it out like i have.



My current code is below.



Sub CountIfsFormula2()
Dim lstrow As Long
Dim i As Long

Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.Calculation = xlCalculationManual

lstrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lstrow
Range("C" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C,Sheet1!R1C3,'Agent_Detail_Data'!C[1],"">=""&Sheet1!RC[-1],'Agent_Detail_Data'!C[1],""<""&Sheet1!R[1]C[-1],'Agent_Detail_Data'!C[11],Sheet1!R1C1)"
Range("D" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C[-1],Sheet1!R1C4,'Agent_Detail_Data'!C,"">=""&Sheet1!RC[-2],'Agent_Detail_Data'!C,""<""&Sheet1!R[1]C[-2],'Agent_Detail_Data'!C[10],Sheet1!R1C1)"
Range("E" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C5,'Agent_Detail_Data'!C[-1],"">=""&Sheet1!RC[-3],'Agent_Detail_Data'!C[-1],""<""&Sheet1!R[1]C[-3],'Agent_Detail_Data'!C[9],Sheet1!R1C1)"
Range("F" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C6,'Agent_Detail_Data'!C[-2],"">=""&Sheet1!RC[-4],'Agent_Detail_Data'!C[-2],""<""&Sheet1!R[1]C[-4],'Agent_Detail_Data'!C[8],Sheet1!R1C1)"
Range("G" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C7,'Agent_Detail_Data'!C[-3],"">=""&Sheet1!RC[-5],'Agent_Detail_Data'!C[-3],""<""&Sheet1!R[1]C[-5],'Agent_Detail_Data'!C[7],Sheet1!R1C1)"
Range("H" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C8,'Agent_Detail_Data'!C[-4],"">=""&Sheet1!RC[-6],'Agent_Detail_Data'!C[-4],""<""&Sheet1!R[1]C[-6],'Agent_Detail_Data'!C[6],Sheet1!R1C1)"
Range("I" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C9,'Agent_Detail_Data'!C[-5],"">=""&Sheet1!RC[-7],'Agent_Detail_Data'!C[-5],""<""&Sheet1!R[1]C[-7],'Agent_Detail_Data'!C[5],Sheet1!R1C1)"
Range("J" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C10,'Agent_Detail_Data'!C[-6],"">=""&Sheet1!RC[-8],'Agent_Detail_Data'!C[-6],""<""&Sheet1!R[1]C[-8],'Agent_Detail_Data'!C[4],Sheet1!R1C1)"

Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.Calculation = xlCalculationAutomatic

Exit Sub
End Sub


Apologies if this has been asked before, i haven't been able to find the answer. Also, i hope i have framed the question correctly.



Many thanks,



A










share|improve this question









New contributor




AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.







$endgroup$



put on hold as off-topic by Comintern, Jamal 21 mins ago


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – Jamal

If this question can be reworded to fit the rules in the help center, please edit the question.


















    0












    $begingroup$


    I am needing to apply a countifs formula on each cell in range that includes rows and columns. I am able to make it work if it is for a specific range of rows within one column but not sure about how to apply the same to multiple columns without having to write it out like i have.



    My current code is below.



    Sub CountIfsFormula2()
    Dim lstrow As Long
    Dim i As Long

    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    Application.Calculation = xlCalculationManual

    lstrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To lstrow
    Range("C" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C,Sheet1!R1C3,'Agent_Detail_Data'!C[1],"">=""&Sheet1!RC[-1],'Agent_Detail_Data'!C[1],""<""&Sheet1!R[1]C[-1],'Agent_Detail_Data'!C[11],Sheet1!R1C1)"
    Range("D" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C[-1],Sheet1!R1C4,'Agent_Detail_Data'!C,"">=""&Sheet1!RC[-2],'Agent_Detail_Data'!C,""<""&Sheet1!R[1]C[-2],'Agent_Detail_Data'!C[10],Sheet1!R1C1)"
    Range("E" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C5,'Agent_Detail_Data'!C[-1],"">=""&Sheet1!RC[-3],'Agent_Detail_Data'!C[-1],""<""&Sheet1!R[1]C[-3],'Agent_Detail_Data'!C[9],Sheet1!R1C1)"
    Range("F" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C6,'Agent_Detail_Data'!C[-2],"">=""&Sheet1!RC[-4],'Agent_Detail_Data'!C[-2],""<""&Sheet1!R[1]C[-4],'Agent_Detail_Data'!C[8],Sheet1!R1C1)"
    Range("G" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C7,'Agent_Detail_Data'!C[-3],"">=""&Sheet1!RC[-5],'Agent_Detail_Data'!C[-3],""<""&Sheet1!R[1]C[-5],'Agent_Detail_Data'!C[7],Sheet1!R1C1)"
    Range("H" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C8,'Agent_Detail_Data'!C[-4],"">=""&Sheet1!RC[-6],'Agent_Detail_Data'!C[-4],""<""&Sheet1!R[1]C[-6],'Agent_Detail_Data'!C[6],Sheet1!R1C1)"
    Range("I" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C9,'Agent_Detail_Data'!C[-5],"">=""&Sheet1!RC[-7],'Agent_Detail_Data'!C[-5],""<""&Sheet1!R[1]C[-7],'Agent_Detail_Data'!C[5],Sheet1!R1C1)"
    Range("J" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C10,'Agent_Detail_Data'!C[-6],"">=""&Sheet1!RC[-8],'Agent_Detail_Data'!C[-6],""<""&Sheet1!R[1]C[-8],'Agent_Detail_Data'!C[4],Sheet1!R1C1)"

    Next i

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
    Application.Calculation = xlCalculationAutomatic

    Exit Sub
    End Sub


    Apologies if this has been asked before, i haven't been able to find the answer. Also, i hope i have framed the question correctly.



    Many thanks,



    A










    share|improve this question









    New contributor




    AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.







    $endgroup$



    put on hold as off-topic by Comintern, Jamal 21 mins ago


    This question appears to be off-topic. The users who voted to close gave this specific reason:


    • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – Jamal

    If this question can be reworded to fit the rules in the help center, please edit the question.
















      0












      0








      0





      $begingroup$


      I am needing to apply a countifs formula on each cell in range that includes rows and columns. I am able to make it work if it is for a specific range of rows within one column but not sure about how to apply the same to multiple columns without having to write it out like i have.



      My current code is below.



      Sub CountIfsFormula2()
      Dim lstrow As Long
      Dim i As Long

      Application.DisplayAlerts = False
      Application.AskToUpdateLinks = False
      Application.Calculation = xlCalculationManual

      lstrow = Cells(Rows.Count, "B").End(xlUp).Row
      For i = 2 To lstrow
      Range("C" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C,Sheet1!R1C3,'Agent_Detail_Data'!C[1],"">=""&Sheet1!RC[-1],'Agent_Detail_Data'!C[1],""<""&Sheet1!R[1]C[-1],'Agent_Detail_Data'!C[11],Sheet1!R1C1)"
      Range("D" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C[-1],Sheet1!R1C4,'Agent_Detail_Data'!C,"">=""&Sheet1!RC[-2],'Agent_Detail_Data'!C,""<""&Sheet1!R[1]C[-2],'Agent_Detail_Data'!C[10],Sheet1!R1C1)"
      Range("E" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C5,'Agent_Detail_Data'!C[-1],"">=""&Sheet1!RC[-3],'Agent_Detail_Data'!C[-1],""<""&Sheet1!R[1]C[-3],'Agent_Detail_Data'!C[9],Sheet1!R1C1)"
      Range("F" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C6,'Agent_Detail_Data'!C[-2],"">=""&Sheet1!RC[-4],'Agent_Detail_Data'!C[-2],""<""&Sheet1!R[1]C[-4],'Agent_Detail_Data'!C[8],Sheet1!R1C1)"
      Range("G" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C7,'Agent_Detail_Data'!C[-3],"">=""&Sheet1!RC[-5],'Agent_Detail_Data'!C[-3],""<""&Sheet1!R[1]C[-5],'Agent_Detail_Data'!C[7],Sheet1!R1C1)"
      Range("H" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C8,'Agent_Detail_Data'!C[-4],"">=""&Sheet1!RC[-6],'Agent_Detail_Data'!C[-4],""<""&Sheet1!R[1]C[-6],'Agent_Detail_Data'!C[6],Sheet1!R1C1)"
      Range("I" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C9,'Agent_Detail_Data'!C[-5],"">=""&Sheet1!RC[-7],'Agent_Detail_Data'!C[-5],""<""&Sheet1!R[1]C[-7],'Agent_Detail_Data'!C[5],Sheet1!R1C1)"
      Range("J" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C10,'Agent_Detail_Data'!C[-6],"">=""&Sheet1!RC[-8],'Agent_Detail_Data'!C[-6],""<""&Sheet1!R[1]C[-8],'Agent_Detail_Data'!C[4],Sheet1!R1C1)"

      Next i

      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      Application.AskToUpdateLinks = True
      Application.Calculation = xlCalculationAutomatic

      Exit Sub
      End Sub


      Apologies if this has been asked before, i haven't been able to find the answer. Also, i hope i have framed the question correctly.



      Many thanks,



      A










      share|improve this question









      New contributor




      AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.







      $endgroup$




      I am needing to apply a countifs formula on each cell in range that includes rows and columns. I am able to make it work if it is for a specific range of rows within one column but not sure about how to apply the same to multiple columns without having to write it out like i have.



      My current code is below.



      Sub CountIfsFormula2()
      Dim lstrow As Long
      Dim i As Long

      Application.DisplayAlerts = False
      Application.AskToUpdateLinks = False
      Application.Calculation = xlCalculationManual

      lstrow = Cells(Rows.Count, "B").End(xlUp).Row
      For i = 2 To lstrow
      Range("C" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C,Sheet1!R1C3,'Agent_Detail_Data'!C[1],"">=""&Sheet1!RC[-1],'Agent_Detail_Data'!C[1],""<""&Sheet1!R[1]C[-1],'Agent_Detail_Data'!C[11],Sheet1!R1C1)"
      Range("D" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C[-1],Sheet1!R1C4,'Agent_Detail_Data'!C,"">=""&Sheet1!RC[-2],'Agent_Detail_Data'!C,""<""&Sheet1!R[1]C[-2],'Agent_Detail_Data'!C[10],Sheet1!R1C1)"
      Range("E" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C5,'Agent_Detail_Data'!C[-1],"">=""&Sheet1!RC[-3],'Agent_Detail_Data'!C[-1],""<""&Sheet1!R[1]C[-3],'Agent_Detail_Data'!C[9],Sheet1!R1C1)"
      Range("F" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C6,'Agent_Detail_Data'!C[-2],"">=""&Sheet1!RC[-4],'Agent_Detail_Data'!C[-2],""<""&Sheet1!R[1]C[-4],'Agent_Detail_Data'!C[8],Sheet1!R1C1)"
      Range("G" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C7,'Agent_Detail_Data'!C[-3],"">=""&Sheet1!RC[-5],'Agent_Detail_Data'!C[-3],""<""&Sheet1!R[1]C[-5],'Agent_Detail_Data'!C[7],Sheet1!R1C1)"
      Range("H" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C8,'Agent_Detail_Data'!C[-4],"">=""&Sheet1!RC[-6],'Agent_Detail_Data'!C[-4],""<""&Sheet1!R[1]C[-6],'Agent_Detail_Data'!C[6],Sheet1!R1C1)"
      Range("I" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C9,'Agent_Detail_Data'!C[-5],"">=""&Sheet1!RC[-7],'Agent_Detail_Data'!C[-5],""<""&Sheet1!R[1]C[-7],'Agent_Detail_Data'!C[5],Sheet1!R1C1)"
      Range("J" & i).Formula = "=COUNTIFS('Agent_Detail_Data'!C3,Sheet1!R1C10,'Agent_Detail_Data'!C[-6],"">=""&Sheet1!RC[-8],'Agent_Detail_Data'!C[-6],""<""&Sheet1!R[1]C[-8],'Agent_Detail_Data'!C[4],Sheet1!R1C1)"

      Next i

      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      Application.AskToUpdateLinks = True
      Application.Calculation = xlCalculationAutomatic

      Exit Sub
      End Sub


      Apologies if this has been asked before, i haven't been able to find the answer. Also, i hope i have framed the question correctly.



      Many thanks,



      A







      vba excel






      share|improve this question









      New contributor




      AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited 58 mins ago







      AdmirE













      New contributor




      AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 6 hours ago









      AdmirEAdmirE

      11




      11




      New contributor




      AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      AdmirE is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.




      put on hold as off-topic by Comintern, Jamal 21 mins ago


      This question appears to be off-topic. The users who voted to close gave this specific reason:


      • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – Jamal

      If this question can be reworded to fit the rules in the help center, please edit the question.




      put on hold as off-topic by Comintern, Jamal 21 mins ago


      This question appears to be off-topic. The users who voted to close gave this specific reason:


      • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – Jamal

      If this question can be reworded to fit the rules in the help center, please edit the question.






















          0






          active

          oldest

          votes

















          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes

          Popular posts from this blog

          How to make a Squid Proxy server?

          Is this a new Fibonacci Identity?

          19世紀