I am trying to record a simple macro to put a colon in front of the
character second from the right in a cell. Here's my issue:
I go to a cell, (let's say it has the number 1030 in it)
I start the macro recorder, give is a shortcut key (say, "W")
I click on the relative reference icon
I press F2 to edit the cell
I use the left arrow to move two positions to the left
I type in a colon (":")
I press return
Then I click on the end recording button
Now, what I want to happen is the when I go to the next cell and press
CTRIL-W is to have a colon inserted in front of the second character
from the right. So if this cell has the number 1045 in it I expect it
to end up as 10:45. What happens is it overwrites that cell with the
value from the previous cell with the colon in it. Instead of 10:45 I
get 10:30 again, and again, and again......sigh. Looking at the maco
in the VB editor, sure enough, theres the text "10:35". I don't want
the text. I want only the commands to enter edit mode in the current
cell and move two places to the left and insert a colon. In the
preVBA days this was SO easy: {edit}{left}{left}":"{down} and you were
done. That's all I want!
Thanks!

Macro recorder isn't the bect solution for this, try something like:

Option Explicit

Sub colon()
Dim rng As Range
Dim c As Range
Dim dl As String

Set rng = Range("a1:a100")
Cells.NumberFormat = "@"
For Each c In rng
dl = Len(c)
c.Value = Left(c, 2) & ":" & Mid(c, 3, dl)
Next
End Sub

you can set any range you want, numberformat is used to force format
to text

regards

Piotr

Here's my idea:

Private Sub CommandButton1_Click()
Dim S As String
Dim L As Integer
S = ActiveCell.Value
L = Len(S)
Dim NewS As String
NewS = "=" & Chr(34) & Left(S, L - 2) & ":" & Right(S, 2) &
Chr(34)
ActiveCell.Value = NewS
End Sub

There are some important assumptions in here, though. If you've got a
number like 1030, if you slap a colon in the middle and make 10:30
then you have changed the value of that cell. It's now 10:30am Janary
0th year 00. This equates to a numerical value of 0.447916667

Do you really want a macro that changes 1030 to 0.447916667 ?

If you have a cell with the contents "1030" which is NOT a number but
a string, then you can slap a colon in the middle and it will be a
different string which looks the way you want.

My sub, above, will take the number in your cell, turn it into a
string, then put a colon in the 2nd from the right. So 1000 becomes
"10:00" and 100000 becomes "100:00" and 123456789 becomes "1234567:89"

Maybe instead of a macro you can get away with applying a number
format like 0":"00 which will NOT change the contents of your cell,
but will affect the APPEARANCE of your data. In this way, 1000 looks
like 10:00 but still has the value of 1000.

HTH

Brian Herbert Withun

Assuming you want to place the colon after the second character (Everytime)
you can try this:
In my example I am using column K, as you can tell by the cells reference
number 11
Modify to suit your needs.

Dim i As Integer, iLastRow As Integer

iLastRow = Range("K65536").End(xlUp).Row

For i = 1 To iLastRow
Cells(i, 11).Activate
With ActiveCell
..NumberFormat = "@"
..Value = Mid(ActiveCell, 1, 2) & ":" & Mid(ActiveCell, 3, 250)

End With


Next i


--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




" wrote:

I am trying to record a simple macro to put a colon in front of the
character second from the right in a cell. Here's my issue:
I go to a cell, (let's say it has the number 1030 in it)
I start the macro recorder, give is a shortcut key (say, "W")
I click on the relative reference icon
I press F2 to edit the cell
I use the left arrow to move two positions to the left
I type in a colon (":")
I press return
Then I click on the end recording button
Now, what I want to happen is the when I go to the next cell and press
CTRIL-W is to have a colon inserted in front of the second character
from the right. So if this cell has the number 1045 in it I expect it
to end up as 10:45. What happens is it overwrites that cell with the
value from the previous cell with the colon in it. Instead of 10:45 I
get 10:30 again, and again, and again......sigh. Looking at the maco
in the VB editor, sure enough, theres the text "10:35". I don't want
the text. I want only the commands to enter edit mode in the current
cell and move two places to the left and insert a colon. In the
preVBA days this was SO easy: {edit}{left}{left}":"{down} and you were
done. That's all I want!
Thanks!