variables in formulae
 

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

--
Message posted from http://www.ExcelForum.com

variables in formulae
 

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)


--
Regards,
Tom Ogilvy



"Kelston " wrote in message
...
I am trying to create a variable from a formula.

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

Is there a way you can use a variable in a formula?


---
Message posted from http://www.ExcelForum.com/

variables in formulae
 

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


"Kelston " wrote in
message ...
I am trying to create a variable from a formula.

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

Is there a way you can use a variable in a formula?


---
Message posted from http://www.ExcelForum.com/