+ Reply to Thread
Results 1 to 8 of 8

Select nearest value based on two variables

  1. #1
    Forum Contributor
    Join Date
    03-03-2011
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    123

    Select nearest value based on two variables

    Hi there again

    This one is a bit above my ability
    See the attached workbook:

    On the VBA form Glass , go to the Fenestration Tab, on the SHGC sub tab:

    The variables
    P/H value (txtPHvalue)
    The orientation (The combobox)
    The Zone (in the lower right corner)


    Now, based on the Zone and the orientation, I am looking for a code that will find the value in the shading sheet in Col A

    The Shading Sheet consist of 6 Tables, one for each Zone. It is clearly marked.

    An example what t must do

    When the P/H value is 0.21, the Zone is 4 it must find the value nearest to 0.21 in the table A83:107 (which is the table for Zone 4). Then, with the orientation to south, it must find the value in the F column (which is south)

    I know it is quite complicated, but I wil really really appreciate some help

    Best regards,

    Jakes
    Fenestration Calculator.xlsm

  2. #2
    Forum Contributor
    Join Date
    03-03-2011
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Select nearest value based on two variables

    Ok, So far I was thinking about the following method:


    Each Zone is a case, and each case search specific cells for the value.

    Here is the code (obviously not working)

    I also attach a newer version of the spreadsheet, added a textbox where the factor need to go in.

    Please Login or Register  to view this content.
    Fenestration Calculator.xlsm

  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select nearest value based on two variables

    hi Jakes, option, please check attachment

    As you did not specify where the result value should go I put it in txtGvalue.
    I've also changed the showclimatezone control to combobox with values from 1 to 6. Hope you do not mind.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    03-03-2011
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Select nearest value based on two variables

    Hi Watersev
    A BIG thanks, it is working almost perfectly. Only one thing I see,and I hope you can help me with, s that it does not select the nearest value, , for example when I work with the value 1.01, it select the row of 1.1, instead of 1.0. SO it looks for the closest value higher, and not lower as well. I will play around, see if I can fix it.
    Otherwise, it is exactly what I wanted. Thanks a million!

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select nearest value based on two variables

    please check attachment, the code corrected

    What should happen if the closest values are equally outstanding from the searched value: searching for 1.05, 1st closest - 1, second closest - 1.1?
    As for now it takes 1.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-03-2011
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Select nearest value based on two variables

    Awesome! Thank you!
    Could I ask a small favour? can you please indicate what has changed so I can incorporate it into my workbook, as I already added some other stuff.

    Appreciate it!

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Select nearest value based on two variables

    there were two changes (one line deleted, one amended) in Heat_Gain_calc code in the last posted version if that's what you mean

  8. #8
    Forum Contributor
    Join Date
    03-03-2011
    Location
    South Africa
    MS-Off Ver
    Excel 2007
    Posts
    123

    Re: Select nearest value based on two variables

    Thanks, will use the code.
    A million thanks!!

    Jakes

+ 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