Iteration through a Range in Excel

October 28, 2006 at 07:19:54
Specs: XP, Athlon XP 2800+ / 1Gb
Hi folks,

This I thought would be a relatively simple task to undertake, but I've spent the past day trying to get it to work to no avail.

Basically I have a column (A1:A5541) of imported data in timestamp form: hh:mm:ss in Excel. I have changed the cell format so that it's recognised as a time index, however, it appears that for it to be properly recognised in that way I need to go through each cell, click on it, then select the tick box in the formula display and repeat for all 5541 values.

As you'd imagine I thought of trying to speed this up by trying to write a quick VBA script in Excel to do this, however I cannot get it to work.

My code is:

Sub IterateColumn()

Dim rCell As Range

For Each rCell In Range("A1:A5541")

rCell.Select

Next rCell

End Sub


When this is run I get a runtime error 1004. And I'm sure that the 'Select' command if it were to work wouldn't achieve the same effect as clicking on the 'Tick' in the formula window.

Does anyone know how I can simply iterate through each cell in the column, select the 'tick' box automatically and then move on to the next value?

Thanks in advance for your help.


See More: Iteration through a Range in Excel

Report •


#1
October 28, 2006 at 09:13:29
Hi Aj2uk,

I don't know waht you mean by "tick box in the formula display". It's probably because I'm dutch and so is my Excel.

Excel does a lot of guessing about formats when you import, paste or type data.
Remeber: Cell properties don't change the underlying data.

So what's important:
What type of data is your source? Is it text that looks like timestamps or is it some query on an Access database?

Dates and times are in fact numbers. The integer part is the number of day since some date in the past (you can look it up in the Excel settings) and the decimal part is the part of 24 hours. So 0.5 = 12:00:00 and 0.25 = 6:00:00. You can try it in Excel. Type in 0.25 in some cell en change the format to a time-format.
This format is unambiguous so if possible try to get your source in this format.

From your message it looks like Excel isn't able to make the right "guess" when you're importing.


Report •
Related Solutions


Ask Question