Search results

Ok might be slightly off topic but I asked this ages ago and got nowhere - how can you create a custom VBA AGGREGATE function i.e. that takes records as a parameter and can be used in a query in the same way aggregate functions can be. I know I can create a function that uses a recordset object...

Cheers for your help guys yeh it is because Excel ignores non-numerics- can't believe I didn't think of that! I'll write a function to sort it. cheers

Hello everyone, After trawling through data and queries for days I have finally stumbled upon the reason figures weren't matching which is because when the Max function is given the values ("3", "L") it returns "L" in MS Access and yet in MS Excel the same Max function returns "3". Firstly -...

Ahh, to be honest I'm not sure if you can do that in Excel VBA. I know in 'proper' programming languages such as Java and C# you can set up programs to run as threads that could possibly get around this - but I haven't a clue how to do this in Excel VBA or even if it's possible - i would suspect...

Hello Musthu, there were a couple of things wrong with that spreadsheet. First the years in column A were stored as dates and not numbers (so they wouldn't match to the year function). The second thing is that they weren't 'Array formulas' - to make them array formulas ensure you enter the...

There's no need to use coding for this mate if you're not used to it. Instead you can use what's called an array formula to do a sort of AverageIF function. Assuming your figures are in cells A1:A9 and the dates are in cells B1:B9, write 2006 in cell C15 and then in D15 write the following...

Hi Arie, If you go to the Visual Basic editor (by pressing Alt and F11) and insert a module by going to the Insert menu and clicking module. Now in the white space on the right type the following: Sub turnOffMenuBar() Application.CommandBars("Worksheet menu bar").Enabled = False End Sub...

Something along the lines of: Sub waitCopyAndPaste() Const StartRow As Long = 1 Const EndRow As Long = 1000 Dim x As Long For x = StartRow To EndRow Rows(x & ":" & x).Select Selection.Copy Application.Wait (Now + TimeValue("0:05:00")) ' Wait 5 minutes Selection.PasteSpecial...

Hello chergh, thanks a lot for that. To be honest I was hoping to be able to do it by just pulling in the other data series (since we are going to have to generaete a lot of these charts) but this is helpful nonetheless. Thanks very much for your help

Hi Arie, Just right click on any toolbar and uncheck the ones you don't want displayed. To do it programmatically (and for main menu bar) use: Application.CommandBars("Worksheet Menu Bar").Enabled = false and for example for standard toolbar use Application.commandBars("Standard").enabled=...

Thanks shades but I tried that and still no joy. Anyone any ideas? Many thanks for any help.

Does this work? =COUNTIF(E2:BB2,">"&(PERCENTILE(E2:BB9,0.9)))

Hello all, I've got some school data in Excel for 5 subjects and we can create a bar chart of this data no problem. The problem is we need to also display (as a line or points) Lancashire data on top of the bars. I've added another data series but cannot get the points to tie up with the...

I've sorted this now, I can't believe I've spent hours looking at this but the prob was that I've got a copy of this database on a different server and I was referring to the copy in my ADODB connection - so the cahnges I was making to field lengths weren't being applied to the database I was...

Hello all, I've written code using the examples given at http://msdn.microsoft.com/en-us/library/aa155434(office.10).aspx to import data into Access from Word via word forms. I've generated a template with data and when I tried dummy data this imported fine, but the templates have now been...

Hello, I've sort of found a solution to this problem by using this below: SELECT tblLSIP_PLASC_Data.Type, tblLSIP_PLASC_Data.Level, tblLSIP_PLASC_Data.Level_Name, tblPLASCGroupReq.PLASCGroup, Sum(IIf([Group]=[PLASCGroup],[2003/04],0)) AS [Y-4], Sum(IIf([Group]=[PLASCGroup],[2004/05],0)) AS...

Ok, this is slightly different but the principle is the same: SELECT tblLSIP_PLASC_Data.Type, tblLSIP_PLASC_Data.Level, tblLSIP_PLASC_Data.Level_Name, tblPLASCGroupReq.PLASCGroup, tblLSIP_PLASC_Data.[2003/04], tblLSIP_PLASC_Data.[2004/05], tblLSIP_PLASC_Data.[2005/06]...

Hello all, We have had this ongoing problem that I'm sure must be fairly simple if we know how. Basically we have a lot of data in different groups that for example we need to display for specific years e.g. the last 5 years. The trouble is, as the data is calculated, there may not be data...

Thanks I think I've sorted it now - I simply just started again and inserted a sub form and access did the rest for me. I think the prob before was that I wasn't pulling in the EmployeeID to link on. Cheers

Hello, I'm trying to develop a staff/employee database and have run into a problem: I have 3 main tables tblEmployee (table of all employee basic details) tblPost (table of all posts in the department) tblEmployeeInPost (main table of employees and their assigned posts) The prob is I need a...