Passing Parameters to Userdefined Functions
rng.rows.count
rng.columns.count
to refer to a specific cell
rng(1,1) would be first row, first column
rng(2,10) would be second row, first column
rng(rng.rows.count,rng.columns.count) would be the lower right
Note that this method of address is anchored to the upper left corner of the
range - so you can refer to cells beyond the range defines
? Range("A1:B4")(5,15).Address
$O$5
As an example.
Putting in Cells is equivalent:
? Range("A1:B4").Cells(5,15).Address
$O$5
--
Regards,
Tom Ogilvy
Peter M wrote in message
...
Tom,
Thanks for that. I am now trying to use the data passed, and am
struggling
on two and I suspect related counts. I firstly tried to define an array
to
hold the data, but couln't extract the number of columns or rows in the
range.
Using the variables from your example, I tried:
rng.rows
rng.columns
but these appear to fail. How do I refer to individual cells within the
range, would rng.cells(1,3) find the first row, third column for example?
Grateful for any advice.
Peter M
"Tom Ogilvy" wrote in message
...
Assuming your range is a contiguous range of cells:
Function Dosomething(names as string, rng as Range)
Dim cell as Range
for each cell in rng
if cell.value = names then
--
Regards,
Tom Ogilvy
"Peter M" wrote in message
...
I want to create a userdefined function that will be assigned to
parameters:
1. a cell containing a string, 2. a range of cells containing a
variety
of
data. The contents of the string will be parsed and then compared
with
the
range of data. The actual processing is not the problem, and I can
easily
create the passing mechanism for the string variable, but I dont know
how
to
pass an area of the spreadsheet to the function. I have tried
something
like:
function dosomething(names as string, paramarray DataArea)
code here
end func
It compiles and seems to work, but nothing appears in the DataArea
variable
(or if it does I cant access it!).
Any ideas gratefully received.
Peter Morris
|