Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expanding Code
Phil,
Sorry I missed a value. Try this instead 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("V11").Value) / _ (Range("P11").Value - Range("V11").Value)) Case 39: ' Column "AM" Cells(row, "AP").Value = _ ((Target.Value - Range("V12").Value) / _ (Range("P12").Value - Range("V12").Value)) Case 48: ' Column "AY" Cells(row, "AV").Value = _ ((Target.Value - Range("V13").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) "Bob Phillips" wrote in message news:... 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) "Phil Hageman" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expanding a file | Excel Discussion (Misc queries) | |||
Expanding links??? | Links and Linking in Excel | |||
Expanding Selection | Excel Discussion (Misc queries) | |||
Expanding field | Excel Discussion (Misc queries) | |||
Expanding Code | Excel Programming |