Expanding Code

[Phil Hageman]

In this code (located in the worksheet code area), I need
to expand things further. As it is now, cells P10 and V10
work with two ranges: U19:U30, and X19:X30, to calculate
values for the "X" range cells. I need to add three more
sets of cells/ranges:

P10, V10, 9:U30 and X19:X30 (existing code)
P11, V11, AD19:AD30 and AG19:AG30 (New. AD = Column 30)
P12, V12, AM19:AM30 and AP19:AP30 (New. AM = Column 39)
P13, V13, AV19:AV30 and AY19:AY30 (New. AY = Column 48)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long
row = Target.row
'Test target column
If Target.Column = 21 Then ' Column "U"
'Test if it is a 'month'
If row >= 19 And row <= 30 Then
Cells(row, "X").Value = _
((Target.Value - Range("V10").Value) / _
(Range("P10").Value - Range("V10").Value))
End If
End If
End Sub

 

[Bob Phillips]

Hi Phil,

I think that this is what you want

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long

row = Target.row
'Test if it is a 'month'
If row >= 19 And row <= 30 Then
'Test target column
Select Case Target.Column
Case 21: ' Column "U"
Cells(row, "X").Value = _
((Target.Value - Range("V10").Value) / _
(Range("P10").Value - Range("V10").Value))
Case 30: ' Column "AD"
Cells(row, "AG").Value = _
((Target.Value - Range("V10").Value) / _
(Range("P11").Value - Range("V11").Value))
Case 39: ' Column "AM"
Cells(row, "AP").Value = _
((Target.Value - Range("V10").Value) / _
(Range("P12").Value - Range("V12").Value))
Case 48: ' Column "AY"
Cells(row, "AV").Value = _
((Target.Value - Range("V10").Value) / _
(Range("P13").Value - Range("V13").Value))
End Select
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

one way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nRow As Long
Dim dV As Double
Dim dP As Double
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range( _
"U19:U30,AD19:AD30,AM19:AM30,AV19:AV30")) _
Is Nothing Then
nRow = Int(.Column - 21) / 9
dV = Range("V10").Offset(nRow, 0).Value
dP = Range("P10").Offset(nRow, 0).Value
Application.EnableEvents = False
.Offset(0, 3).Value = (.Value - dV) / (dP -dV)
Application.EnableEvents = True
End If
End With
End Sub