Posts by analystbank

  • Second Largest or Second Smallest Value

    I'm using Excel 97-2003 version.


    In a spreadsheet, I have a threshold limit (Number) in Cell N126 which i want to compare with number values in Column Range N130:N161 and return second highest value in Cell P131 where i am getting the second highest value result with Array formula

    Code
    =INDEX(N130:N161,MATCH(TRUE,N130:N161>N126,0))

    . Cntrl Shift Ent


    Similarly, I want to retrieve second smallest value, compared to cell content of N126 in cell Q131, where i am getting confused, and seek help.


    Any better suggestion to existing formula is also welcome whereby desired result can be obtained.


    Later, I want to simply combine both formula and get the desired result anywhere in spreadsheet based on criteria of Cell M124. To Illustrate, IF M124="B", then retrieve second highest value, else, second lowest value.


    If I have not explaied well here, i am attaching sample file with color coding and what exactly i seek to avoid confusion.


    Look forward for suggested corrected formula, thanks.

  • Strange Compile Error at startup

    I've been making use of this UDF http://www.ozgrid.com/VBA/ExtractNum.htm in one of my workbook for several years with no error.


    My Excel workbook was made in version 1997-2003 and have been making use ot it on higher MS-Office version with no problem all these years.


    Suddenly today when i am opening my Excel workbook, i get following error message at startup


    Code
    Compile Error
 Automation Error


    at start line only which is


    Code
    Function EN(rCell As Range, _
     Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double


    When i ignore the message, (click Ok), UDF continue work well but such error message on start up annoys? What possibly could have gone wrong, and how to avoid such messages, as in past, there was no such error message.


    Likewise, in another workbook, same error as quoted above comes in one of the macro which starts like


    Code
    Sub SortData(ByRef Destination As Range, ParamArray SourceData())

  • Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

    Re: Formatting Range of Cells of within gap of 3 value / Factor for 3 levels


    Ger Plante, simply superb, Sir, exactly what i wanted.


    Kindly have a look at A9 (188) and B9(192) this has gap/factor of 4 points, so either one of A9 (188) or B9(192 ) should have been ignored. Nonetheless, this could be due to comparison of it with B8 (189) and hence both got selected.


    See, if doable, else, your given code is perfectly alright. Thanks, and Awesome.


    Suppose, if i want to tweak the code with only 2 scenario / level only and with gap /factor of only 2 instead of 3 currently, which line i should change.


    For Factor of 3, i guess, you set the line at


    Code
    Const GAP = 3


    When i changed this to 2,
    it ignored Grey color shading in cell D2 (337) and H2(340) but


    continued Green color shading in cell B2(347) and G3(345) ?


    If i reduced the number of color to any level less than yours , you have set 5 color, what difference it will make?

  • Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

    Re: Formatting Range of Cells of within gap of 3 value / Factor for 3 levels



    Thanks for looking in.



    You are right,
    C8 Yes, that is manual /inadvertent mistake, and it should have been colored, Green. Sorry for this inadvertent mistake.



    Normal Formatting or Conditional formatting either will do, as after running the macro, it should highlight such set of data. (If possible allow to see how conditional formula would have been set for such work.)



    Yes, No summary is needed, since possible block would have range that can fit on the screen, and 3 or lesser colored set could be visually seen.

  • Formatting Range of Cells of within gap of 3 value / Factor for 3 levels

    Hello Seniors,


    I want a macro that will


    1) allow / Prompt user to select the Range of cells across Rows & Columns, and do the following


    2) From Range of Cells at three levels (max 3, or less than three is also okay, and if there is no such cases, nothing to highlight or no message to be given).

    In Sample Workbook, attached here, the Range to be evaluated is A6:H9. However, on user selection, it could vary in terms of Row, and Columns, but dont expect it to
    be more than 10 x 10 matrix.


    3) Now upto 3 levels, Each of cell value should be compared within range as mentioned in 2) above, within value / factor of 3 points, and if matched, should be highlighted through color.


    4) In the given workbook, just for illustration purpose, i have done this manually, and applied different color to make it clear, what i seek.


    5) Just for illustration purpose, i have highlighted upto 4 levels (4 colors, where numbers are within 3 factor/point from each other), but it is okay, if macro or formula could highlight upto max 3 levels only.


    Thanks

  • Run time error 13 Type mismatch in VBA

    One of the VBA sentence in my existing code is


    Code
    Dim lngRows As Long 
