Usinf function in macro
 

I want to write a macro using Excel function in it e.g. Average.

Using the record macro, I get the code "=Average(RC[1]:R[0]C[12])"

However, I want that the range is input by the user
i.e the no. of column is unknown.

So how do I change the code?


--
hurriance
------------------------------------------------------------------------
hurriance's Profile: http://www.excelforum.com/member.php...o&userid=30882
View this thread: http://www.excelforum.com/showthread...hreadid=511502

Usinf function in macro
 

Will the user select the whole range?

Option Explicit
sub testme01()

dim myRng as range
set myrng = nothing
on error resume next
set myrng= application.inputbox(prompt:="Select a range",type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel, do nothing
else
msgbox application.average(myrng)
'or
activecell.formula = "=average(" & myrng.address(external:=true) & ")"
end if

End Sub


hurriance wrote:

I want to write a macro using Excel function in it e.g. Average.

Using the record macro, I get the code "=Average(RC[1]:R[0]C[12])"

However, I want that the range is input by the user
i.e the no. of column is unknown.

So how do I change the code?

--
hurriance
------------------------------------------------------------------------
hurriance's Profile: http://www.excelforum.com/member.php...o&userid=30882
View this thread: http://www.excelforum.com/showthread...hreadid=511502

--

Dave Peterson