Code Tweek Needed

[Phil Hageman]

Excel 2000, code located in the worksheet code object.

Testing the first of three parameter cases in this
worksheet code; columns P1013 (data entry cells),
V10:V13 (data entry cells), U19:U30 (data entry cells),
and column X19:X30 (code results cells):

Entering the value 100 in cell P10, and the value 50 in
cell V10 sets up a case for entries in U19:U30. After
entering the value 75 in cell U19, pressing enter, the
code should calculate the value 50 for cell X19. Instead,
there is no value entered in cell X19 (it is blank on
screen) and the cursor moves on to cell U20. No error
messages.

Entering the value 75 in AD19 results in #Div/0! in
cell AG19, result should be 50.
Entering the value 75 in AM19 results in #Div/0! in
cell AP19, result should be 50.
Entering the value 75 in AV19 results in #Div/0! in
cell AY19, result should be 50.

Can someone help me correct this?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As range)
Dim nRow As Long
Dim nRow1 As Long
Dim dV As Double
Dim dP As Double
Dim rng As range
Set rng = Intersect(range("U:U, AD:AD, AM:AM, AV:AV"), _
range("19:30, 51:62, 83:94"))
With Target
If .Count > 1 Then Exit Sub
If NotIntersect(.Cells, rng) Is Nothing Then
nRow1 = Int((.row - 19) / 32)
nRow = Int(.Column - 21) / 9
dV = range("V10").Offset _
(nRow + 32 * nRow1, 0).Value
dP = range("P10").Offset _
(nRow + 32 * nRow1, 0).Value
Application.EnableEvents = False
.Offset(0, 3).Value = (.Value - dV) / (dP - dV)
Application.EnableEvents = True
End If
End With
End Sub

 

[Paul Reynolds]

Phil,

A few things:

i) NotIntersect is missing a space. I assume this is a typo.
ii) The code you supply does give a value in X19 - provided the value in U19
is entered after the values in P10 and V10 are entered. However, it gives a
value of 0.5, not 50.
iii) For the other columns (AD19, AM19 and AV19), it is difficult to see
where the mistake is without seeing the spreadsheet. E.g. for a change in
AD19, it is looking for a value of dV in V42 and a value of dP in P42. If
these are present in the spreadsheet, the code should work properly.

Hope this helps,
Paul

 

[Phil Hageman]

Paul, thanks very much for your help - appreciate your
time. Before I go further, please know I am not a
programmer and a lot of this code I do not understand.
That said, the following:

i.) The space was added, which cleared a compile error.
ii.) .5, or 50%, is the correct answer - but I'm still
getting a blank in X19, and, #Div/0! when I make entries
in AD19 (error in AG19), AM19 (error in AP19), and AV19
(error in AY19). Seems like it is looking in the wrong
place for the divisor (thus a blank and #Div/0!)?. The
inputs were made in the correct order.
iii.) Could this be a clue - for AD19, AM19 and AV19, it
should not be looking in P42 or V42, rather, P10 and V10.

Where next?

Thanks, Phil

-----Original Message-----
Phil,

A few things:

i) NotIntersect is missing a space. I assume this is a typo.
ii) The code you supply does give a value in X19 -

provided the value in U19 is entered after the values in
P10 and V10 are entered. However, it gives a value of 0.5,
not 50.

 

[J.E. McGimpsey]

Did you take a look at the revised code that I provided?

http://google.com/groups?threadm=jemcgimpsey-0F0EFB.15181821122003@
msnews.microsoft.com

I *think* it does what you expect.

Note that, based on your earlier problem statement, and entry in
AD19 will reference P11, not P10. Likewise, AM19 will reference P12
and AV19 will reference P13.

 

[Paul Reynolds]

Phil,

If it should always be using P10 & V10, I'm a bit confused by the code:

dV = range("V10").Offset(nRow + 32 * nRow1, 0).Value
dP = range("P10").Offset(nRow + 32 * nRow1, 0).Value

Offset(row,column) is used to get an offset to a cell (e.g.
Range("A1").Offset(2,1).Value would be the value in B3), so the cells used
for dP and dV will change depending on the row/column changed in the
worksheet.

It looks like these two lines could be changed to:

dV = range("V10").Value
dP = range("P10").Value

