Posts by Cheeky Charlie

    Re: Sumproduct Function To Pull Data From Raw Data Import

    It would really help if the original input data didn't have so many totals and subtotals dotted through it. Before writing something to work around that - how much control do you have over the inputs?

    I don't like the table at the top and summary at the bottom (but these can be worked around fairly easily) but the per-branch subtotals throughout the data are a pain to work around (it can be done and not too slowly, but developing good design is much better than working around bad design IMHO)

    FYI, there are definitely general imporvements we can make to some of the formulae to make them less complex and implicitly easier to read.

    First ever post, and it's not a terribly happy one :(

    I'm all for robust admin and banning timewasters and ingrates (anybody whose post count equals their thread count would be a good start) but I'm not entirely comfortable with the concept of OzGrid charging people for volunteers' help (not that mine has much value, but others' does).

    I'd be interested to know if this is a permanent move, and if OzGrid is going to refund people who post relevant and worthwhile questions.

    More generally, I'd like to know if I'm the only one who feels this concern?


    Re: Find Common Numbers Between 2 Columns


    There must be a better way... hmmm too... and also - how do you choose those colours?!


    Actually, more seriously, this might not work with 30 columns each splitting out into several columns with text to columns.

    Re: Static Date & Time Corresponding To Cell Change

    Hey, let's not get grumpy with each other. In principle we agree; you are 100% right that offset is not the same. I think you understand that the point of my post was to explain to people who couldn't "decipher" the code how what is effectively an offset is being achieved. (That is why bluecondor could not easily tweak the code to his own spec).

    Anyone that gets this far will hopefully be able to understand the difference between "something" and "effectively achieving something". I thought I covered in my original post on this concept, but to be fair, I had left it a little ambiguous (even with the italics!)

    Ultimately, nobody will be any the worse off for reading your thorough dissection and I wouldn't remove it if it were up to me.

    As for all the examples proving the same point... I guess it makes it inescapably clear! :wink:

    Re: Auto-Extending Graph/Chart Range

    Thanks for following up ManU - this really helps the forum to work best. As a general tip, you can really help yourself for the future by regularly returning to the "logic" of what you're trying to do.
    I'll work through your 'labels' column to explain:

    1. IF(NOT(x=y)) means the same as IF(x<>y) (<> means not equal to). Obviously, the second is easier to read. So we could write:

    2. Using an if with parameters for "if true" and "if false" means logical statements can be inverted providing the "results" are swapped, like this:

    3. Nested ifs can sometimes be "compounded" with AND and OR statements (which work like NOT, i.e. applied 'around' other logical statement(s)). So let's look at the logic; you are testing two criteria:
    "Don't make a label if B shows 0 (i.e. beyond end of data range)" then -
    "If the next row value in B is 0, show the cumulative value (D) for this row, i.e. show the label if it is the end of the data"
    Or, getting nearer to the "boolean" form: show the data if it is not beyond the end of the range but the next value is:

    Which I think is easier to read and understand later on.


    Re: Static Date &amp; Time Corresponding To Cell Change

    General postscript:

    For those reading this thread, the target(y,x) which confused bluecondor temporarily is effectively an abbreviated form of the offset property which can be very easily understood from the VBA help files.

    It has slightly different syntax in that target(y,x) has the target at 1,1 whereas offset starts from target = 0,0. This is probably easier to understand like this:

    Target(4, 5) = Target.Offset(3, 4)
    Target(0, 0) = Target.Offset(-1, -1) '!

    I could understand a justification for using the longer wording, it makes it slightly easier to "read" as you have the offset written explicitly, so helps you understand what the numbers are for. The difference in speed is minimal, but that would depend on what you're doing...


    Re: Copying Specific Columns Into A New Worksheet

    uh, no... oh dear.

    I mean, this part of the code:

    Dim myReplaceWith$ 
        myReplaceWith = "" ' jiuk - edit as needs be

    Defines a constant to replace awkward ASCII characters with; this part of the code:

    vResult = Application.WorksheetFunction.Substitute(vResult, Chr(10), myReplaceWith) 
        vResult = Application.WorksheetFunction.Substitute(vResult, Chr(13), myReplaceWith)

    Uses this value.

    So if you change the line in question to:

    myReplaceWith = " " ' jiuk - edit as needs be

    Then the function will replace characters with space

    myReplaceWith = " Cheeky Charlie is a wonderful man " ' jiuk - edit as needs be

    Would make the code functionally superb in every way.


    Re: Assign Variable To Worksheet Formulas

    Hi Kindlin,

    I feel inclined to wade in here:

    To answer your original question: No, Excel does not have a "k" function that can be applied to cells. As has been alluded to, you can build an equivalent in VBA, though this would require more manual reworking than would make it worthwhile.
    Here is an idea of how that would look:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Empty Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 1 Then Target.Formula = "=" & Target.Value & "*0.9685"
    Application.EnableEvents = True
    End Sub

    This would do what you're asking, put into the worksheet.

    You've been getting "unhelpful" answers because:
    This is hardcoded to apply only to changes in column A - if you have a more complicated range you'd have to hard code all that in too. It is not tremendously robust, and if it crashes, it will leave events disabled... which could be remedied.
    It took a couple of minutes to write, far less time than simply writing the formula =A1*0.9685 in cell B1 and copying down would take.

    There are plenty of extra columns and it isn't bad practice to use them to "stretch" formulae. Also, if you use an extra column for what you're doing, you can see the original numbers at a glance and you can quickly edit them (i.e. select cell, type, instead of select cell, edit within formula, delete old number, type new number).

    I appreciate you're better than most you know; on this forum, sadly, you ain't got nothin'. If it helps soothe, consider yourself above average in the real world, but here... embrace the geek, there are plenty of people here with frightening knowledge.


    Re: Search Range For Part Strings

    I think instr might work - have a look at the help file, the syntax is pretty straightforward and you seem to know what you're doing. Get back to us if that doesn't help.

    PS, sometimes I too find the great DH can OVERUSE the bold and italics to make you feel a bit WORTHLESS - remember he moderates about a thousand moron posts a day (I don't mean to include you with that) and has helped thousands of people via this forum

    Re: Highlight Formula Reference On Another Sheet


    ... I've seen code that Dave (I think) has written that follows off- sheet references. I think the lack of response is that (a) whatever highlighting is done will hose the formatting already applied to the precedents, make it necessary to capture and restore it (including conditional formats), (b) people (including me) don't perceive much value over the existing capability.


    ok...will do...can you please let me know if it is possible to automatically revert the formatting changes made by this macro?

    The thin line between tenacity and taking it too far...

    Re: Extending A Graph Range


    If I understand you correctly, you are trying to get a chart to automatically update itself according to how much information has been entered in the columns storing its data.

    For this I would recommend not a macro, but using dynamic named ranges for your series, as per:

    Which is shown in my attachment. See how adding and deleting from the bottoms of the columns automatically adjusts the graph to fit.

    DNRs are trickier in charts than in pivot tables but basically, the key is to define the series by dynamic named range, not the chart overall source data by the collection of the series. If someone wants to make that intelligible please go ahead, I'm not precious.

    Finally, if you post an example we might be able to help a little better.


    Re: Copy Cells Into Corresponding Workbooks Names


    Maybe posting the form, some sample data, a filled-out form, and an explanation in context would help.

    I'm confident this is the best way of helping us to help you. Your task seems like a series of fairly simple operations, but we don't like to waste time solving the wrong puzzle, if you see what I mean.