+ Reply to Thread
Results 1 to 19 of 19

cell references help needed

  1. #1
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Smile cell references help needed

    Hello
    I am having a problem with the syntax of cell references in two places in an Xcel program.
    Someone may be able to see the problem with the code very quickly.
    Appreciate any code change suggestions.

    1) I am trying to write general code that will handle any number of components (=M).

    Can you see what is wrong with this line of code:
    Please Login or Register  to view this content.
    as shown below?

    All the ‘commented out code is what the for loops are meant to do and is code that works when I run it specifically for M = 5

    Please Login or Register  to view this content.

    2) I cannot get
    Please Login or Register  to view this content.
    to work below.

    Again the ‘commented out code that it is designed to replace, works

    Please Login or Register  to view this content.
    I’ve tried different coding w/o success.

    Last edited by DaveCott; 07-13-2011 at 02:59 AM. Reason: typos

  2. #2
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: cell references help needed

    help still needed

  3. #3
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: cell references help needed

    1) I think you need to create another variable, say k, to replace -30 - i because the RC reference won't accept a calculation. Then you can use
    Please Login or Register  to view this content.
    Note that the text between the quotes is exactly what will appear in the spreadsheet so you don't want the i or k to be in quotes as you want their values to be shown instead.

    2) Sorry I don't really understand what you're trying to do with
    Please Login or Register  to view this content.
    . Could you explain a bit more, please?

  4. #4
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: cell references help needed

    Thanks Huron, I'll try your advice. Will R[" & i & "] work, whereas R[i] won't?

    What I am trying to do with the Pred = Pred + Solver!RC" & 31 + i & " * "Input!R" & 6 + i & "C[2]" code is generate the code ActiveCell.FormulaR1C1 = _
    "=((Input!R[22]C[2]/Input!R24C[2]-(Solver!RC32*Input!R7C[2]+Solver!RC33*Input!R8C[2]+Solver!RC34*Input!R9C[2]+Solver!RC35*Input!R10C[2]+Solver!RC36*Input!R11C[2]))^2)" more generally for any values of M, i.e. for i varying.

    I can write the above formula for each case of M, expanding it by the appropriate +Solver!RCx*Input!RyC[2] term and it would work (or has so far for M = 2 to 5 that I have done) but M can be anything from 2 to 15, so I am trying to make it neater by using the code I have shown in the box so I can avoid writing 15 cases. Trouble is it won't run OK as-is.

    Pred = Pred + Solver!RC" & 31 + i & " * "Input!R" & 6 + i & "C[2]" may not be the only coding error of course in this segment of code. I'll have to deal with any other errors one by one as I correct lines of code.

    Appreciate any extra advice.
    DC

  5. #5
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: cell references help needed

    thanks - your tip for the first problem appears to have fixed the problem all through this part of the sub, just having problems with:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: cell references help needed

    Quote Originally Posted by DaveCott View Post
    thanks - your tip for the first problem appears to have fixed the problem all through this part of the sub, just having problems with:

    Please Login or Register  to view this content.
    down to solving why
    Please Login or Register  to view this content.
    does not work now.

    Please Login or Register  to view this content.
    being the problem

  7. #7
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: cell references help needed

    Try
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Cool Re: cell references help needed

    thanks, problems with this still
    1) when changing to your suggested code I'm not sure how the C[2] at the end of the original code for the input sheet is reflected in your suggested code as it seems to be missing. Column seems to be 1, not 2 to the right, i.e. [2]?
    2) my code first positions you at B9, so the row on the solver sheet for the first loop is row 9, does your code put it at row 9 or 1?
    3) when I ran your code it then stopped at
    Please Login or Register  to view this content.
    , so something wrong with that next.
    4) when I replaced the above code with ActiveCell.FormulaR1C1 = Pred to try to see what was happening these cell values were zero, which suggests the calculation of pred was not working yet.
    ..so back to the drawing board.

    How is the weather in Hampshire anyway? Nice winter's day here.

  9. #9
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: cell references help needed

    Could you upload a copy of your spreadsheet after removing any sensitive information and with a small amount of data? It will make it much easier to see what's happenening. Just click Go Advanced and click on the paper clip.

    By the way, it's a typical summer's day here - cool, wet, windy and grey.

  10. #10
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Thumbs up Re: cell references help needed

    Hi Huron
    Back in my office after the weekend.
    I've made a copy of the relevant part of the sheet with data for M=5. The sub CalcSS() in the solve module for the case 5 (ie M=5) works OK with the specific code for M=5.
    I have commented out the more generic code I was trying to create, with your help, for M = anything from 2 to 15. teh geenric code does not currently work. What would be helpful is getting the generic code that uses pred to work. I would then replace all the specific code for cases 2 to 15 with this (i've coded cases 2-6 so far). You may need to add in solver.xla to project references to enable solver to run.
    Look forward to seeing what you can come up with.
    Attached Files Attached Files

  11. #11
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: cell references help needed

    Try this workbook to see if I'm on the right lines.

    The code could still be improved, but let's see if it works first.

    I'm not sure about the If statement in Sub CalcSS, the else option.
    I suspect this statement could be redundant but would need to know the rules a bit better.

    Maybe a sample where we could choose the options would be an advantage?

    Hope this helps.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  12. #12
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Talking Re: cell references help needed

    Hello marcol. I spent 3 months in Scotland last year and really enjoyed it.
    Thanks for taking a look at this. From my quick run of your sheet it appears to work.
    I will now apply your coding logic to the whole section including the different options in the larger workbook and see if it contimues to work. I expect it will but this will take me awhile.
    If it does work I will then immediately hit solved and rate you and huron highly as you have saved me much web trawling as computing coding is not my area of expertise. Thanks again.

  13. #13
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: cell references help needed

    It worked. Thanks again. I hit the scales of you both.

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: cell references help needed

    Bit of a lucky guess with the if statement!
    I feel that you could just use the else option and this part (cell)
    Please Login or Register  to view this content.
    would return 1 when n =4

    It could at least be simplified a little try this statement
    Please Login or Register  to view this content.

    As you will have noticed, I left the formula breakdown in the workbook to help explain the logic and build up. With long formula this is often a good way to tackle the problem in VBa.

    Anyhow, it seems to fit the bill, and that's good.

    Thanks for the rep and glad to hear you enjoyed your stay in our part of this little island, as we say "Na bi fada gun tilleadh" or in the English "Haste ye back."

    Slainte
    Alistair

  15. #15
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: cell references help needed

    Thanks Alistair. I can see that your suggestion is a bit neater for when n=4. The front weighting term just drops out then as it is =1. The weights I will actually use are not 1 but they were not needed to fix the code. I did see the formula sheets and your logic to create lead, body and tails of equations. My problem was that I am using a very dusty memory of basic/fortran over 30 years ago at Uni. with no VBA training, so I don't know the legal VBA syntax in some situations, ie. I'm fine with the logic but not always the syntax. This bit is actually only a small part of the workbook as the solver solutions have to be processed a lot further. Thanks again for your helpful look at this.

    Our 3 months was spent at SAC, Penicuik, living in Gorebridge last year. We were there during the Edinburgh Festival which was great, as was the Glass House off the mile from memory.

  16. #16
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: cell references help needed

    PS, your neater coding worked fine.

  17. #17
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: cell references help needed

    Hey, if you remember the festival, you weren't there! ... ... ,,,

    Na bi fada gun tilleadh (Haste ye back)

    Slainte
    Alistair

  18. #18
    Registered User
    Join Date
    07-05-2011
    Location
    Armidale, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Cool Re: cell references help needed

    Remember bits of it! Spent more money on shows than grog! We hope to get back. Really enjoyed our travels up north&west of Aberdeen. Finished my Talisker whisky so will have to go back one day.

  19. #19
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: cell references help needed

    Try Lagavulin next time, you might just stay for ever!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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