Search results

Try adding square brackets like this: SELECT * FROM TransactionFile WHERE Dept IN (SELECT Dept FROM UserDeptFile WHERE [UserCode = ?]) Or if you're using the query design grid then put something like [Enter UserCode] in the Dept Value field of the UserDeptFile table (not TransactionFile). If...

Sure, I hear you on that: they can be easily misused by the unwary.

Hi Jeff, But that's my point: it entirely depends on the workbook setup and how it is used, so I can't make a generalisation. A non-volatile formula might be better in one case, but the same one may be worse in another case. Since Charles' name keeps popping up on this thread, I'll give an...

Five reasons for calculate in the status bar are listed here: http://www.decisionmodels.com/calcsecretsf.htm

Some good, differing opinions on the thread which makes for an interesting read. I think advanced Excel users too frequently make the mistake of dismissing volatile functions without proper consideration (just because they're 'volatile' oh no!) and, as a result, may end up using inferior...

Hi, The original way you wrote it would be interpreted as follows. Let's suppose the file name is HARENDRA27-Jun-13-100638.xlsx. First this line of code: Select Case myFile.Name Is read as Select Case "HARENDRA27-Jun-13-100638.xlsx" This file name is the test expression...

Hi Vijay, The dimensions of Range will cause sum_range to be resized internally within the SUMIF() calculation, so that won't work. :(. It's quite a nasty one because it looks like it should work. For more information please see section 3.2...

Hi Hanim, To do this with SUMIF() you can use a helper column in Sheet2. In Sheet2, in a spare column, put a SUM formula like this (assuming data starts from row 2): =SUM(C2:Z2) and fill down the spare column. Let's say that this spare column is column AA. Then you can reference column AA...

Hi, Shouldn't your Select Case statement be like this? Debug.Print myFile.Name Select Case Left(myFile.Name, 3) Case "HAR" email = "HChaurasia@yamaha-motor-india.com" Case "KAP" email = "RKapoor@yamaha-motor-india.com" Case "NIK" email =...

Luke's given you a great tip there: use a String variable to build up the formula string. This makes your code much easier to debug because you can use the VBA IDE's tools to interrogate the string variable (to see the string itself, how long it is etc) to determine what the problem is with it.

The other conclusion to make is that SirJB is much faster at typing than I am. :)

The RIGHT() worksheet function is a function which has been included in Excel so people can work with strings. As such, it has been defined to return a string, so the numbers you have in D1:D3 are actually considered by Excel to be string types rather than number types. There's a small giveaway...

Congratulations SirJB! Looking forward to your next 5000. :)

Okay, thanks for clarifying Narayan. No harm done if it's a one-off.

Hi Deb, I don't understand your reply. I'm not asking how long I can edit my post for: I'm saying that I am unhappy that someone has injected words into my post and it looks like I wrote them.

Ninjas, someone edited my post and added the text in bold "COUNTIFS is available only in Excel 2007 and later versions". I'm not particularly happy about it because it looks like it was written by me. If you add something to someone's post then you should make it clear that it was written by you...

For completeness, here's the COUNTIFS() equivalent (untested) =COUNTIFS(Sheet1!$E$2:$E$12,$B3,Sheet1!$C$2:$C$12,$E$2,Sheet1!F$2:F$12,"<>") ------------ Edit by Ninja: COUNTIFS is available only in Excel 2007 and later versions

linha() is a dynamic array in your code, so you need to ReDim it. If I include your variable declarations for you: Sub foo() Dim linha() As Long Dim nAtivos As Long Dim i As Long Dim ret As Worksheet Set ret = Sheets("Retornos") nAtivos = 6...

What does "didn't work out" mean? Assuming ret is the worksheet's codename, shouldn't that be this? For i = 2 To nAtivos + 1 linha(i) = ret.Cells(253, i).End(xlUp).Row Next Debug.Print Application.Min(linha) If that doesn't work then please give full details (eg. wrong value, error message)...

Could it be because of crystal reports? This is the first hit on google when I search for that error: http://support.microsoft.com/kb/269383

Hi and welcome to the board. I'm guessing because I can't see your worksheet, but perhaps you actually want to Sum the data in column F rather than Count it? If you're using Excel 2007 or later you can do that with the SUMIFS() function.

Hi Peter, Before you upgrade, have a very careful look at which Office 2013 SKU you need. For example, if you're a powerpivot user, check which SKU you need in order to keep using powerpivot and make a judgement call against whether or not the price is acceptable for you.

Thanks, SirJB. Shame I didn't come up with it... I picked it up from the MrExcel board (I think) many moons ago.

This is one of my favourite formulas, in I4: =MID(H4&" "&H4,SEARCH(" ",H4)+1,LEN(H4)-1) In I5 =MID(H5&", "&H5,SEARCH(" ",H5)+1,LEN(H5)+2)

The 255 was inherited, but agreed. :) Yes, 'semi-volatile' means a one-off volatile recalc on open.