Code malfunction

[Phil Hageman]

I'm trying to develop a basic code architecture upon which
to expand to additional fixed cells and range pairs. The
basic construct is explained below.

In Excel 2000, the Sub (located in the worksheet code
area - I will be placing the exact same code on three
other worksheets) operates on two cells, P10 and V10, and
a pair of ranges, U19:U30 and X19:X30 (twelve rows, one
for each month of the year). The user makes inputs into
P10, V10, and a "U" cell, say U19 (Case 1. here), and the
code calculates a value for a corresponding "X" cell, in
this Case X19. The formulas for four cases below would
be:

Case 1. U19 - V10 / P10 - V10.
Case 2. U20 - V10 / P10 - V10
Case 3. U21 - V10 / P10 - V10
Case 4. U22 - V10 / P10 - V10
Etc... U30 - V10 / P10 - V10

Inputs and code answers for the four cases:

V10 P10
Case 1. 100 (input) 0(input)
Case 2. 100(input) 0(input)
Case 3. 90(input) 0(input)
Case 4. 90(input) 30(input)

Cell U19 X19
Case 1. 100(input) 100%(the answer: 100-0/100-0)

U20 X20
Case 2. 90(input) 90%(the answer: 90-0/100-0)

U21 X21
Case 3. 85(input) 92%(the answer: 85-30/90-30)

**Here is the problem - the answer for Case 3 is coming
out 35%, vs. correct 92%, and wrong answers from there on
(Case 4.). No error messages, just wrong answers.
However, when if I highlight cells U21 and X21 and press
delete, I get a Run-time error '13': Type mismatch. Can
someone help me straighten this code out? Thanks.

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

 

[Tom Ogilvy]

this formula
(Target.Value - Range("V10").Value / _
(Range("P10").Value - Range("V10").Value))


is
U21 - (V10/(P10-V10) rather than the desired (U21-V10)/(P10-V10)

(Target.Value - Range("V10").Value) / _
(Range("P10").Value - Range("V10").Value)

is what you want.

 

[Phil Hageman]

Tom, I'm confused here. What is the change? The section
of the code "Target.value" is the values for the "U"
cells, correct?

 

[Phil Hageman]

Tom, if I delete the last ")" and execute the code, I get
a syntax error, with the following code in red:

Cells(row, "X").Value = _
(Target.Value - Range("V10").Value / _
(Range("P10").Value - Range("V10").Value)

 

[Phil Hageman]

Tom, I figured out from your answer just where to put
additional left and right parens, and got it to work. In
a little while, I will post the next thing I want to do
with this model - expand it to work with other ranges.

Thanks, very much for your time.