+ Reply to Thread
Results 1 to 7 of 7

VBA - Static code to dynamic

  1. #1
    Forum Contributor
    Join Date
    03-13-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    152

    VBA - Static code to dynamic

    Hi,

    Please have a look at the attached file, "HB Data" is the original sheet and got the following code from this forum


    Please Login or Register  to view this content.
    The above code works fine if I have the data as per the 'HB Data' sheet, I know if additional data added as in the 'HB Data_new' then the above code won't work. So I need to convert the above code to dynamic.

    Kindly help me please.

    Originally I had
    Please Login or Register  to view this content.
    But it was not working, so I got help and above code from this forum.

    Thanks
    Attached Files Attached Files
    Last edited by gan_xl; 03-06-2014 at 11:53 AM.

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

    Re: VBA - Static code to dynamic

    What, in words, is the code meant to do?

    Also, why do you have the Select Case when the value of Position is fixed?
    If posting code please use code tags, see here.

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA - Static code to dynamic

    What are you actually trying to achieve?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Forum Contributor
    Join Date
    03-13-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: VBA - Static code to dynamic

    Hi Olly,

    My input value is '16', so its nearest smallest number is 14 (E2), its nearest smallest number position is 3 (E1) and its nearest biggest number is 19 (F2).
    I got the nearest smallest number position value using match function and cross checking position value with the headers (1,2,3,4,5) if position value matches with the header value then the cell below matched header will be smallest number and the next cell will be nearest biggest number.

    Please let me know if it is still not clear

    Thanks
    Last edited by gan_xl; 03-06-2014 at 11:53 AM.

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

    Re: VBA - Static code to dynamic

    Where in the code are you inputting a value and determining the position?

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA - Static code to dynamic

    Okay - here's a coupe of UDFs which should help:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    03-13-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: VBA - Static code to dynamic

    Olly, It works fantastic. Thank you so much

+ 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] Changing code from Static to Dynamic
    By JamesFletcher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2013, 04:33 AM
  2. [SOLVED] Changing static code to dynamic code
    By JamesFletcher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2013, 07:31 AM
  3. [SOLVED] Make Static code Dynamic
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-05-2012, 11:09 AM
  4. Static Cell Value in Reference with Dynamic one?
    By Rick-O-Shay in forum Excel General
    Replies: 1
    Last Post: 01-11-2011, 02:50 PM
  5. Turn static RANGE to dynamic
    By Barmoley in forum Excel General
    Replies: 7
    Last Post: 11-14-2010, 04:33 PM

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