Posts by LocalPeople

    Re: Get % between two amounts

    I'm not completely clear what you mean. You can calculate 25, 30 and 35% of a value using [f]=[value]*0.35[/f] etc
    If you want to specify upper and lower bounds, you need to specify what happens when the calculated value falls outside those bounds.
    For example, if you want the 35% figure to be between 2501 and 4000, even when the value is 100 (and 35% is therefore 35), you would use [f]=if([value]*0.35>4000,4000,max(2501,[value]*0.35))[/f]

    Re: Seeking help with macro to find min and max of a lot of data

    Because his data is structured badly for formulas. But perfectly for pivot tables:
    -Select data
    -Insert>Pivot Table
    -Put 'Distance on Date' in the row field
    -Put 'Distance' in the data field, and set to 'Maximum'
    -Put 'Distance' in the data field a second time, and set to 'Minimum'.

    Re: Open a file based VBA input name

    It would help to see the code you already had, but the basic bit you need is

    Workbooks.Open("C:\blah\"&<the variable, cell reference etc that you've changed>&"\ZZZZ.xlsx")

    If you're passing the whole string, you can just use


    Re: Specific rounding issue

    There may be a more elegant way to do it, and it still rounds up 5.109 to 5.15, but it's not clear that that isn't what you wanted.

    Re: Adding Time to dates

    As far as I can tell, the problem is that, because you're keeping dates, times and day of the week in different columns, you aren't adding the last set of hours into each days calculation; it's a fairly easy fix, but my recomendation is to keep the 'finish date/time/day' all in one cell, formatted as 'dddd dd/mm/yyyy hh:mm'; that way it's less likely to cause a similar problem in future.

    Re: Getting a date to not change format after import

    A date that Excel has recognised as a date will have been converted to the date serial number, and then formated as a date. A date constructed and copied as text will not have been 'recognised' as a date, so you're asking excel if the text '01/01/1901' is equal to the number '1', which it obviously isn't.
    There are two ways around this - force the date to be a text value, or force the text to be a date (which is slightly more robust). Assuming text in A1 and date in B1.

    Re: Adding Time to dates

    Provided all the dates are real dates, and the times are real times which don't include a date component, they should add up fine; if they aren't that suggests that one of those isn't true. Assuming that isn't enough for you to figure it out on your own, we'll need a worksheet to see where the error is.

    Re: Rota

    You've got the cell format set to text.
    This has probably happened because when you put 8-8 into Excel, it will interpet it as the eighth of August (08/08/2012). You need to:
    1) replace "8-8" with "8 to 8", or better yet "8 to 20" (or even better, list start time and end time in seperate cells, as "8 am" and "8 pm" or just "8" and "20").
    2) set the format of all cells back to 'general'

    Re: Rota

    Are 8-8 and 8-2 the only possible shifts? If so, you could try [f]=countif(B2:H2,"8-8")*12+countif(B2:H2,"8-2")*8[/f]
    It would be better if you used an imput format that Excel doesn't try to interpret as a date...

    Re: Removing code line stops code working?!?!?

    Alright, assuming everyone is on a version of Windows makes things a little easier.
    You're using a template; I've not used templates very often, so I may be making some incorrect assumptions here.
    Are they opening the template itself, or creating a new workbook based on the template? If it's a new workbook, it doesn't have a path yet, because it isn't actually in any location.
    It looks like even the default 'send as PDF option' by default saves the PDF, albeit in the temporary location where it will get cleaned up later.
    Your best bet is probably to use


    , which should give you a path which is valid and logical on whatever setup it's run on, and then kill the file after it's been emailed (though it might be worth checking if users will want a copy of the file for their records).

    Re: Removing code line stops code working?!?!?

    There are a couple of ways to do this, the simplest being getting the path that the workbook itself is saved in


    The real variable is the OS; I've no idea how you'd go about doing something like this to work with a Mac...
    If this is intended to go out as a 'black box', you probably want to include some error handling, even is it's a simple statement that stops the program with an 'oops, something went wrong, please call me to sort it out' type message.

    Re: Removing code line stops code working?!?!?

    So what you want to achieve is a PDF version of your Excel sheet which is emailed out without being saved anywhere on your computer?
    I don't think that's possible; the process of creating the PDF is bound up with the process of saving it. Your best bet is probably to create the PDF, email it out, then delete it.
    Of course, saving a PDF doesn't actually have any effect on the Excel sheet it was created from. I would also avoid quitting the application - they may be finished with your form, but you don't know what else they've got open, and quitting Excel will close everything else as well.

    Re: Removing code line stops code working?!?!?

    Well, that's a call the one of RDB's pdf creater routines (which I've used myself occasionally). Is the code stopping inside RDB_Create_PDF, or is the call itself failing? If you step through the code, what line does the code error out on?

    Re: Loop/Range

    You're almost certainly not going to find anyone willing to post you homework answers for you.
    That said, some help with the logic is fine:
    A 'sub' does something; it's the 'default' type of procedue, and probably what you want. A function returns something, either into another sub or function, or into a formula in a cell; it may do other things as well, provided it isn't being used in a cell.
    On top of that, your logic isn't clear. The range is just numbers? So there will be an infinate count of numbers both above and below the bounds, unless you specify some other, outer set of bounds.