Substitute for Excel 4 Macro

[Deutz]

Using Excel 2003.

To determine a cells data type format I have defined a worksheet name as 'CellFormat' with 'Refers to:' =GET.CELL(7,INDIRECT("rc[-1]",FALSE))

Problem is that now I get a second enable macro message when the workbook opens (Workbook contains Excel 4 macros ... disable/enable).

Is there a more current formula that will not trigger the macro warning message but still achieves the same thing?

I did try a UDF with Cell.NumberFormat which returned a slightly different format with days and months of dates switched around.

I also had a question about date formats. Wondering why the same date in two different cells can be either in General format (left aligned) or in Date format (right aligned) and is still displayed as a date? When the date is in General format my formula to test if it is the last day of the month and in a year range does not work.

Thanks in advance

 

[GlennUK]

So, you have a UDF for this, but it's not doing what you want? Can you be more specific? What are you trying to achieve exactly? For what reason?

If you give an idea of your overall aim, people may be able to give solutions that you hadn't thought of.

As for your other question, what formula are you using to test for last day of the month? Any normal formula will not rely on cell format ... unless you have text in cells unexpectedly ( which left alignment usually indicates ).

 

[Deutz]

Hi <?xml:namespace prefix = st1 ns = "urn:schemas:contacts" /><st1:GivenName w:st="on">Glenn</st1:GivenName>,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for your reply. I will explain a little more about what I'm trying to do. We get spreadsheets of client data from our data analyst that I have to check for accuracy. I need a formula to check that the dates in a column are the last day of the month and between a start year and an end year and flag those that are not and check that the dates in another column are the first day of the month and between a start year and an end year and flag those that are not.<o></o>
<o></o>
Sample data:<o></o>
<o></o>
ColumnEndDates<o></o>
31/12/2007<o></o>
30/09/2009<o></o>
31/1/2009<o></o>
<o></o>
ColumnStartDates<o></o>
1/11/2007<o></o>
1/09/2010<o></o>
1/1/2009<o></o>
<o></o>
The problem is that I can't be sure that the dates will always be in Date format. They could be in General format or Text format and my formula doesn't work with every format. So I was trying to come up with a formula that will work in every instance or determine the cell format before applying a formula so I could determine which method to use to flag the duds.<o></o>
<o></o>
This is my formula to test the end of month dates:<o></o>
<o> </o>
strFormula = "=IF(ISERROR(RC" & SearchCol & "),""Dud"",IF(TRIM(RC" & SearchCol & ")="""",""OK"",IF(ISERROR(YEAR(RC" & SearchCol & ")), ""Dud"", IF(NOT(DAY(RC" & SearchCol & ")= DAY(EOMONTH(RC" & SearchCol & ",0))),""Dud"", IF(OR(YEAR(RC" & SearchCol & ")<" & StartYear & ",YEAR(RC" & SearchCol & ")>" & EndYear & "),""Dud"",""OK"")))))"<o></o>
<o> </o>
<o> </o>
Thanks<o></o>
<st1:GivenName w:st="on">Michael</st1:GivenName><o></o>

 

[GlennUK]

According to my testing that formula works no matter what the cell format of the cell being tested. What happens when you try to use it?

 

[xenou]

I also had a question about date formats. Wondering why the same date in two different cells can be either in General format (left aligned) or in Date format (right aligned) and is still displayed as a date? When the date is in General format my formula to test if it is the last day of the month and in a year range does not work.

You sound like your getting UK dates in a workbook that is set for a US Region either in Windows or Office (or both). Usually if Excel doesn't recognize the date it leaves it as text - and UK dates aren't being recognized.

 

[Deutz]

You're right Glenn, the formula does work with TEXT and Date format (for some reason I thought it only worked with Date format). The problem is that it doesn't do exactly what I want when in General format. If in General format the date is converted into a number which the formula still flags as ok which is what you would expect but for my purpose the date must appear in the cell visually as a date so I want to flag it if it doesn't.

Xenou, I am not sure about regions and dates as our Network blocks us from accessing those options.

Regards

Michael

 

[GlennUK]

You originally said:

To determine a cells data type format I have defined a worksheet name as 'CellFormat' with 'Refers to:' =GET.CELL(7,INDIRECT("rc[-1]",FALSE))

... why don't you write a VBA version of that then?