Re: I have a problem with displaying the name of all open workbook
You should use code tags, rather than whatever you've used there, for code.
Other than that, if the problem is to do with a wider procedure we're going to need to see the whole thing to see what the problem is.
Edit (Code Tags Added by Mod)
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: Average For Age Per Class
Depending on how you've got your data set up, the best option is probably a pivot table, using the 'average' function in the data area.
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
Workbooks.Open(string)Re: Disable a Macro with Apostrophe
On the Edit toolbar in the VBA there's a 'Comment Block' button (and it's companion, 'Uncomment Block'). I couldn't find it on the menus though.
Re: Specific rounding issue
[f]=IF(A1-INT(A1)<0.01,ROUNDDOWN(A1/0.05,0)*0.05,ROUNDUP(A1/0.05,0)*0.05)[/f]
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.
[f]=DATEVALUE(A1)=B1[/F]
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: Count cells which are greater or less than a specified time
Assuming the pickup time is in A1 and other entries are in B1:B100
[f]=countif(B1:B100,">"&A1+(1/24))+countif(B1:B100,"<"&A1-(1/24))[/f] should give you anything more than one hour out.
Re: Conditional Formatting Grades
If you're in Excel 2007+, your best bet is to just set up one formula for each grade. Otherwise, you may have to use a helper column.
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
application.DefaultFilePath, 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
Thisworkbook.Path
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: Removing code line stops code working?!?!?
The fourth line down is
End Sub, so no, the macros won't work without it. What are you trying to achieve?
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.