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
vba excel
asked 6 hours ago
AdmirEAdmirE
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.
$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.
add a comment |
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
vba excel
asked 6 hours ago
AdmirEAdmirE
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.
$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.
add a comment |
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
vba excel
asked 6 hours ago
AdmirEAdmirE
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.
$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
vba excel
asked 6 hours ago
AdmirEAdmirE
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.
asked 6 hours ago
AdmirEAdmirE
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.
edited 58 mins ago
AdmirE
asked 6 hours ago
AdmirEAdmirE
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.
asked 6 hours ago
AdmirEAdmirE
11
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.
add a comment |
add a comment |
0
active
oldest
votes
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
