How to use InputBox to populate function
Requirement:
Someone could help me to use inputbox variables to populate the data to be used in this function.
Function Cover (Stock As Double, Sales As Range) As Double
s = Stock
c = 0
For Each sale In Sales.Cells
If s = 0 Then Exit For
If s >= Val(sale.Value) Then
c = c + 1
s = s - Val(sale.Value)
Else
c = c + s / Val(sale.Value)
s = 0
Exit For
End If
Next
If s > 0 Then c = 9999
Cover = c
End Function
Solution:
Code:
Sub Test_Cover()
Dim d As Double, s As Range
d = Application.InputBox("Stock", Type:=1)
Set s = Application.InputBox("Select Sales Range", Type:=8)
MsgBox Cover(d, s)
End Sub
Function Cover(Stock As Double, Sales As Range) As Double
Dim c As Double, s As Double, sale As Range
s = Stock
c = 0
For Each sale In Sales
If s = 0 Then Exit For
If s >= Val(sale.Value) Then
c = c + 1
s = s - Val(sale.Value)
Else
c = c + s / Val(sale.Value)
s = 0
Exit For
End If
Next
If s > 0 Then c = 9999
Cover = c
End Function
Obtained from the OzGrid Help Forum.
Solution provided by Kenneth Hobson.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use an input box to enable a range of cells to autofill |
| How to use a macro to select value criteria from a table rather than manually inputting |
| How to format InputBox as date |
| How to input a row variable pertaining to all macros |
| How to find and replace based on list entered by user input |
| How to use cell content as input to a structured reference as part of a lookup function |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
