Posts by Pesky Weasel

    Re: Negative number return in a formula

    Not really I'm afraid.

    This is what your formula does:

    The formula compares numbers to a base value ($I$5) with a tolerance of +/- 0.5%.
    If the value lies within that margin the formula returns 1, otherwise it returns ""

    Your sum formula then counts the number of values that meet the above conditions.

    Did you need it to do something else?


    Re: Button to increment cell number


    Place a button on the worksheet - makesure it is from the Controls toolbox not the Forms collection.

    Double click the button to activate the code pane, in between the buttons click event (this should appear by default) place the following:

    Sheets("sheet2").Range("a6").Value = Sheets("sheet2").Range("a6").Value + 1

    The end result looks like:

    Private Sub CommandButton1_Click()
    Sheets("sheet2").Range("a6").Value = Sheets("sheet2").Range("a6").Value + 1
    End Sub


    Re: Displaying Sheet Names within one sheet


    There is no formula that refernces a sheet name. If you really want one paste this code into a standard module:

    Private Function SheetName(Sheet_Index As Integer)
    SheetName = Sheets(Sheet_Index).Name
    End Function

    Use it like this:


    where 1 is the order of the sheet in the workbook. Be warned if you change the order of the sheets in the book the column title will change but the references will still point to the original sheet.


    Re: Date range inability


    There are two ways to approach this but your information was a little vague.

    Method 1:
    In this example I have assumed that the first period spans 17 Oct 2003 to 15 April 2004 - any date within these parameters returns 15 April 2004.
    All other dates outside of this regardless of the year are retuned as 15 April 2004 +180 days (12 October 2004)


    Method 2:
    This uses a nested if to restrict the +180 to a definite date range, you will have to supply the final argument of what the formula should return if neither criteria is met.

    =IF(AND(L4>=DATEVALUE("17/10/03"),L4<=DATEVALUE("04/15/04")),DATEVALUE("04/15/04"),IF(AND(L4>=DATEVALUE("04/16/04"),L4<=DATEVALUE("10/16/04")),DATEVALUE("04/15/04")+180,"Invalid Date")


    Re: Purdy Spreadsheet examples?


    Welcome in from the cold.

    Making Excel look pretty is just a matter of playing around with the formatting tools however see here for some tips on good spreadsheet design.

    I cannot emphasise enough that a good spreadsheet has its foundations in a clear and logical data layout, not how many fonts, colours and shades you can squeeze onto one page. Have a look at the AutoFormat under the Format menu - but unless your data is laid out in database table style this will unpredictable results.

    Hope That Helps

    edit: I changed the name of the post to more accurately reflect the question. This assists with referencing the question with similar posts and the search function. Pleasae see the sticky note - http:// for further details.

    Re: make a formula change another cells value

    No - a formula cant influence any cell other than the one it is entered into. Having said that, there is always a workaround but the success of this will depend on how intelligent you want the workaround to be.

    Will the positive integer always be 1 digit?
    Will it always be at the end of the text?


    Re: Autofill two rows using r1c1 notation.


    It is a little hard to suggest a more efficient way of performing the task without seeing the rest of the Sub, could you possible post the complete code (so long as it isnt 100s of lines :) )


    Re: Evaluating numbers less than x


    see commented code below:


    Re: Evaluating numbers less than x


    There were a few logical errors in your code. Please see sample below and compare the differences, post back if you don't understand why it now works (hopefully :) ).

    I presume that the cell J1 contains a count of the number of records.


    Re: Setting Location of Programmatically added Controls by Cell


    Welcome to Ozgrid - Glad you like it!

    The example below uses the active cells left, top, width & height properties to add a command button to each cell in a selection of cells. To test highlight a range of cells and then run the macro.

    Hope that helps

    Re: Speeding up VBA Coding



    1) Your table contains only the data you wish to effect
    2) There are no gaps in the data
    3) Row one had the column headers

    The following should do as you wish -


    Re: assign value to list from VBA


    You will have to provide a little more information here:

    Does the cell contain a validation list? or
    Do you want to add a value to the cell at the end of a list?

    How do you propose to get the value that will be added:
    User input?

    When is the value to be added:
    Button click?
    Conditions met?


    Re: formulas don't resolve


    Welcome to OZGrid!

    Could the formatting of cells be changed to text?
    Try changing the format of the cell to General and then select the cell in Edit Mode (ie make the cursor flash in the formula bar), then hit enter.


    Re: Select In Select

    Thanks Will,

    I will try that out asap. My introduction to SQL so far has not covered the CASE keyword. Thats 6 keywords now - 994 to go :)