Help inserting a variable in to a formula

[Joinersfolly]

Range("B" & LastRow).Formula = "=Today()-6"



How may I insert a variable instead of the number 6 above.
I want to insert a number from the cell G1 in my worksheet change event

[mancubus]

try:



Dim varName As Long


varName = 6
Range("B" & LastRow).Formula = "=Today()-" & varName

[SamT]

Range("B" & LastRow).Formula = "=Date()-$G$1"
NB: The Date therein will change every day when the sheet is Calculated

[lombardo73]

Dim variable As Range


Set variable = Range("G1")

Range("B" & LastRow).Formula = "=Today()-" & variable.value

[mancubus]

it seems i missed the last bit.

use SameT's code. since you know the cell, you dont need a variable.

[snb]

cells(rows.count,2).end(xlup).offset(1)="=today()-$G$1"

Since the attributed string starts with "=" it's being evaluated as a formula by Excel. The default for a cell is .Value. So nor .Formula, nor .Value is required (is even rdundant, because by using the '=' you force Excel to consider the string as a formula..

[Joinersfolly]

Thanks everyone,

The code that mancubus provided worked a treat, however I understand the other answers(after a fashion)
Problem solved, but in turn led to hours of frustration with 'enable events',also now solved, the fun is in the learning they say. Thanks again