How do I pass a variable cell reference to a Range Object?

[Paula]

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(xlLastCell).Select_
ActiveCell.Offset(1, -9).Range("A1").Select" ))

 

[Barb Reinhardt]

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

 

[JLGWhiz]

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