ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Code malfunction (https://www.excelbanter.com/excel-programming/285921-code-malfunction.html)

Phil Hageman[_3_]
Code malfunction
 
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
Code malfunction
 
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.

--
Regards,
Tom Ogilvy



Phil Hageman wrote in message
...
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

Phil Hageman[_3_]
What is the change?
 
Tom, I'm confused here. What is the change? The section
of the code "Target.value" is the values for the "U"
cells, correct?
-----Original Message-----
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.

--
Regards,
Tom Ogilvy



Phil Hageman wrote
in message
...
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



.

Phil Hageman[_3_]
Syntax error
 
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)

-----Original Message-----
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.

--
Regards,
Tom Ogilvy



Phil Hageman wrote
in message
...
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



.

Phil Hageman[_3_]
Got it working
 
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.
-----Original Message-----
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.

--
Regards,
Tom Ogilvy



Phil Hageman wrote
in message
...
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



.


All times are GMT +1. The time now is 11:21 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com