Cells(lngRows, "Q").FormulaR1C1 = _
"=(IF(RC[-15]=""JOHN"",""JN"",IF(RC[-15]=""QUEEN"",""QN"",IF(RC[-15]=""KINGDOM"",""KG"",""""))))&LEFT(RC[-12],1)&RC[-13]&TEXT(RC[-14],""DD"")&UPPER(LEFT(TEXT(RC[-14],""MMM""),2))&TEXT(RC[-14],""YY"")"


    while executing the Macro, i get error 13 Type mismatch




    What i need to change, so that, i dont get error, and it gets executed.

  • decimal value derived through formula acting as text, how to make it as value

    Re: decimal value derived through formula acting as text, how to make it as value


    yes, i dont know why it happens, and i am apology for that. When i posted there [ http://www.excelforum.com/showthread.php?t=1167361 ] , and since then, forum (website) does not load on my machine, so, i requested here. I tried opening in separate TAB, and search if draft was posted or not, and i could not see the thread, so presumed request was not made, After couple of hours, Excelforum, still not loading on my machine, and i was badly looking for the solution so with no bad intention it was posted here. Offence is offence, and i apologise for the same. Earlier also, this happened and it was here only. NExt time, i will wait for more time. I sincerely apologise, again.

  • decimal value derived through formula acting as text, how to make it as value

    EXcel 2007 and above

    I have random value say, 3254.70 in cell A9



    I want to extract only the decimal part, so, in cell B9 (can try either of the formula, as both yield same result)
    EITHER
    =A9-TRUNC(A9)



    OR



    =MOD(A9,1)



    both would bring the decimal value 0.7 or 0.70 BUT when i am testing it IN Cell B10, with IF formula, like =IF(B9=0.7,7,0), it shows false results, and returns 0 (return value on non fulfilling conditions)



    I tried using VALUE function also which works with INT / TRUNC function (but not with TRUNC or MOD), but no desired result.


    What formula i should use to get only decimal value, and test it with IF function, to ensure certain other work.

  • Extract Data Table information from Java xHTML page

    Re: Extract Data Table information from Java xHTML page


    Quote

    Are you referencing libraries ?..


    Whatever you suggested, I activated but not working sir. Macro does run without errror, and returns text portion (mainly data header from web page) but it does not bring in respective numerical value.


    I tried using Excel 2007 also, but same issue.


    Further, given the quantum of more data to bring in and further processing involved, it would not be prudent to open IE browser, and bring excel data for different URLs. Just sharing my thought based on facts. Thanks.

  • Extract Data Table information from Java xHTML page

    Re: Extract Data Table information from Java xHTML page


    :( No success.


    But thanks for all your efforts. I'll try to get it in Ecel 2007. Give sometime for feedback.


    Quote

    are you using the workbook i have attached?
    it has all the library reference checked.. it very strange you dont get any thing at all


    Yes, I did. I use the same workbook, as you shared. When I downloaded the workbook, that you shared, i could see, all numeric data also, but when i run the query, it just returned text part.


    I just get the header names, Text portion. All numerical data just does not appear. Could it be due to version difference. Currently, I m using excel 2003.

  • Extract Data Table information from Java xHTML page

    Re: Extract Data Table information from Java xHTML page


    Thanks, for all the efforts, Sir. Somehow, does not work, and bring in the data. I close this thread, but sincerely appreciate your efforts, to solve this query.


    I would look for some alternates, which could be easier, and that being non Web related query but on this subject only, would start a new thread, here. Trust, no offence.


    Thanks a ton, once again.

  • Extract Data Table information from Java xHTML page

    Re: Extract Data Table information from Java xHTML page


    Thanks, Sir. with reference to post #18


    Again data under TAB 'Fundamentals', (TAB5Content of xhtml line number- starting from 604) which appears on Left hand side, and Top header, which gives, Open, High, Low, last close, (line 586 onward) does not appear.


    Moreover, If i run the macro, i just get the header names, not the numeric data. I am using Excel 2003 version. When I opened the attachment, above, it nicely brings in numeric data inside the table, except FUNDAMENTALS (TAB5Content of xhtml line number- starting from 604) , and latest Open, High, Low, and last traded price of the stock.


    I am still impressed with the code that you gave in # 5. And really scratching my head, as to how do i navigate through (TAB5Content of xhtml line number- starting from 604) and also get data from line 586 onward that has Open, High, Low, last traded price, alter Array and try to get it in excel.

    I am more concern with data Tab5Content and Open, High, Low, which is the latest information, which requires further analysis.