Shorter way to do this

[Corey]

I want to have in the = Sub Worksheet_SelectionChange(ByVal Target As
Range):
*********************************
H9=IF(D9*F9>0,(D9*F9),"")
H10=IF(D10*F10>0,(D10*F10),"")
.....
.....
.....
.....
same down rows till row 48
.....
.....
H48= =IF(D48*F48>0,(D48*F48),"")
*********************************

Is there a way i can condense this to not have a mass of code for each
separate reference to each cell from row 9 to row 48?


Corey....

 

[marcus]

Hi Corey

Try this for size. Put this in the worksheet module you want to run
it from. It will fill the Range in Column H with your formula any
time a cell changes in the Range D9 to F48 - Change to suit.

Take care

Marcus

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D9:F48")) Is Nothing Then
Range("H9").Value = "=IF(D9*F9>0,(D9*F9),"""")" 'Formula in H9
Range("H9:H48").FillDown 'Copies formula above down
End If

End Sub

 

[OssieMac]

Hi Corey,

The following will do what you want but I wonder if
Worksheet_SelectionChange event is the right type of event for this action.
Perhaps you can explain when you want the code to run.

'Note additional double quotes around the
'zero length string are required.
Range("H9") = "=IF(D9*F9>0,(D9*F9),"""")"
Range("H9").Copy Destination:=Range("H9:H48")

 

[Corey]

Thanks Marcus.

Perfect!

 

[Mike H]

Hi,

Try this

Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each c In Range("H9:H48")
If c.Offset(, -4).Value * c.Offset(, -2).Value > 0 Then
c.Value = c.Offset(, -4).Value * c.Offset(, -2).Value
Else
c.Value = ""
End If
Next
End Sub



Mike

 

[Bernd P]

Hello,

As a rule of thumb, disable events at the beginning and enable them
when the procedure exits:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("D9:F48")) Is Nothing Then
Range("H9:H48").FormulaArray = "=IF
(D948*F9:F48>0,D948*F9:F48,"""")" 'Formula in H9:H48
End If
Application.EnableEvents = True
End Sub

But why an event procedure? Did you set your calculation to manual?

Regards,
Bernd

 

[Rick Rothstein]

You can do what you want with one line of code...

Range("H9:H48").Formula = "=IF(D9*F9>0,(D9*F9),"""")"

 

[Corey]

Where would this code be better placed, if not in the
Worksheet_SelectionChange event?

Should i plkace it int he Calculation event instead?

Corey....

 

[Bernd P]

Hello,

If you have your formula in place and your calculation set to
automatic it would update anyway.

I cannot see why you would need that event procedure.

What did you mean to do, please?

Regards,
Bernd