Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass a worksheet as a parameter to a function? | Excel Programming | |||
Pass ActiveCell Value to my Add In Workbook | Excel Programming | |||
pass parameter to Query | Excel Programming | |||
Pass a range object as a parameter | Excel Programming | |||
pass named range address into variable | Excel Programming |