Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a variable cell reference to a Range Object?
I will be choosing the end of data point (over and over) and then offsetting
the active cell by some value. I don't know how to reference that new active cell so that I can tell another function that that is its destination. 'goes to last cell in data set (Ex: A10) ActiveCell.SpecialCells(xlLastCell).Select 'active cell 1 down and 9 left (Ex: B1) ActiveCell.Offset(1, -9).Range("A1").Select 'How do I indicate that Destination is B1, when B1 Changes each time? Instead of '"$A$85" I want to give it a relative variable. Destination:=Range("$A$85")) NOTE: I thought I could put in something like Destination:=Range("ActiveCell.SpecialCells(xlLast Cell).Select_ ActiveCell.Offset(1, -9).Range("A1").Select" )) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a variable cell reference to a Range Object?
I'm not sure what you're doing with destination, but you could do something
like this Sub test1() Dim aWS As Worksheet Dim myRange As Range 'goes to last cell in data set (Ex: A10) Set aWS = ActiveSheet Set myRange = ActiveCell.SpecialCells(xlLastCell) 'active cell 1 down and 9 left (Ex: B1) Set myRange = Nothing On Error Resume Next Set myRange = myRange.Offset(1, -9) On Error GoTo 0 If Not myRange Is Nothing Then 'Destination:= myRange 'I'm sure there's more to this End If End Sub -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Paula" wrote: I will be choosing the end of data point (over and over) and then offsetting the active cell by some value. I don't know how to reference that new active cell so that I can tell another function that that is its destination. 'goes to last cell in data set (Ex: A10) ActiveCell.SpecialCells(xlLastCell).Select 'active cell 1 down and 9 left (Ex: B1) ActiveCell.Offset(1, -9).Range("A1").Select 'How do I indicate that Destination is B1, when B1 Changes each time? Instead of '"$A$85" I want to give it a relative variable. Destination:=Range("$A$85")) NOTE: I thought I could put in something like Destination:=Range("ActiveCell.SpecialCells(xlLast Cell).Select_ ActiveCell.Offset(1, -9).Range("A1").Select" )) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pass a variable cell reference to a Range Object?
These two topics are in the VBA help files and will
provide sample code to illustrate how the references work. You might also want to look at the Offset property and the example code for that. There are several ways that you can apply cell references by variables, offset and relative reference. 1. Referring to Cells Relative to Other Cells 2. How to Reference Cells and Ranges "Paula" wrote: I will be choosing the end of data point (over and over) and then offsetting the active cell by some value. I don't know how to reference that new active cell so that I can tell another function that that is its destination. 'goes to last cell in data set (Ex: A10) ActiveCell.SpecialCells(xlLastCell).Select 'active cell 1 down and 9 left (Ex: B1) ActiveCell.Offset(1, -9).Range("A1").Select 'How do I indicate that Destination is B1, when B1 Changes each time? Instead of '"$A$85" I want to give it a relative variable. Destination:=Range("$A$85")) NOTE: I thought I could put in something like Destination:=Range("ActiveCell.SpecialCells(xlLast Cell).Select_ ActiveCell.Offset(1, -9).Range("A1").Select" )) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Cell Range Reference | Excel Worksheet Functions | |||
VLOOKUP variable range cell reference | Excel Worksheet Functions | |||
Pass a range object as a parameter | Excel Programming | |||
Pass a variable into a range? | Excel Programming | |||
How to (re)set a range.value to pass -0- to a "double" variable | Excel Discussion (Misc queries) |