Solved: UDF to select a particulr range automatically

[philfer]

For example if you use the autosum function with numbers in the same row and numbers above it in the column it will automatically take the numbers above it as the expected range. However if there are only numbers in the row it will take that as the expected range.

How do I get my UDF to have the same functionality.

Thanks

[Bob Phillips]

The problem with that is that when the sheet recalculates, your UDF won't as it has no range that would be in the recalculation stack.

[philfer]

Hi,

I think there may be a mix up between the two posts.

In the workbook attached if you click on F8 and click autosum it will select F5:F7 as what it thinks you want to sum

If you click on F14 and click autosum it will select C14:E14 as what it thinks you want to sum

How do I get VBA do do this if I am writing a similar function

[Bob Phillips]

I perfectly understand what you want to do, but as I said, if you write a UDF it would have to be argument free, and deduce the range to total within the range. That would work, but if any value in that range changes, your UDF would not be invoked because it doesn't reference the cells changed, so your total would not update.

The AutoSum facility isn't a function, it inserts a function. UDF's cannot do that.

[david000]

xld is right it can't be done, so I would try looking at other techniques.


Mr Excel podcasts 812 & 823
http://www.mrexcel.com/podcast/2008/08/episode-823-recording-autosum.html

http://www.mrexcel.com/podcast/2008/07/episode-812-recording-autosum-dont.html

J-Walk making an AutoSum like formula.


Sub EnterAvg()
Dim FirstCell As String
Dim LastCell As String
Dim TheFormula As String

' Check for invalid conditions
If TypeName(Selection) <> "Range" Then Exit Sub
If ActiveCell.Row = 1 Then Exit Sub
If ActiveCell.Offset(-1, 0).Value = "" Then Exit Sub
If ActiveCell.Offset(-2, 0).Value = "" Then Exit Sub

' Determine address of first and last cell in the range
FirstCell = ActiveCell.Offset(-1, 0).End(xlUp).Address _
(rowabsolute:=False, columnabsolute:=False)
LastCell = ActiveCell.Offset(-1, 0).Address _
(rowabsolute:=False, columnabsolute:=False)

' Build the formula
TheFormula = "=AVERAGE(" & FirstCell & ":" & LastCell & ")"

' Assign the formula
ActiveCell.Formula = TheFormula
End Sub



Sum a column starting in b2 down to the last cell and place a formula in it (one line) found here --- Nice!


Sub rng_EvaluateDown()
[B2].End(xlDown).Offset(1, 0).Formula = "=Sum(B2:" & [B2].End(xlDown).Address(0, 0) & ")"
End Sub