Also, try clearing all formatting from X19, and seeing whether you still get
a blank.

Paul

 

[Phil Hageman]

Mr. McGimpsey, Nice to hear from you again. Am I
confused here! I thought this was your code (from 12/21),
as modified by Tom Ogilvy. Should I enter your code,
dated 12/21, 2:18:18, vs Tom's? Sorry this is getting so
confusing...
Thanks, Phil
I tried to come up on the below url, and Google could not
find it.

 

[Phil Hageman]

Mr. McGimpsey, I entered your code from 12/21. Some
points:
i.) I changed the row number sequence in "U51:U62... line
to all 51:62, where some were 62:51.
ii.) My computer will not allow me to capitalize the word
range anywhere in the code.
iii.) Do not receive any #Div/0! errors.
iv.) Answers are not coming up in columns "X", "AG", "AP"
or "AY".

 

[Phil Hageman]

Paul, thanks for your interest and response. The code
refers to "P" and "V" cell ranges in three places in this
worksheet, thus the offset.

I did as you said; Edit > Clear > All in X19 - still the
same outcome, no answers in the "X", "AG", "AP" or "AY"
cells.

Still trying to get it right.

Thanks, Phil

 

[J.E. McGimpsey]

Did you put the code in the worksheet module?

It works for me in my test workbook. Download it at

ftp://ftp.mcgimpsey.com/excel/ph_demo.xls

 

[J.E. McGimpsey]

You need to use the entire URL, from "http://" to ".com"

 

[J.E. McGimpsey]

See my response in "McGimpsey Code Entered".

If possible, let's stick to one thread for any further replies...

 

[Tom Ogilvy]

This version of the code shows you what cells will be involved in the
calculation. From my testing, it matches exactly what you described. the
actual calculation has been commented out. Perhaps you need a check to
insure the dp - dv <> 0

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nRow As Long
Dim nRow1 As Long
Dim dV As Double
Dim dP As Double
Dim rDV as Range
Dim rDP as Range
Dim rng As Range
Set rng = Intersect(Range( _
"U:U,AD:AD,AM:AM,AV:AV"), Range( _
"19:30,51:62,83:94"))

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, rng) _
Is Nothing Then
nRow1 = Int((.Row - 19) / 32)
nRow = Int(.Column - 21) / 9
Set rDV = Range("V10").Offset(nRow + 32 * nRow1, 0)
Set rDP = Range("P10").Offset(nRow + 32 * nRow1, 0)
dV = Range("V10").Offset(nRow + 32 * nRow1, 0).Value
dP = Range("P10").Offset(nRow + 32 * nRow1, 0).Value
sStr = Target.Address & " - " & Target.Offset(0, 3).Address _
& "-" & rDP.Address & "-" & rDV.Address
MsgBox sStr
' Application.EnableEvents = False
'
' .Offset(0, 3).Value = (.Value - dV) / (dP - dV)
' Application.EnableEvents = True
End If
End With
End Sub

Rows: 19 through 30
P10, V10, U19:U30 (input); X19:X30 (code solutions)
P11, V11, AD19:AD30 (input); AG19:AG30 (code solutions)
P12, V12, AM19:AM30 (input); AP19:AP30 (code solutions)
P13, V13, AV19:AV30 (input); AY19:AY30 (code solutions)

EXPANSION ONE PARAMETERS:
Rows: 51 through 62
P42, V42, U51:U62 (input); X19:X30 (code solutions)
P43, V43, AD51:AD62 (input); AG51:AG62 (code solutions)
P44, V44, AM51:AM62 (input); AP51:AP62 (code solutions)
P45, V45, AV51:AV62 (input); AY51:AY62 (code solutions)

EXPANSION TWO PARAMETERS:
Rows: 83 through 94
P74, V74, U83:U94 (input); X83:X94 (code solutions)
P75, V75, AD83:AD94 (input); AG83:AG94 (code solutions)
P76, V76, AM83:AM94 (input); AP83:AP94 (code solutions)
P77, V77, AV83:AV94 (input); AY83:AY94 (code solutions)

I uncommented the calculations and repeated the procedures you outlined.

Again, the code worked flawlessly.

You were probably getting divide by zero errors because you didn't put any
values in P1113 and V11:V13

Don't know why you are having trouble, but the code seems to work fine.

 

