+ Reply to Thread
Results 1 to 78 of 78

select case calculations not working

  1. #1
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    select case calculations not working

    I have a select case that is triggered off of a value change in cell A2. A macro (classify) reformats some data and a data validation dropdown is put in cell A2. The user selects the case # and a value associated with that change is used in the select statement.

    VB
    Please Login or Register  to view this content.
    For example, the user selects case 15 and the value is "Comprehensive Epilepsy". So the calculations for that case are run. As of now the cells in Homopolymer (AQ) are blank and I do not know why. Thank you.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    There's no worksheet/range reference here so you'll get an unqualified reference error when you try and run the code.
    Please Login or Register  to view this content.
    What are you trying to fill down anyway?

    By the way, don't try and do everything all in one go like this,
    Please Login or Register  to view this content.
    instead break things up, and throw some variables in.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: select case calculations not working

    You might need to turn off events at the start of your sub and on again at the end.

    Please Login or Register  to view this content.
    As the sub is making changes to cells, this change can trigger another instance of the same sub to be fired.
    Martin

  4. #4
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I have attempted a code and linked the workbook, hopefully this is close and stores the calculations in the Homopolymer column on the annovar sheet. Thanks.
    https://app.box.com/s/y03vfrczheor1f27y4l0

    So for the event_change:
    VB
    Please Login or Register  to view this content.
    and for the calculations select:
    VB
    Please Login or Register  to view this content.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    The sub Calculations takes no arguments, so you can't pass any arguments to it.

    You are trying to pass an argument to it here, so the code won't work.
    Please Login or Register  to view this content.
    Can you explain, in words, what you are trying to do with the code?

  6. #6
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    After the annovar worksheet is reformatted by the classify macro, in cell A2 a data validation dropdown allows the user to select a case #. Depending on that selection, text in F2 is either "Comprehensive Epilepsy or Marfan Disorder". The change in A2 triggers an event_change and the calculations macro runs depending on the text. The Panel tab has all the formula values in it that are used ib the calculations macro.

    So, if in A2 the user selects case 45 and the value in F2 is "Comprehensive Epilepsy", then the calculations for "Comprehensive Epilepsy" are run and the results are stored in the Homopolymer column on the annovar sheet. I hope this helps and thank you.

    If in A2 the user selects case 50 and the value in F2 is "Marfan Disorder", then the calculations for "Marfan Disorder" are run and the results are stored in the Homopolymer column annovar sheet.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Does this work at all?

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Please Login or Register  to view this content.
    gives no error, but the calculations dont work.

    Please Login or Register  to view this content.
    get an Argument not optional error. Thanks.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Where do you get the argument not optional error?

  10. #10
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Call Calculations is highlighted. Thanks.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Sorry but that doesn't make sense.

    Can I ask why you are even using the sub Calculations?

    The code in that sub could be moved into the change event.

    Just had a look at the workbook, which I think I've already downloaded in one form or another, there is no data validation dropdown in A2 on 'annovar' or 'panel'.

  12. #12
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    That might be an old copy. The new link has a more recent copy in it. How would the event-change code look with calculations in it? The calculated values need to be stored in the homopolymer column. Thank you.

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Eh, the code in the sub Calculations doesn't store data anywhere outside the sub.

  14. #14
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Will this code work or come close. Thank you.

    VB
    Please Login or Register  to view this content.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    If rData is the range you want the results of the calculations to go then I'm sorry but I don't think so.

    Even if it's not then there's nothing in the code that will put values into a range.

    Actually, I'm pretty sure that code won't compile so you won't be able to run it.

    Any chance you could mock up a file with (a lot) less, but still representative, data and then attach it here?

  16. #16
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I made it smaller and it still exceeds the attachment limit, so I put it on box.net. I also took out the change event and the calculations, but the classify macro is there.Thank you for your help.

    https://app.box.com/s/y03vfrczheor1f27y4l0

  17. #17
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    The file is still the same size, 2.3MB.

    The main reason the file is so large is because of the amount of data, is there no way you could cut that down and still keep it representative of what you are working with?

    If you can do that then you should be able to attach it here.

    Attaching it here will mean a lot more people will be able to have a look at the file - some folk are reluctant, or unable, to download files from sources outwith the forum.

  18. #18
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I attached a zip and the workbook, I believe it should illustrate the issue. Will this work? Thanks.
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    The zipped file attached has the ranges for calculations correct and should hopefully be a better copy. The classify and
    calculations macros are in the workbook as well as the change_event, but that is not working. Thanks.
    Attached Files Attached Files

  20. #20
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Does it have the data validation dropdown?

  21. #21
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    The file attached does, in order to run the classify macro (which contains the data validation dropdown), the change_event needed to be removed. The calculations macro is still there as well. Thank you.
    Attached Files Attached Files

  22. #22
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Sorry, still no data validation dropdown.

  23. #23
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Do you see the classify macro, after running it if you click on cell A2 it should be there. I can see it on my end. Thank you for your help.

  24. #24
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Right, I can get the code to run without error but it doesn't actually do anything.

    Here's the worksheet change code.
    Please Login or Register  to view this content.
    Here's the code for the sub Calculations.
    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Those are the correct codes, and the calculations, as is, dont seem to work. Thanks.

  26. #26
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Actually the Calculations code isn't all executed because the VLOOKUP here fails and returns an error to Res.
    Please Login or Register  to view this content.
    The Match here also fails.
    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Why are they failing, is there a more efficient way to do this? Thanks.

  28. #28
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    They are failing because what the VLOOKUP and MATCH are trying to find aren't being found where you are looking for them.

    Actually, if you adjust them both to work
    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    When I use the following code the Homopolymer column is blank. The text in blue define where the values are.

    The Q5 and R5 are from the annovar sheet

    The $ values are from the panel sheet.

    Thank you.

    Please Login or Register  to view this content.

  30. #30
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    You don't put anything back on to the sheet.

    This just puts the result of the formula you are evaluating in iHomopolymer, replacing the column you found earlier.
    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Oh, I am trying to put the result of the selected formula in the Homopolymer column on the annovar sheet. Im not sure how to do this. Thank you very much.

  32. #32
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Which row in the column do you want to put the result in?

  33. #33
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    The results can go in the Homopolymer (AQ) column, starting at row 5. The column position can change but the row number will always be 5. The amount of rows will vary.

    For example todays sheet may have 150 rows to put calculated values in, but tomorrows sheet may have 200 calculated values. Thank you .

  34. #34
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    This will put the result in row 5 of the Homopolymer column.
    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    The Homopolymer column is blank after changing the data validation dropdown.

    Does
    Please Login or Register  to view this content.
    Need to be:
    Please Login or Register  to view this content.
    Thank you.

  36. #36
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Not sure, but including the sheet name for ranges on 'Panel' won't cause any problems.

  37. #37
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Homopolymer is blank with the following: Thank you.

    worksheet_change
    VB
    Please Login or Register  to view this content.
    Calculations
    VB
    Please Login or Register  to view this content.

  38. #38
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Why are you going back to this?

    It doesn't work.
    Please Login or Register  to view this content.

  39. #39
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    How do I trigger the calculations off the chage in A2? Thanks.

  40. #40
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    When I remove the change_event the Homopolymer column is blank. Don't I need a change_event though in order for the classify macro to run, then stop untill the user selects a case in A2, then depending on that selection the calculations macro runs. Thanks.

  41. #41
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    The change event will only be call the Calculations sub if A2 has been changed.

  42. #42
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    A2 is being changed each time.

    For example after the classify macro runs A2 is blank then I select case 64 and "Comprehensive Epilepsy" results in cell F2, that is supposed to trigger the calculations macro for the Case Comprehensive Epilepsy. The results of those calculations are stored in the Homopolymer column. Thank you very much.


    Please Login or Register  to view this content.

  43. #43
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    So everything is now working?

  44. #44
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    No, it is not working and Im not sure why the change in A2 is not triggering the calculations. Thanks.

  45. #45
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    How do you know it's not?

    Changing A2 might be triggering the Calculations sub, but that sub might not be doing what you want/expect it do.

    To see if it is being triggered try putting a breakpoint on its first line using F9.

    Then try changing the value in A2 and see what happens.

  46. #46
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    When I insert a page break on the first line:

    Please Login or Register  to view this content.
    - nothing happens and the first line is highlighted yellow

    When I insert a page break on the second line:

    Please Login or Register  to view this content.
    - nothing happens and everything until THEN is yellow and starting at Call is Red.

    Thanks.

  47. #47
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    I don't know what you mean by a page break, that's for worksheets and printing.

    A breakpoint is a debug tool you can use to cause a break in the code at a particular point.

    I suggested putting breakpoint on the first line of the Calculations sub by selecting that line and then pressing F9.

    I've no idea what you are trying to do with the code you just posted but I've got to emphasis, again, that this is totally wrong.
    Please Login or Register  to view this content.

  48. #48
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    After the data is reformatted by the classify macro, the user selects the case from the dropdown in A2 and that changes the cell F2 to either "Comprehensive Epilepsy" or "Marfan Disorder". That change in F2 triggers the calculation macro for the desired formula. Thanks.

    Select_Change
    VB
    Please Login or Register  to view this content.
    Calculations
    VB
    Please Login or Register  to view this content.

  49. #49
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    So this is solved then?

  50. #50
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I will try it tomorrow, do you think it will work or at least get closer to the desired result? Thanks.

  51. #51
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    To be honest, no.

  52. #52
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I made the changes using the code above and as you suspected it does not work, what do you suggest? Thanks.

  53. #53
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    I suggest leaving the worksheet change code as it was, see below, and investigate the code/formulas in the Calculations sub.
    Please Login or Register  to view this content.

  54. #54
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    It does not appear that the calculations macro is being triggered. When I insert a breakpoint on line 1 of the calculations macro it turns yellow. I do not even know where to begin correcting this. Also, can
    Please Login or Register  to view this content.
    be Range("$CA5:$CG"), 6, 0) [/CODE]. The search range will always start at CA5, but the CG end can be variable (today it is 74, but tomorrow it might be 77). Thanks.

    Please Login or Register  to view this content.

  55. #55
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    If the line turns yellow it means that code execution has been halted on that line of code, which in turn indicates that the Calculations sub has been called.

    When that happens have you tried stepping through the code with F8?

    As for changing the range, yes it's possible but not like that, try using CA:CG.

  56. #56
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Stepping through the code

    Please Login or Register  to view this content.
    turns red. Thanks.

  57. #57
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Does the vlookup value need to be stored and then that cell referenced by the select statement?
    For example, if the user selects case 64 and the vlookup value is "Comprehensive Epilepsy", should that value be stored in a cell (F2) and referenced by the select statement in order for the calculation to run? Thanks.

  58. #58
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    No, the VLOOKUP value is stored in the variable Res.

  59. #59
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Do you have any sugesstions, I am stumped . Thanks.

  60. #60
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I attached an image, it appears the vlookup value is being stored in Res when A2 is changed, it is the forumulas that are causing the issue and not storing the value in the 5 row of the Homopolymer column. Thanks.
    Attached Files Attached Files

  61. #61
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    I know that the result of the VLOOKUP is being stored in Res and the result of the formulas is definitely going to row 5 of the homopolymer column.

    Have you considered that the formulas might not be returning the expected result?

    That would be the first thing I would check.

  62. #62
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    It appears that the calculations are storing one value in row 5 some of the time (Image). How do I offset it, so that it fills the entire column?

    For example, there are 192 rows (5-192) and only 5 has a value. The row number will vary each time the macro is run.

    Other times there is no value (Not there), I have no idea why this is, any sugesstions. Thanks.


    When I do a step though and hoover over the:
    VB
    Please Login or Register  to view this content.
    iHomopolymer = 43
    Attached Files Attached Files

  63. #63
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Have you checked the formulas yet?

  64. #64
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    When the Res = "Comprehensive Epilepsy" and I step through the code

    Please Login or Register  to view this content.
    iHomopolymer = 43 (this value doesent make sense)

    the way the formula should work is:
    Q5 = 1 and R5 = 40556922

    so those two values are used to search in panel in the ranges indicated and what results in the Homopolymer column is "No" because a match was not detected. If a match was detected the result in Homopolymer would be something like "9A" or "5C".

    Below is the manual formula I used to use each time (and what I hope can be automated)

    Please Login or Register  to view this content.
    The ranges in the formula are different, but the idea is the same. Thank you for your help.

  65. #65
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Why not?

    It's the column HomoPolymer is found in?

  66. #66
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I'm not sure why the formula seems to have a problem finding the correct values to use in the calculations. Is the formula syntax correct? Thanks.

  67. #67
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    I don't know if the syntax of the formula is right.

    One thing does look strange about it - R5.

    What is that supposed to refer to ?

  68. #68
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    I've added some extra code so you can check out the formulas, including some Stops which will interrupt code execution at the appropriate points.
    Attached Files Attached Files

  69. #69
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    Hopefully my explnation will help:

    Please Login or Register  to view this content.
    Q5 = annovar.Chr (1) - annovar is the sheet, Chr is the column, (1) is the row value
    R5 = annovar.Start (40556922) - annovar is the sheet, Start is the column, (40556922) is the row value


    all the search ranges, as well as vlookup, are from sheet panel:
    B$2:B$6713,C$2:C$6713,D$2:D$6713, VLOOKUP(R5,panel!$C$2:$E$6713,3,1), ""No"")")

    So, the Chr value from annovar is searched in the panel range indicated, then the Start value from annovar is searched in panel range indicated, next a vlookup is performed on this value and the result from the third column is put in the Homopolymer column on annovar. If there is no match found then the value "No" is put in the Homopolymer column of annovar.

    Using the numbers in the example, chr 1, start 40556922 does not match any criteria in the panel search range, so "No" would be the result in the Homopolymer column of annovar. I hope this helps and thanks.
    Attached Files Attached Files
    Last edited by cmccabe; 06-20-2014 at 12:50 PM.

  70. #70
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    Why do you use G2 in the formula on the worksheet and R2/Q2 in the formula in the code?

    Have you tried just putting the formula in the cell rather than the result?

    If you did that then you could check out the formula to see if it's calculating correctly.

    This would be how you would put the formula in the cell rather than the result.
    Please Login or Register  to view this content.

  71. #71
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    I get an application defined or object defined error 1004 when I use that code. Thanks.

    The G2 and R2/Q2 are the manual version of the formula (when all the values were on one sheet). That formula did work, but is time consuming. That is the formula I am trying to modify for VBA, but not having luck so far. Thanks.

  72. #72
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    This seems to work, for 'Marfan Disorder' anyway - I don't know how to trigger the other option 'Comprhensive Epilepsy'.
    Please Login or Register  to view this content.

  73. #73
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    When I step through the code

    Res = Marfan Disorder

    formula result for Marfan Disorder (iHomopolymer = 43), this value doesn't make sense and the column on the annovar sheet is blank. Thanks.

  74. #74
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    You need to check the formulas.

    Is the syntax correct?

    Should you be passing any values/references to them from the code?

  75. #75
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    What does:

    Please Login or Register  to view this content.
    refer to that = Nothing when I step through the code below: Do I also need to define the R5 and Q5 values? Thanks.

    VB
    Please Login or Register  to view this content.

  76. #76
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    That is the first linr that has a value = nothing, so maybe that is causing the formuls not to run? Thanks.

  77. #77
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: select case calculations not working

    There is nothing in that code that will cause the formulas not to run.

  78. #78
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    Re: select case calculations not working

    It has got to be the formula syntax, but I have no idea how to correct it or even where to begin, any suggestions. I know the value is being stored/transferred to Res, but the formulas do not calculate. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. select case calculations not working
    By cmccabe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-12-2014, 04:07 PM
  2. Select Case possibly not working as intended. (Another strange bug)
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-16-2013, 10:40 AM
  3. my Select Case function is not working correctly
    By DorothyFan1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-25-2011, 07:56 AM
  4. [SOLVED] Case Select not working
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2010, 06:53 AM
  5. Select Case not working
    By RadBrad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-31-2009, 01:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1