Change to Worksheet Change Event

  • Thread starter Thread starter Steph
  • Start date Start date
S

Steph

Hello, given the worksheet change event code below:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
With Target
If .Column = 6 And .Row > 11 And .Row < 112 Then
Application.EnableEvents = False
If .Value = "S" Then
Cells(.Row, 8).FormulaR1C1 =
"=IF(ISBLANK(RC[-3]),0,IF(RC[-3]=""FT"",R10C25*8,R10C25*4))"
Cells(.Row, 9).FormulaR1C1 = "=RC[-2]*RC[-1]"
End If
Application.EnableEvents = True
End If
End With
End Sub

How can I have the code NOT put a formula in, for exmple, Cells(.Row,8) if
that call is NOT blank? I only want the formula to be entered if the cell
is blank. Thanks for your help!
 
This should do it if you mean "if the cell value is blank, even if the blank
is the result of a formula in the cell"
If Cells(.Row,8).Value="" Then Cells(.Row, 8).FormulaR1C1 =
"=IF(ISBLANK(RC[-3]),0,IF(RC[-3]=""FT"",R10C25*8,R10C25*4))"

This should do it if you mean that both the value of the cell is blank AND
there is no formula within the cell:
If Cells(.Row,8).Formula = "" Then Cells(.Row, 8).FormulaR1C1 =
"=IF(ISBLANK(RC[-3]),0,IF(RC[-3]=""FT"",R10C25*8,R10C25*4))"
 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
With Target
If .Column = 6 And .Row > 11 And .Row < 112 Then
Application.EnableEvents = False
If .Value = "S" Then
if isempty(Cells(.row,8)) then
Cells(.Row, 8).FormulaR1C1 =
"=IF(ISBLANK(RC[-3]),0,IF(RC[-3]=""FT"",R10C25*8,R10C25*4))"
End if
if isempty(Cells(.Row,9)) then
Cells(.Row, 9).FormulaR1C1 = "=RC[-2]*RC[-1]"
End if
End If
Application.EnableEvents = True
End If
End With
End Sub
 
Back
Top