Simple Text Editing Macro Help
 

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!

Simple Text Editing Macro Help
 

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

Simple Text Editing Macro Help
 

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

Simple Text Editing Macro Help
 

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!