variables in formulae

  • Thread starter Thread starter Kelston
  • Start date Start date
K

Kelston

I am trying to create a variable from a formula.

The formula is a max() and a min() where the row numbers change and ar
variables. e.g I go to end of column and and set a variable calle
LastRow. Then I have another varaible that is MyRow which equal
LastRow-a number which has been inpuuted by an Input box.

Is there a way you can use a variable in a formula
 
Dim a as variant
Dim rng as Range
Dim mMax as Double
Dim mMin as Double
Do
a = inputbox("enter number of rows - must be a number")
if a = "" then exit sub
Loop Until isnumeric(a)
set rng = Range(cells(lastrow- a +1 ,1),Cells(lastrow))
mMax = application.Max(rng)
mMin = Application.Min(rng)
 
Kelston,

Try something like

Dim LastRow As Long
LastRow = CLng(Application.InputBox(prompt:="Enter last row", _
Type:=1))
Debug.Print Application.Max(Range("A1:A" & LastRow))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top