Using VBA and inputbox input as data to create a formula to output

Motowisch

New Member
Joined
Oct 14, 2022
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I have been struggling with this for days and days. I am guessing one of you can easily solve this. I will try to be clear. I am trying to automate input using inputbox and gather variables to plug into a formula that will output into a chosen cell. The formula only has three variables.

A cell that is D2 in the working formula below
A range that is in a different workbook that I would like the address and range to come through into the formula
and
possibly select the location that the formula will be placed.

The working formula is
=IFERROR(VLOOKUP(D2,'G:\Shared drives\CGA Accounting\- Project Tracker\New Project Tracker Build folder\[MO Project tracker .xlsx]MO PROJECT S-W 3'!$C$11:$O$37,2,FALSE),0)

What I have is as follows;
Sub NEW_PROJECT_BILLING_TRACKER()



Dim STARTMONTH As Range
Dim PROJECTRANGE As Range
Dim FORMULACELL As Range
Dim PROJECTFORMULA As String


Set STARTMONTH = Application.InputBox("Start Month.", "Select Cell", Type:=8)
Set PROJECTRANGE = Application.InputBox("Project Date Range.", "Select Cells", Type:=8)
Set FORMULACELL = Application.InputBox("LOCATION OF CELL.", "Select Cell", Type:=8)
Range("D13").Formula = "=IfError(VLookup(STARTMONTH,PROJECTRANGE, 2, False), 0)"
End sub

I can only get this to put the exact text of the range as shown into the cell. It is not picking up the variables?

Please help, I would be so grateful that I will probably donate to this site.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,215,067
Messages
6,122,949
Members
449,095
Latest member
nmaske

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top