Update variabel to be = string, specified in a cell?(Splitting a cell and distribute)

[JKB]

Hi everybody!

I have a column, which i would like to split up and distribute into other cells:



SYMBOL
DATE
TIME


PRICE


IBM
20010102
09:30:07


84.5


IBM
20010102
09:30:08


84.5


IBM
20010102
09:30:08


84.5


IBM
20010102
09:30:08


84.5


IBM
20010102
09:30:08


84.5


IBM
20010102
09:30:08


84.5


IBM
20010102
09:30:08


84.5


IBM
20010102
09:30:08


84.5


IBM
20010102
09:30:08


84.5



What i want to do is the following: I want to define the Time cells as a string in a variable sCell (im doing this in a until loop).
Doing this will allow me to split it up and distribute minutes one column to the right, and seconds two columns to the right (i hope)

To do this i have written the following:

Dim sCell As String
sht.Range("C2").Select


Do Until ActiveCell = ""
sCell = ActiveCell

ActiveCell.Offset(1, 0).Select

Loop


but when i look in the locals tab it says that sCell has a digit value, fx 0,3923424234......., does anybody know how to fix this so that
for each loop i make sCell will be equal to "09:30:07" for 1st loop, "09:30:08" for the next loop etc?
- Of course, if you know a easier way to split up each cell in column c and distribute, i would like to know how!

Hope somebody can help!

[Bob Phillips]

Nothing to fix, time is just a formatted value in Excel


Dim lastrow As Long
Dim i As Long

With sht

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow

.Cells(i, "G").Value = Minute(.Cells(i, "C").Value)
.Cells(i, "H").Value = Second(.Cells(i, "C").Value)
Next i
End With

[JKB]

Nice!!!! It works! Thx for the help! Much easier than defining each cell as a string i guess :)