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

Pass activecell(.address?) as a range in a function parameter?
 

It happens half the time... struggle forever, give up and post, and then
finally figure it out 5 minutes later. I successfully am passing the range
using

CustomChanges ActiveCell.Resize(1, 1)

Still open to ideas on problem #2 below, how to select the upper left cell
in a range! :)

Thank you,
Keith

"ker_01" wrote:


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

Pass activecell(.address?) as a range in a function parameter?
 

To pass the range to the function:

Sub CustomPicker()

Dim myRange as Range

ActiveCell.Resize(1, 1).Select
Set myRange = ActiveCell
CustomChanges myRange

End Sub


To get the address of the top- and left-most cell in your selection

myAddress = cells(selection.row, selection.column).address




"ker_01" wrote:


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

Pass activecell(.address?) as a range in a function parameter?
 

Option Explicit
Sub CustomPicker()

ActiveCell.Select 'why resize(1,1) and why select????

'don't include the ()'s
CustomChanges ActiveCell
'or if you want them, add the Call statement
Call CustomChanges(ActiveCell)
'don't use both.

End Sub

Function CustomChanges(xTargetCell As Range)
'do stuff
Dim a As Long
Dim b As Long

a = 2
b = 3

xTargetCell.Value = a
xTargetCell.Interior.Color = b
End Function

ker_01 wrote:

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

--

Dave Peterson