Recent content by glenntb

I'm not certain, but I think this will do what you're looking for: SELECT [field1], [field2], [field3] FROM tblABC WHERE ( conditionA AND something = something ) OR ( conditionB AND something <> something ) Glenn

It appears that you've got two ELSE conditions tied to the first CASE statement. I copied your code into a text editor, and played with the line breaks to line up all the CASE WHEN and ELSE statements. Here's a pseudo-code picture of what your code looks like when you do this: 01 ORDER BY...

First, a disclaimer that I'm a little new to SQL and still learning... but I think this might work for you: SELECT Group_Name, ScheduledYear, CASE WHEN SUM(CASE WHEN Q1 = 'Null' THEN 0 ELSE 1 END) > 0 THEN 'Audit Info' ELSE 'Null' END AS Q1, CASE WHEN SUM(CASE WHEN Q2 = 'Null'...

Instead of using "thisworkbook" use "activeworkbook" -Glenn

Does the problematic text string contain a delimeter within it? (e.g. is there a comma in the string: "Doe, Jane") Glenn

If you want to highlight the pairs of duplicates, you'll need to modify the above code to something along these lines: Sub Highlight_Duplicates(Values As Range) Static blnColor As Boolean ' ' Make sure there's no highlighting already in our range: Values.Interior.ColorIndex =...

I'm with Skip... Deleting rows containing merged cells will lead to the loss of any other data on that row. It would be safer to unmerge the merged cells, then delete the empty rows, leaving the un-empty rows in your data set for processing. If you are absolutely certain that this will never...

Will this work for you? Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer For Each mycell In Array([K1], [E2], [H2], [K2]) Select Case mycell.Value Case "" icolor = 16 Case Is < 0.505 icolor =...

PHV: Nice! It actually lends itself to a non-VBA answer as well Year Formula Result 2010 =DATE(D3,5,24)-WEEKDAY(DATE(D3,5,24))+1 5/23/2010 2011 =DATE(D4,5,24)-WEEKDAY(DATE(D4,5,24))+1 5/22/2011 2012 =DATE(D3,5,24)-WEEKDAY(DATE(D3,5,24))+1 5/20/2012

This should work: Function VictoriaDay(iYear As Integer) As Date ' ' Calculates the first Monday before 5/25 of the provided year Dim dtTest As Date dtTest = CDate("5/24/" & iYear) While Weekday(dtTest) <> 2 ' note: 2 = Monday dtTest = dtTest - 1 Wend...

This looks like it'll do what I'm looking for, but I get an error on the first line. I also had to rewrite the loop so that Excel VBA was happy: Set rst = ActiveSheet.PivotTables(1).PivotCache.Recordset Do Until rst.EOF For Each fld In rst.fields MsgBox fld.Value Next rst.movenext...

Thanks Skip... I've been playing with them all day and can't seem to make anything work. Has anyone ever successfully used the methods and properties on a PivotCache Object that might be able to give me some pointers? The help files are proving to be something less than helpful. Glenn

Greetings! I have an Excel Pivot Table pulling data from an Access db. The source data is >65K lines (it's actually close to 200K). The original Access data is no longer available, but the Pivot table still works because the original data is stored somewhere in Excel (in a Pivot cache...

Our pastor frequently changes the message board in front of our church. One Sunday, he posted "Every Believer Has A Message". As I was pulling into the parking lot, I only glanced at it and could have sworn it said "Every Beaver Has A Message". Of course, I had to mention that to him, and he...

Thanks Michael - I am not having a problem with autonumber, though. My unique key identifier already exists in the file extracts that I import fresh each day to ensure that I have the latest data. The issue that I am having is that the table that I am loading the data into, forgets that the...