Macro to make all numbers in 2 columns of 40 sheets the same format?

[synses]

Hi,

I am using this formula to compile data from 40 sheets onto one using the district name and school name as matching criteria.

(array formula)

for forum 5.xlsx

School names are typed differently in many pages.
Compiling 40 sheets into one page took all day and I have 40 more to compile, looking at lots of correcting errors due to "no match" when there is a match that is slightly differently spelled or carries an ' or not.

It would SEEM to be easier to match district number and school number BUT they are formatted differently - some entered as text, some as Custom, some as Special, some with an apostrophe (all due to needing to have leading zeroes) that I can't remove and make it stay removed, etc.

Is there a macro that would make all the numbers in Elem FCAT Reading columns A and B match the formatting on the other sheets of the numbers in columns B and D ...? (A should match D; C should match E)

I am using Excel 2010 and can't get the "Custom" to stick on the sheet I create (Elem FCAT Reading), I enter it but it changes to Special....

AND I can't get the apostrophe to disappear from the other sheets - must be programmed in - when I delete them they reappear!

So there are major formatting inconsistencies....issues that I am thinking a macro across all sheets MIGHT fix so I can edit the formula to match District Number and School Number and have less corrections of "no match" errors...?

Thanks for your help,
Synthia

[tehneXus]

Hi,

the following code will loop all sheets in your workbook and change the formatting of column 2 to "00" and column 4 to "0000" (rows 13 till end) and converting text to number values (at least in the example), skipping the sheet with name "Elem FCAT Reading":

Please Login or Register  to view this content.

[synses]

THANKS so much!