Forum Discussion

Nastarius's avatar
Nastarius
Copper Contributor
Sep 20, 2022
Solved

Creating a formula using InputBox

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 Cr...
excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Sep 20, 2022

    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.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Sep 20, 2022

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.

Resources