+ Reply to Thread
Results 1 to 22 of 22

Incorporating 2 codes/Functions together!

  1. #1
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Post Incorporating 2 codes/Functions together!

    Hi,

    I have a code that brings in specified inputs from a worksheet incrementally (Min, max, increment values specified). I.e min = 1, max = 5 & increm = 2 would result in 3 unique solutions as the output.

    I also have a code that brings in a list of values that have been specified in a column on the same worksheet to an equation. I.e A list of 5 unique inputs would result in 5 unique solutions at the output.

    My aim is to now incorporate both codes into one function so that I can bring in a range of inputs for one part of an equation, whilst also bringing in an incrementally rising input for another part of the same equation. For example, if i had a list of inputs in a column ranging from 1 to 5 (ie 1, 2, 3, 4, 5) and another input with a min value of 1, a max of 5 and increments of 2 (ie values of 1, 3, 5) I should get 15 individual solutions.

    Any advise, tips, pointers or help with combining these codes will be much appreciated!

    Here is the code that brings in inputs incrementally between a specific min & max.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    Here is the code that inputs a listed set of values from the worksheet column.

    Please Login or Register  to view this content.

    Individually they both work as intended.

    I have combined them as follows.

    Please Login or Register  to view this content.

    This script does not produce any error messages, but it does not work as intended.

    Any help will be very much appreciated.
    Last edited by AliGW; 02-17-2018 at 08:27 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Hi AlphaFrog,

    Thanks for your reply.

    This amended code still neglects the changing 'D' term. I.e only 3 outputted solutions instead of 15.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    I got 5 columns (each D) of 3 outputs (rows) = 15

  5. #5
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Apologies, I stand corrected.

    Ideally the 15 outputs should be in 1 column - in this case column M.

    Could you advise on how to place the solutions there instead?

    Thanks,
    HU

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    Quote Originally Posted by HonestlyUgly View Post
    Apologies, I stand corrected.

    Ideally the 15 outputs should be in 1 column - in this case column M.

    Could you advise on how to place the solutions there instead?

    Thanks,
    HU
    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 02-17-2018 at 09:42 AM.

  7. #7
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Perfect, thank you for your time AlphaFrog.

    Regards
    HU

  8. #8
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    You're welcome. Thanks for the feedback.

  9. #9
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Hi AlphaFrog,

    I have come across a further problem. I have reduced the min, max and increment values to 0.07, 0.15 and 0.04 respectively. This results in Run-time error '6': Overflow occurring.

    I have attempted to rectify this by changing 'As Long' to 'As Double' but this has not solved the issue.

    Do you know why this error is occurring?

    HU

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    It works for me when I changed these to Double.

    Please Login or Register  to view this content.

    When you get the error, what line is highlighted when you click the Debug button on the error dialog?

  11. #11
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    I made this change and it solved error 6. It resulted in Run-Time error '7': Out of memory arising. I managed to resolve this issue.

    The new code produces 15 solutions with the original inputs but only 10 with the updated inputs.

    I have attached an example .xlsm document to highlight this.
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    Quote Originally Posted by HonestlyUgly View Post
    The new code produces 15 solutions with the original inputs but only 10 with the updated inputs.
    That's correct. Do the math.

    0.15 (max) - 0.07 (min) = 0.08 / 0.04 (inc) = 2 * 5 (D values) = 10 results

  13. #13
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Also, 20 (max) - 10 (min) = 10 / 5 (inc) = 2. But this still gives 15 solutions

    I was under the impression that it would produce 3 sets of 5 using v as 0.07, 0.11 and 0.15 just as its originally used 10, 15 and 20

  14. #14
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    Try this...

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Thank you once again!

  16. #16
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    AlphaFrog,

    I have attempted to modify this macro so that it incorporates 2 inputs that vary incrementally and 2 inputs that change at the same time as each other as specified in lists.
    I have been unsuccessful.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    Could you help here?
    Thanks,
    HU

  17. #17
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    Quote Originally Posted by HonestlyUgly View Post
    AlphaFrog,

    I have attempted to modify this macro so that it incorporates 2 inputs that vary incrementally and 2 inputs that change at the same time as each other as specified in lists.
    Too vague. Provide an expanded description and attach an example workbook with expected results.

  18. #18
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Post Re: Incorporating 2 codes/Functions together!

    I have a relation with 4 variables (v, q, d, Fs) and several constants.

    v and q rise incrementally. The the min, max and inc values are stated in the worksheet. (3 individual inputs each)
    d changes as specified in a column within the worksheet, whilst Fs directly related to d and changes as d changes. (5 individual inputs each)

    This means there will be 45 individual outputs.

    I wish for inputs v, q, Fs and d to be inputted as follows.

    v1 d1 Fs1 q1
    v2 d1 Fs1 q1
    v3 d1 Fs1 q1
    v1 d2 Fs2 q1
    v2 d2 Fs2 q1
    v3 d2 Fs2 q1
    v1 d3 Fs3 q1
    v2 d3 Fs3 q1
    v3 d3 Fs3 q1
    v1 d4 Fs4 q1
    v2 d4 Fs4 q1
    v3 d4 Fs4 q1
    v1 d5 Fs5 q1
    v2 d5 Fs5 q1
    v3 d5 Fs5 q1
    v1 d1 Fs1 q2
    v2 d1 Fs1 q2
    v3 d1 Fs1 q2
    v1 d2 Fs2 q2
    v2 d2 Fs2 q2
    v3 d2 Fs2 q2
    v1 d3 Fs3 q2
    v2 d3 Fs3 q2
    v3 d3 Fs3 q2
    v1 d4 Fs4 q2
    v2 d4 Fs4 q2
    v3 d4 Fs4 q2
    v1 d5 Fs5 q2
    v2 d5 Fs5 q2
    v3 d5 Fs5 q2
    v1 d1 Fs1 q3
    v2 d1 Fs1 q3
    v3 d1 Fs1 q3
    v1 d2 Fs2 q3
    v2 d2 Fs2 q3
    v3 d2 Fs2 q3
    v1 d3 Fs3 q3
    v2 d3 Fs3 q3
    v3 d3 Fs3 q3
    v1 d4 Fs4 q3
    v2 d4 Fs4 q3
    v3 d4 Fs4 q3
    v1 d5 Fs5 q3
    v2 d5 Fs5 q3
    v3 d5 Fs5 q3



    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Thank you,

    modifying this even further so that there are 3 inputs that vary incrementally and 3 inputs that are taken from a list has proved difficult.

    Please Login or Register  to view this content.

    What part needs fixing to allow this to work?
    Attached Files Attached Files

  21. #21
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Incorporating 2 codes/Functions together!

    You were very close

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    02-17-2018
    Location
    United Kingdom
    MS-Off Ver
    2013
    Posts
    20

    Re: Incorporating 2 codes/Functions together!

    Great.

    Reverting back to a previous solution you provided, I'd like to know how to add an extra component to that also.

    I now wish to bring inputs to an relation in the following way, still yielding 45 solutions.

    v1 d1 Fs1 q1 E1
    v2 d1 Fs1 q1 E1
    v3 d1 Fs1 q1 E1
    v1 d2 Fs2 q2 E1
    v2 d2 Fs2 q2 E1
    v3 d2 Fs2 q2 E1
    v1 d3 Fs3 q3 E1
    v2 d3 Fs3 q3 E1
    v3 d3 Fs3 q3 E1
    v1 d4 Fs4 q4 E1
    v2 d4 Fs4 q4 E1
    v3 d4 Fs4 q4 E1
    v1 d5 Fs5 q5 E1
    v2 d5 Fs5 q5 E1
    v3 d5 Fs5 q5 E1
    v1 d1 Fs1 q6 E2
    v2 d1 Fs1 q6 E2
    v3 d1 Fs1 q6 E2
    v1 d2 Fs2 q7 E2
    v2 d2 Fs2 q7 E2
    v3 d2 Fs2 q7 E2
    v1 d3 Fs3 q8 E2
    v2 d3 Fs3 q8 E2
    v3 d3 Fs3 q8 E2
    v1 d4 Fs4 q9 E2
    v2 d4 Fs4 q9 E2
    v3 d4 Fs4 q9 E2
    v1 d5 Fs5 q10 E2
    v2 d5 Fs5 q10 E2
    v3 d5 Fs5 q10 E2
    v1 d1 Fs1 q11 E3
    v2 d1 Fs1 q11 E3
    v3 d1 Fs1 q11 E3
    v1 d2 Fs2 q12 E3
    v2 d2 Fs2 q12 E3
    v3 d2 Fs2 q12 E3
    v1 d3 Fs3 q13 E3
    v2 d3 Fs3 q13 E3
    v3 d3 Fs3 q13 E3
    v1 d4 Fs4 q14 E3
    v2 d4 Fs4 q14 E3
    v3 d4 Fs4 q14 E3
    v1 d5 Fs5 q15 E3
    v2 d5 Fs5 q15 E3
    v3 d5 Fs5 q15 E3

    How would the 'q' term be added properly? In this case q is drawn from a list of 15 meaning it does not have to repeat itself (it already has a relation to E).

    Please Login or Register  to view this content.

    The attached file has the inputs and the expected result for the first solution.
    Attached Files Attached Files
    Last edited by HonestlyUgly; 03-01-2018 at 06:22 AM.

+ 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. [SOLVED] Functions in VBA codes for other languages than english
    By Maradona_10 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-28-2014, 10:14 PM
  2. Replies: 1
    Last Post: 12-10-2013, 12:28 AM
  3. Replies: 0
    Last Post: 10-09-2013, 07:59 PM
  4. Incorporating/Using TimeBomb Codes Into Sheets
    By Eric Excels in forum Excel General
    Replies: 6
    Last Post: 10-05-2011, 07:42 PM
  5. where can i find macro codes for various functions such print, save and save as
    By fellayaboy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2011, 02:06 AM
  6. Replies: 6
    Last Post: 12-21-2010, 01:37 AM
  7. Incorporating Matrix Functions From Excel
    By LHenkel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2005, 09:06 AM

Tags for this Thread

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