Search results

Hi a_dani, For the original version that calculates the entire selection, this change should work: If SUM <> 0 Then AVERAGE = Round(Application.WorksheetFunction.Subtotal(1, Selection),8) Else AVERAGE = 0 Similarly, for the second version that only considers visible cells, try this...

I'm not sure where the page is here, but I think he's referring to Ron de Bruin's tips which can be found at http://www.rondebruin.nl/tips.htm Also of interest: http://chandoo.org/wp/2009/02/04/satisfaction-surveys-excel/...

Hi Giles, I'm back with the OLE DB instructions. Yes indeed, you can do if/then constructs. It should be more full-featured in general. OLE DB uses an underlying "provider" (database engine) to connect to your data file (in this case an Excel file). The two underlying database engines from...

The reason by-the-way for deleting rows from the bottom up is that when you delete a row, your data has all moved up a row, and your loop will end up skipping/not checking some rows of data, and then checking a bunch of blank rows at the bottom of your table, if you use a regular FOR loop or FOR...

I retract my comment. It would work but for one little problem... number formats (which allow you to specify text) only apply to cells with values in them. You cannot "format" a blank cell. You could use conditional formatting to highlight the cell (or even the row) red (or apply other...

Couldn't a conditional formatting rule on column B that uses a format with the message "OVERDUE" in it under that condition, and that otherwise uses the normal date format work?

Felix, if you start a new topic you will better help: http://chandoo.org/forums/forum/ask-excel-questions You may need to use an array formula.

Hi ahhhmed, I see you have a couple of other similar questions - http://chandoo.org/forums/topic/grouping-items http://chandoo.org/forums/topic/conditions Are they related? Do you still have the problem? If you still need help, it would help me to understand your requirement better...

If Excel doesn't recognize your list as one contiguous list due to the blank rows, just highlight your whole list before initiating the auto-filter. Asa

Hi ahhhmed, You should be able to auto-filter your list (cursor in list then DATA/Filter in ribbon, or Ctrl-Shift-L, or Alt-D,F,F), then filter for blanks, then highlight and delete rows, then unfilter. Asa

Giles, re. Pastebin -- nice!! Syntax highlighting and all. Sounds like the way to go :) I will post back here when I have the instruction on OLEDB. All the best, Asa

Glad to offer my little contribution, OleMiss! One more thing.. Depending on how concerned you are about making the routine bullet-proof, you could have the error handler make sure that the specific error raised is the one you expect. If the sheet couldn't be renamed due to worksheet...

If the first sheet other than "Summary" even if it wasn't renamed, then move If Not SelectionChanged Then ws.Select SelectionChanged = True End If before If ws.Range("C5") <> "<>" And ws.Range("C5") <> "" Then

OK, I see. The behavior is rather odd, though. Assuming a workbook with the first sheet named Summary and the others potentially being renamed: If "Summary" is the ActiveSheet when you run the macro, the next sheet that has a name in C5 will be selected and renamed. Then the remaining...

I realized that your problem with the subtraction was probably getting a blank column, with no values, right? If so that is because you can't do math with blank values, and I assume only the credit or the debit column has a value in it for each row. The solution would usually involve using one...

I'm glad you got a working SQL statement. Hmm, I was able to use arithmetic adding/subtracting with no problems here. With your current SQL statement, can you verify that the moneyin/moneyout is showing up as numeric and not as text in the resting QueryTable? Also, what kind of error did you...

Excel uses your Windows/Mac regional settings to determine how to interpret dates when they are entered/imported/converted. The default regional settings in regards to two digit years have a 21st century cutoff of 2029. Two digit years 00-29 become 2000-2029, 30-99 become 1930-1999.

Luke, what's the reason for executing the Select method on ws? Or checking ActiveSheet.Name in this loop -- I wouldn't expect the active sheet to change unless you .Activate another sheet.

As to your question about Hui's excelhero example, I think he was just providing a short and sweet instruction. I'm better at the long explanations, and the concise one is a good complement/supplement to my long commentary The example from excelhero is appropriate for concatenating/merging...

Hi :) Try changing ~Date~ AS "Date", in the third select statement to just ~Date~, (backticks instead of tildes of course) You may have to do this same thing with "Amount" there. If this works then it would be my fault for suggesting the AS clause (also called an alias) when the...

Hi Giles, I just have a minute now, so I'll give you some quick pointers for the moment. "Too few parameters" it thinks your query has something called parameters. Suffice it to say this query shouldn't have parameters and what it actually means is that 4 of your field (column) names don't...

Thanks Hui. The complication I brought into it was the alias names for the fields (well, and my general verbosity!) and the unfortunate problem that MS query modifies your SQL after you paste it in. With a union query it insisted on removing all the aliases, which totally botched it...

Hi :) Remove UNION ALL from the end of the query. Those words should only go between each SELECT...FROM ("select statement") and are actually the glue that pulls several queries (each select statement) together. It's possible that you should remove those single quotes I placed inside the...

I don't understand your statement #3. What does "reply bock" mean? In #2 when you say "this is with the SQL line", where you have tildes there, were they actually backticks? Can you upload you exact SQL statement to shortText.com and provide a link so I can look at it? Also, what version...

Hi moneymonitors! I'm new to some of the stuff I recommended, as I have just started using Excel again for almost the first time in over 10 years, so if I miss something in my advice hopefully others will comment on it. But I have been experimenting with the features I talked about and have...