Posts by Om Avataar

    Re: Maintaining Range Integrity in Common Functions

    Thanks, Ger, for your prompt help... The 'Target' term in the header of your suggested Private Sub - is that like a 'dummy' placeholder variable I need to replace in order for the code to work for me? I must apologize, but I have never used ByVal before...

    Please don't laugh, but where exactly do I place your Private Sub? Pls allow me to clarify...
    The worksheet in which the macro button resides is called 'STATIC DATA' - which is also where Cell C3 is. The underlying (Public Sub) code is written in (default) Module1 of the Project Explorer window. I also see the 'Sheet2 (STATIC DATA)' Excel object in the same Project Explorer window...

    So, where exactly do I need to put your Private Sub code?

    i) In Module1? (above my Public Sub entry? below? doesn't matter?)

    ii) In 'Sheet2 (STATIC DATA)'?

    iii) Or in Module2? (Module2 thus far unused)

    Pls advise. Thanks.

    Hi VBA Gurus,

    I have a seemingly very simple problem:

    In Cell C3, I need for the formula to ALWAYS remain: =SUM($C$6:$C$1005)

    Seems simple enough, but I also have a macro which conditionally deletes multiple rows within this same range of rows (i.e. rows 6 to 1005). To my horror, I have noticed that if upon a certain macro run 25 rows got deleted, the formula in Cell C3 automatically changes to =SUM($C$6:$C$980) - which is unacceptable for my program in the long run.

    Please advise a formula I should put into cell C3 [in place of =SUM($C$6:$C$1005)], which ensures cell C3 always displays the sum of the FIXED range C6:C1005.

    Thanks a million in advance... And please note that I need a formula expressly written into cell C3; performing said summation in aforesaid macro via code, and assigning the result to cell C3 via same macro is NOT an option. Please don't ask why... trust me, you don't want to know! :)

    - Om

    Re: WorksheetFunction.CountIf

    Hi Dave... Thanks for your reply. Just needed some clarification; pardon my ignorance.

    'Ranger' is a named range in the active worksheet itself. Do I still need to introduce a Range variable to act as a handle for referencing 'Ranger'?

    Pls advise... Thanks.

    - Om

    Hi... My VBA macro code registers an error at the following line of code:

    Overlaps = WorksheetFunction.CountIf(Ranger, CurrCred)

    , and I get an error message stating: "Run-time error '424': Object required"

    Please note that in the code above, I am just trying to store in variable 'Overlaps' the number of times customer 'CurrCred' appears in named range or column 'Ranger'

    Please explain what I'm doing wrong... Thanks a million in advance.

    - Om

    Re: Sample Multiple-Constraints Optimizer Program

    Dear VBA Gurus,

    Perhaps I was not very clear in stating my problem in the thread starter. Here is a more detailed version.

    Say I would like to automate, using Excel/VBA, the task of selecting an OPTIMAL set of 100 items from a database, or 'universe', of 1000 or more items. That is, a set of just 100 that simultaneously meets multiple performance constraints at the set or 'portfolio' level (i.e. the constraints are mainly weighted averages of various common parameters across all items in the OPTIMAL, and universe, sets).

    Would any of you have done something like this or similar using purely Excel/VBA? I understand it is NOT possible to automate such an optimal set selection using just Excel's built in 'optimizers' - Solver and Goal Seek functions.

    Would you have a sample Excel program which implements something like this for even just 2 'dummy' constraints? I hope to learn the approach to implementing such a dual-constraint optimizer from your potential example, so that I may subsequently extend it to as many as 4 simultaneous constraints.

    Thanks in advance, all... I do hope my problem statement is now much clearer.

    Much obliged,

    Dear VBA pundits,

    Say I would like to automate, using Excel/VBA, the task of selecting the optimal set of 100 names (from a database or universe of 1000+) which simultaneously meets multiple constaints.

    Has anyone done something like this or similar using purely Excel/VBA? Can I use either of Excel's built in 'optimizers' - Solver or Goal Seek - in this capacity? Would you mind attaching a sample Excel program which implements something like this for just 2 'dummy' constraints? I hope to learn about the approach to implementing such a multiple-constraints optimizer from your replies, so that I may extend it to as many as 4 simultaneous constraints.

    Thanks in advance.... And I do hope my problem statement was quite clear.

    Much obliged,

    Re: Selection.QueryTable.refresh BackgroundQuery:=False ??

    Hi Will,

    1) The error message I get is as follows:

    {Run-time error '1004':
    General ODBC Error}

    Hope this makes some sense to you; it's pretty much Greek to me.

    2) You hinted: "It sounds like your selection may not contain a QueryTable off the top of my head."

    Assuming this is the problem, Will, how do I go about inserting a QueryTable? There is this one 'Master' database table referenced at other places/by other macros in the current worksheet...

    3) The code: <Selection.QueryTable.refresh BackgroundQuery:=False> ---> Is it just testing to see if the current selection actually contains the QueryTable, OR does it actually execute the query itself?


    Dear VBA gurus,

    Unfortunately, I have encountered a somewhat cryptic error that I don't even understand. The premise is this:

    I 'inherited' a relatively complex multiple-workbooks VBA program. Within one workbook, there is the following subroutine code attached to a 'Refresh' Button located in the 'G7C' worksheet: {I do not know if the following is at all relevant, but the 'ATC' worksheet referred to in the lower half of the code is within the SAME Excel workbook as the 'G7C' worksheet}



    The error occurs (i.e. program execution hangs) at the line underlined above:

    Selection.QueryTable.refresh BackgroundQuery:=False

    Please note that there is an associated database which this multiple-workbooks Excel program references now and then. In view of this, please explain:

    i) What this error means - and what are the most usual suspects/things I should double check as far as (code) troubleshooting is concerned

    ii) Why the 'Selection.QueryTable.refresh BackgroundQuery:=False' method worked for the 'G7C' worksheet (first half of code above), but not for the 'ATC' worksheet (lower half of code)

    Thanks in advance.

    Hopelessly confused,

    Re: Auto-Averaging

    Hi Krishna Kumar,

    Thanks for your code, but my cell now has an error sign [Yellow diamond with a Black '!' mark within] next to it...

    Just in case, would you mind explaining how exactly the INDEX-MATCH combination works to solve my problem?

    Many thanks in advance.

    - Om

    In a cell at the top of a column, say $C1, I need to take the average of a column of occupied cells. Problem is, the number of row entries is not fixed, and may change with each day.

    So, I can't input something straightforward like {=AVERAGE($C4:$C100)} into cell $C1, because the next day $C111 may be the last entry. How do I effect, in cell $C1, something like:

    {=AVERAGE($C4:Last entry in Column C)}?

    Hope my probrlem statement is quite clear.
    Please advise. Thanks.

    To avoid the all-too-frequent user input error, I want to add a drop-down list to all cells in a column (below column header in row 1). I have prepared a simple table/array of acceptable values on a different tab in the same Excel file.

    How do I do this? Thanks in advance... much obliged.

    - Om

    Re: Mapping

    Thanks, Dave!! Silly me didn't get the message first time round. It works with 'True' input as 'range lookup' parameter in the VLOOKUP formula.

    Once again, apologies and thanks.

    Re: Mapping

    Hi Dave,

    I'm afraid VLOOKUP won't do, as it looks for an exact match, doesn't it?

    Also, I absolutely need a function (in-built or coded) to locate between which consecutive AS values [AS_lower, AS_upper] my actual score lies, and then to use the MS value corresponding to AS_lower.

    Just in case, please refer to the first message in this thread for a more detailed problem statement. Thanks.

    - Om

    Dear VBA Gurus,

    I need to accomplish something relatively straightforward (for you guys, I mean).

    Assume I have a list/column of 100 actual scores, say [1.87, 2.05, 1.64, ....]. I need to map this to a 'mapped' score based on the following simple conversion rule:

    If my actual score falls between 2 Actual Scores (AS) per the 'conversion table', the applicable Mapped Score (MS) will be that corresponding to the lower of the 2 AS:

    AS MS
    1.55 1.30
    1.65 1.35
    1.75 1.40
    1.85 1.45
    1.95 1.50
    2.05 1.55
    2.15 1.60
    2.25 1.65
    2.35 1.70
    2.45 1.75
    2.55 1.80
    2.65 1.85
    2.75 1.90
    2.85 1.95

    For instance, my sample actual scores of [1.87, 2.05, 1.64] would map to [1.45, 1.55, 1.30].

    I really hope the above problem presentation is clear, as I need some help to implement this. I tried looking for in-built Excel functions to do this, but admittedly I don't even know half of Excel's in-built functions in the first place. Is there a non-macro way to implement this at all? As far as I know, VLOOKUP or similar Excel functions may not be suitable for my purpose. If a macro is absolutely needed, could someone please indicate a sample code?

    Many thanks in advance...

    Much obliged,

    Dear VBA Gurus,

    Date: 13-Sep-05 {using fn =TODAY(), for instance}

    Bond Coupon Maturity Par Freq AccrInt
    B1 5.00% 15-May-2015 10,000,000.00 S ???
    B2 10.00% 31-Oct-2012 10,000,000.00 Q ???

    In Excel, using in-built functions as far as possible (eg. ACCRINT, etc.), how do I calculate the Accrued Interest figure (as of today, and w.r.t. the last coupon date) for each of these 2 bonds? The last coupon dates, based on each bond's stipulated coupon frequency (Semiannual for B1, Quarterly for B2), would have been 15-May-2005 and 31-Jul-2005, resp.

    I tried using ACCRINT, but couldn't really get logical answers with it. My back-of-the-envelope estimates for the accrued interest, till today (13-Sep-2005), on these 2 bonds are $123,000.00 and $40,000.00 resp.

    Please advise what formulae I should be using...

    Thanks in advance.

    Indebtedly yours,