Calling all excel gurus

[Big Black Stilo]

Hi all

I have a problem with a spread sheet i have that contains data imported from outside of excel- i.e another program.

This data is in a time format [h]:mm:ss as in not the time but as in something took 1hr:26minutes:32 seconds.

However this data is not recognised immediatly by excel and even though the format is correct, it doesnt allocate it a serial number in order for calcualtions to work (e.g a5=sum(a1:a4) doesnt calculate an answer, but a5=a1+a2+a3+a4 will)

There is a way around this, and that is to double click the cell which has the information in, and it allocates a serial code so it can calculate using the first formula above.

The problem is, there are 30 worksheets, each needing 4 columns and 367 rows (total of 44040 cells) which will take a long time to double click each cell to activate the serial code. I need to write a macro that can be assigned to a button that will select for example a1 to d367, and then perform the double click for each cell- and this where im becoming stuck as i can only get it to do it for one cell.

Anyone got any ideas?

 

[The Negotiator]

Do a VBA loop i = 1 to 367 double clicking each time, then for each row each change it manually or do j = 1 to 4 and convert the j value into a ANSII letter for the column reference.

That not help?

 

[smokeyjoe03]

Yeah god bless Visual Basic. Basic algorithm would be:
select cell A1
double click
Activate serial code
down a cell (x367)
repeat for column b
" "c
" "d
End

Then just use the macro for all 30 worksheets.