[Phil Hageman]

Tom, Maybe you've hit on something here - it is obvious to
me, but wouldn't be to you, and I have been passing over
it every time I communicate with you folks. Makes me
wonder what else I'm not telling you...

It is possible some "P", "V", and "U" cells may hold no
values - this is a legitimate circumstance for the user.
Sounds like this may be my problem - the cause of blank
and #Div/0!answers. Can you: 1.) modify the code to work
around blank cells (dp - dv <> 0), and 2.) make it work in
all three regions of the worksheet independent of cell
entries.

Is it possible for me to send you the worksheet via e-mail?

Thanks, Phil

 

[J.E. McGimpsey]

Phil - Please don't change subjects. It makes it impossible for
Google to archive a thread.

Here's a workaround. I've updated the demo file as well:

ftp://ftp.mcgimpsey.com/excel/ph_demo.xls

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nRow As Long
Dim dV As Double
Dim dP As Double
Dim nStart As Long
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range( _
"U19:U30,AD19:AD30,AM19:AM30,AV19:AV30," & _
"U51:U62,AD51:AD62,AM51:AM62,AV51:AV62," & _
"U83:U94,AD83:AD94,AM83:AM94,AV83:AV94")) _
Is Nothing Then
nStart = 10 - 32 * ((.Row >= 51) + (.Row >= 83))
nRow = (.Column - 21) / 9
dV = Range("V" & nStart).Offset(nRow, 0).Value
dP = Range("P" & nStart).Offset(nRow, 0).Value
If Not dP = dV Then
Application.EnableEvents = False
.Offset(0, 3).Value = (.Value - dV) / (dP - dV)
Application.EnableEvents = True
End If
End If
End With
End Sub

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nRow As Long
Dim nRow1 As Long
Dim dV As Double
Dim dP As Double
Dim rDV as Range
Dim rDP as Range
Dim rng As Range
Set rng = Intersect(Range( _
"U:U,AD:AD,AM:AM,AV:AV"), Range( _
"19:30,51:62,83:94"))

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, rng) _
Is Nothing Then
nRow1 = Int((.Row - 19) / 32)
nRow = Int(.Column - 21) / 9
Set rDV = Range("V10").Offset(nRow + 32 * nRow1, 0)
Set rDP = Range("P10").Offset(nRow + 32 * nRow1, 0)
dV = Range("V10").Offset(nRow + 32 * nRow1, 0).Value
dP = Range("P10").Offset(nRow + 32 * nRow1, 0).Value
' sStr = Target.Address & " - " & Target.Offset(0, 3).Address _
' & "-" & rDP.Address & "-" & rDV.Address
' MsgBox sStr
Application.EnableEvents = False
if dP - dV <> 0 then
.Offset(0, 3).Value = (.Value - dV) / (dP - dV)
else
' comment out if you just want a blank cell result
.offset(0,3).Value = "invalid inputs"
end if
Application.EnableEvents = True
End If
End With
End Sub

You can send me the workbook if you want.

 

[Phil Hageman]

Mr. McGimpsey, I confess to ignorance with the vinacular
here. How did I change the subject? I'll try not to do it
again. I don't understand the statement "It makes
it...Google...thread.

I came up on the ftp:... and it shows an .xls sheet with
the numbers, nothing more (answers are correct). Presume
this is all I'm supppose to see. I updated to your code
below with no response. Could the fact that my computer
will not capitalize the word "range" in the code mean VBA
will not recongize portions of the code? I sincerely
believe your efforts should be producing results, and that
the problem is simply me. Please bear with me - I'll try
whatever it takes. Could I sent the worksheet?

Thanks, Phil

 

[Tom Ogilvy]

Phil,
I would assume you are supposed to change the number in column X19 for
example and see that the code is correctly updating the appropriate cells?
Then look in the worksheet code module and use the code there in your
workbook.

To correct your range problem. First make sure you don't have

Dim range as Range
or have range as an argument to a function.

then go to a general module and put in

Public Range as String

Then select the declaration and delete it.

This should clear up your capitalization problem although I don't think it
is contributing to whatever problem you seem to think you are having.

JE was referring to you changing the subject line in your post.

 

[J.E. McGimpsey]

See my minor addtion in the thread "Capitalization in Declarations"