Allowing Column Ranges in UDF
I'm not sure what valid data is, but maybe you could just use the usedrange:
function myfunc(rng as range) as Variant
dim myRng as range
with rng
set myrng = nothing
on error resume next
'used range and just the first area???
set myrng = intersect(.parent.usedrange,.areas(1))
on error goto 0
end with
if myrng is nothing then
myfunc = "invalid range"
exit function
end if
'just two columns?
set myrng = myrng.columns(1).resize(,2)
'and now work against myrng
....
End function
Nirmal Singh wrote:
I have a User Defined Function which takes a Range as an argument.
It processes the range as follows
For RowNumber = 1 To DateRange.Rows.Count
DateFrom = DateRange.Cells(RowNumber, 1).Value
DateTo = DateRange.Cells(RowNumber, 2).Value
......(Further Processing)
Next RowNumber
This works fine for a range such as "C23:D50".
How can I cater for the user entering a column range such as "C:D"? In this
case I would want to loop down column C while it had valid data.
Nirmal
--
Dave Peterson
|