VBA Help

[Jay]

hi

I have a excel sheet

Column K Column L Column M

A or B or C
drop down list

there will be values on Column K,

Column L is a drop down list which ever i select either A ,B or C i
have a Formula like this on Column M

=IF((L9="A"),-1*K9,IF((L9=""),"",IF((L9="B"),K9*F9,IF((L9="C"),0*K9))))

I have more than 400 Rows this is taking a lot of time to calculate the
formula ...is there any best way to do this ? any VBA code ?

Thanx in advance

Regards
Jay

 

[tplourde]

You might consider simplifiying the formula to speed calculation. If the
difference between a blank cell and zero is not relevant consider
=IF((L12="A"),-K12,IF((L12="B"),K12*F12,0))

Or you could put the formula options in your validation list and eliminate
the ABC thing altogether. Validation options would be 0,-K15,=F15*K15,

Good Luck

 

[Jay]

i have same problem its taking ages to calculate all the cells
connected to it , is thre any macro to do this ?

 

[tplourde]

This macro assumes that you select the cells you want it applied to before
running the macro. Careful, select only cells in column K or it will create a
mess.

Tom

Sub CalcNow()

Dim cellK As Range

For Each cellK In ActiveWindow.RangeSelection
If cellK = "A" Then
cellK.Offset(0, 1) = -cellK.Offset(0, -1)
ElseIf cellK = "B" Then
cellK.Offset(0, 1) = cellK.Offset(0, -5) * cellK.Offset(0, -1)
ElseIf cellK = "C" Then
cellK.Offset(0, 1) = 0
Else
cellK.Offset(0, 1) = ""
End If
Next cellK

End Sub