Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Tweek Needed
Excel 2000, code located in the worksheet code object.
Testing the first of three parameter cases in this worksheet code; columns P10:P13 (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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Tweek Needed
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" wrote in message ... Excel 2000, code located in the worksheet code object. Testing the first of three parameter cases in this worksheet code; columns P10:P13 (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) (0, 1, 2 = Row group) nRow = Int(.Column - 21) / 9 (0, 1, 2, 3 = Column group) dV = range("V10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in V10, V11, V12, V13, V42, V43, ..., V77) dP = range("P10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in P10, P11, P12, P13, P42, P43, ..., P77) Application.EnableEvents = False .Offset(0, 3).Value = (.Value - dV) / (dP - dV) Application.EnableEvents = True End If End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting closer
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. 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" wrote in message ... Excel 2000, code located in the worksheet code object. Testing the first of three parameter cases in this worksheet code; columns P10:P13 (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) (0, 1, 2 = Row group) nRow = Int(.Column - 21) / 9 (0, 1, 2, 3 = Column group) dV = range("V10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in V10, V11, V12, V13, V42, V43, ..., V77) dP = range("P10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in P10, P11, P12, P13, P42, P43, ..., P77) Application.EnableEvents = False .Offset(0, 3).Value = (.Value - dV) / (dP - dV) Application.EnableEvents = True End If End With End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting closer
Did you take a look at the revised code that I provided?
http://google.com/groups?threadm=jem...81821122003%40 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. In article , "Phil Hageman" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which code to use?
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. -----Original Message----- Did you take a look at the revised code that I provided? http://google.com/groups?threadm=jemcgimpsey- 0F0EFB.15181821122003%40 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. In article , "Phil Hageman" wrote: 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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which code to use?
You need to use the entire URL, from "http://" to ".com"
In article , "Phil Hageman" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
McGimpsey Code Entered
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". -----Original Message----- Did you take a look at the revised code that I provided? http://google.com/groups?threadm=jemcgimpsey- 0F0EFB.15181821122003%40 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. In article , "Phil Hageman" wrote: 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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
McGimpsey Code Entered
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 In article , "Phil Hageman" wrote: 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". |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting closer
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" wrote in message ... 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. 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" wrote in message ... Excel 2000, code located in the worksheet code object. Testing the first of three parameter cases in this worksheet code; columns P10:P13 (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) (0, 1, 2 = Row group) nRow = Int(.Column - 21) / 9 (0, 1, 2, 3 = Column group) dV = range("V10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in V10, V11, V12, V13, V42, V43, ..., V77) dP = range("P10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in P10, P11, P12, P13, P42, P43, ..., P77) Application.EnableEvents = False .Offset(0, 3).Value = (.Value - dV) / (dP - dV) Application.EnableEvents = True End If End With End Sub . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset explained, Clear All Tried
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 -----Original Message----- 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" wrote in message ... 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. 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" wrote in message ... Excel 2000, code located in the worksheet code object. Testing the first of three parameter cases in this worksheet code; columns P10:P13 (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) (0, 1, 2 = Row group) nRow = Int(.Column - 21) / 9 (0, 1, 2, 3 = Column group) dV = range("V10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in V10, V11, V12, V13, V42, V43, ..., V77) dP = range("P10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in P10, P11, P12, P13, P42, P43, ..., P77) Application.EnableEvents = False .Offset(0, 3).Value = (.Value - dV) / (dP - dV) Application.EnableEvents = True End If End With End Sub . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset explained, Clear All Tried
See my response in "McGimpsey Code Entered".
If possible, let's stick to one thread for any further replies... In article , "Phil Hageman" wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset explained, Clear All Tried
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 P11:P13 and V11:V13 Don't know why you are having trouble, but the code seems to work fine. -- Regards, Tom Ogilvy Phil Hageman wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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. 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" wrote in message ... Excel 2000, code located in the worksheet code object. Testing the first of three parameter cases in this worksheet code; columns P10:P13 (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) (0, 1, 2 = Row group) nRow = Int(.Column - 21) / 9 (0, 1, 2, 3 = Column group) dV = range("V10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in V10, V11, V12, V13, V42, V43, ..., V77) dP = range("P10").Offset _ (nRow + 32 * nRow1, 0).Value (A value in P10, P11, P12, P13, P42, P43, ..., P77) Application.EnableEvents = False .Offset(0, 3).Value = (.Value - dV) / (dP - dV) Application.EnableEvents = True End If End With End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code Help needed | Excel Programming | |||
Code Tweek Needed | Excel Programming | |||
code needed | Excel Programming |