View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ker_01 ker_01 is offline
external usenet poster
 
Posts: 395
Default Pass activecell(.address?) as a range in a function parameter?


I'm trying to pass a single-cell range to a function that will then do stuff
to that cell. I've figured out how to do everything I want, with one (or two)
exceptions:

(1) I haven't figured out the proper way to pass the activecell to my
function as a range. I'm trying lots of variations of the following, but
haven't found the right syntax; in this case I'm getting type mismatch
presumably because activecell.address isn't a range (I guess it is a
string?).

Sub CustomPicker()
ActiveCell.Resize(1, 1).Select
CustomChanges(ActiveCell.Address)
'also tried activecell.cells, etc.- every property that seemed promising
End Sub

Function CustomChanges (xTargetCell as range)
'do stuff
xTargetCell.value = a
xTargetCell.interior.color = b
End Function

(2) In case anyone who reads this is feeling particularly generous, when a
multi-cell range is selected, is there a good way to always return the cell
in the upper left corner, regardless of which cell in the range is currently
selected, or how the range is selected? With my current code, if I click A1
and drag to F5, it returns A1 as expected. But if I click F5 and drag to A1,
it returns F5 and I'd much rather always default to the most upper left cell
in a multi-cell range, if possible.

Thank you!!
Keith