Sep 20 2022 07:41 AM - edited Sep 21 2022 07:17 AM
I'm using Excel 2016. I am trying to figure out a way to create a formula that takes it's parameters from InputBox entries and then executes. Here's the function I'm trying to fix:
[CODE]
Sub CreateCountIFSQueryFunction()
Worksheets("EmpRpt").Activate
Dim startDate As String
Dim endDate As String
Dim industry As String
startDate = InputBox(Prompt:="Enter the Start Date", Title:="Date Range", Default:="01/01/1980")
endDate = InputBox(Prompt:="Enter the End Date", Title:="Date Range", Default:="12/31/2030")
industry = InputBox(Prompt:="Which Industry Are You Interested In?", Title:="Choose Industry", Default:="Name")
Range("C30").Select
Range("C30").Value = startDate
Range("D30").Value = endDate
Range("E30").Value = industry
Range("G30").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Employment!R2C3:R[477]C[-4],R30C3,Employment!R2C3:R[477]C[-4],R30C4,Employment!R2C7:R[477]C,R30C5)"
End Sub
[/CODE]
This formula works fine if you hardcode the dates. Example:
[CODE]
=COUNTIFS(Employment!$C$2:C502,">=8/1/2022",Employment!$C$2:C502,"<=08/31/2022",Employment!$G$2:G502,"Factory")
[/CODE]
This formula is being executed from the "EmpRpt" sheet. I need this formula to count the number of entries on the "Employment" sheet with dates that range from "startDate" to "endDate" where the column labeled "Industry" is the value entered for "industry". I've tried getting the ">=" and "<=" operators into the formula, but nothing seems to be working.
Am I going about this the wrong way?
Sep 20 2022 08:45 AM
SolutionSub CreateCountIFSQueryFunction()
Worksheets("EmpRpt").Activate
Dim startDate As String
Dim endDate As String
Dim industry As String
startDate = InputBox(Prompt:="Enter the Start Date", Title:="Date Range", Default:="01/01/1980")
endDate = InputBox(Prompt:="Enter the End Date", Title:="Date Range", Default:="12/31/2030")
industry = InputBox(Prompt:="Which Industry Are You Interested In?", Title:="Choose Industry", Default:="Name")
Range("C30").Select
Range("C30").Value = startDate
Range("D30").Value = endDate
Range("E30").Value = industry
Range("G30").FormulaR1C1 = _
"=COUNTIFS(Employment!R2C3:R507C3,"">=""&R30C3,Employment!R2C3:R507C3,""<=""&R30C4,Employment!R2C7:R507C7,R30C5)"
End Sub
You can try this code which works in my sheet.
Sep 20 2022 08:45 AM
SolutionSub CreateCountIFSQueryFunction()
Worksheets("EmpRpt").Activate
Dim startDate As String
Dim endDate As String
Dim industry As String
startDate = InputBox(Prompt:="Enter the Start Date", Title:="Date Range", Default:="01/01/1980")
endDate = InputBox(Prompt:="Enter the End Date", Title:="Date Range", Default:="12/31/2030")
industry = InputBox(Prompt:="Which Industry Are You Interested In?", Title:="Choose Industry", Default:="Name")
Range("C30").Select
Range("C30").Value = startDate
Range("D30").Value = endDate
Range("E30").Value = industry
Range("G30").FormulaR1C1 = _
"=COUNTIFS(Employment!R2C3:R507C3,"">=""&R30C3,Employment!R2C3:R507C3,""<=""&R30C4,Employment!R2C7:R507C7,R30C5)"
End Sub
You can try this code which works in my sheet.