Set Variable to Cell Location and Incorporate into Formula

[Airbisk]

Hello,

Could someone kindly help me complete this procedure please. I have set a variable to the ActiveCell and I wish to pick up the cell reference (eg E3) rather than the value.

The procedure below works but brings back the value rather than reference. So the cell formula will look like =80-C3 instead of =E3-C3 (examples)

Sub ServiceHistory()

Dim LastCell As Range


Range("B3").Select

ActiveCell.Offset(0, 1).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop

ActiveCell.Offset(0, -2).Select

Set LastCell = ActiveCell

Range("B3").Select

ActiveCell.Formula = "=" & LastCell & "-RC[+1]"


End Sub

 

[PHV]

Have a look at the Address property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Sub ServiceHistory()
    With Range("B3")
        .Formula = "=" & .End(xlToRight).Offset(-1).Address
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Airbisk]

Thanks chaps using your advice I have managed to get both cell addresses as variables. What I cannot get to work is when I try to use the string variables in a formula. It's something to do with "=" - Code below not working, thanks for your help.

Sub ServiceHistory()

Dim lastCell As String
Dim firstCell As String

lastCell = ActiveCell.End(xlToRight).Offset(0, -1).Address(RowAbsolute:=False)

Range("B3").Activate

firstCell = ActiveCell.Offset(0, 1).Address(RowAbsolute:=False)

Range("B3").FormulaR1C1 = "=" & lastCell & "-" & firstCell

End Sub

 

[PHV]

Range("B3").Formula = "=" & lastCell & " - " & firstCell

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Airbisk]

Many Thanks PH, all working as required.