Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allowing Column Ranges in UDF
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allowing users to edit selected ranges only | Excel Discussion (Misc queries) | |||
Sum Multiple Ranges in the Same Column | Excel Discussion (Misc queries) | |||
Data ranges within a column | Excel Worksheet Functions | |||
How do I add different ranges in a column together? | New Users to Excel | |||
Only Allowing Certain Codes In Column | Excel Discussion (Misc queries) |