SOLVED

Creating a formula using InputBox

Visitor

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?

1 Reply
best response confirmed by Nastarius (Visitor)
Solution

@Nastarius 

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").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.