thats great cheers


"Paul Mathews" wrote in message
...
Hi again James,

Yep, range selection is a bit goofy in Excel when the ranges occupy
different sheets . Notwithstanding this, perhaps you don't need to do any
range/cell selection at all but rather just retrieve values from the
column
in question. What you might want to try (and this gets around the sticky
cell/sheets selection stuff) is something along the lines of the
following:

findnewnominal(rng as Range) as Integer
dim i as long

For i = 1 to rng.rows.count
if rng.cells(i,1).value = blah blah then
do blah blah blah
endif

end function


"James Cornthwaite" wrote:

ah right


I have udf (as below) which has a range passed to it from a sheet

findnewnominal(rng as Range) as Integer
dim cell as range


rng.columns(1).select

For Each cell in selection
blah
blah

next cell

end function

but the above doesn't work
Am i correct in thinking that word selection is equal to column 1 of
range
(i'm new to VBA, used to JAVA more therefore would expect to see
selection =
rng.columns(1).select). And what you say about selecting the sheet
containing the range first, is this really neccessary when passing the
reference of a range to a function.

Can u see any other reason why my code above doesnt work.

Many Thanks
James

"Paul Mathews" wrote in message
...
Um, one other thing, you'll need to select the sheet the desired range
resides in before you attempt to select the column.

Sub SelFirstCol()
'Select first column of defined range
Sheets("BlahBlah").select
Range("Test").Columns(1).Select
End Sub


"James Cornthwaite" wrote:

does anybody know how to select the entire first column of a defined
range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James