CODEWITHSUNDEEP
vbaexcel
Michael Lynch
Michael Lynch

Reputation: 13

Using InputBox to return user input as a text string as a variable in a formula

I would like to take user input and insert it in a formula.

The input box would have a field for account name, month, type.

This variable would need to be returned in the sumifs formula saying sumifs, column E is this account column c is this month and column d is this type.

I've looked but all I find involve numbers inputs much less returning the text string as a variable in a sumifs formula.

Formula Ex:

=SUMIFS(E2:E100, A2:A100, "Account Name",C2:C100,"Month",D2:D100,"Type")

Upvotes: 0

Views: 1292

Answers (1)

MatthewD
MatthewD

Reputation: 6761

You will have to build a form if you want to have the three input boxes on one form. If you can live with three separate input forms you can do it like this.

Dim AccountName As Variant 
AccountName = InputBox("Please Enter the account name.*", "Account Name")
Dim AccountMonth As Variant 
AccountMonth = InputBox("Please Enter the account name.*", "Account Date")
Dim AccountType As Variant 
AccountType = InputBox("Please Enter the account name.*", "Account Type")

You will want to put some validations on the values that are entered.

Then you can use them to build you formula

Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")

ws.Range("A2").Formula = "=sumifs(E2:E100, A2:A100, " & AccountName & ",C2:C100," & AccountMonth  & ",D2:D100," & AccountMonth & ")"

You will need to target the cell you want to write the formula into. I used A2 because I didn't know your target cell.

Upvotes: 1

Related Questions