Can I make a Macro do the following for me ?

[NickTheBatMan]

I have a sheet of data where in column C I want to insert Chr10 in
each cell at the point where if I'm doing it manually I F2 to edit the
cell then Ctrl L Arrow thrice...

I've tried recording a Macro to do this but it just includes the text
of the particular cell that I'm currently editing and inserts the
Chr10 in where I have done it manually without telling me how to do
what I want....

I'd like to do it with a Macro as I've 1700ish rows to do and am
starting to get RSI from it

Eventually this will be saved as a CSV file so there'll be nothing
Excel in it...

Any help gratefully received

Nick

 

[Gary''s Student]

Let's pick a cell and enter:

go to alpha ralpha blvd

If we select the cell and touch F2 and then CNTRL-BACKARROW three times, the
editting cursor will be just before the alpha and this is where the Chr(10)
will go. Try this simple macro:

Sub batman()
For Each r In Selection
s = Split(r.Value, " ")
s(UBound(s) - 2) = Chr(10) & s(UBound(s) - 2)
r.Value = Join(s, " ")
Next
End Sub

 

[NickTheBatMan]

Mm, either I've got the wrong end of this or it don't do what I was
hoping for...

It's putting Chr10 - a CR - at the beginning of the cell, or if I
select several cells it progresses to the next cell and puts the next
space in the text until it runs out of text then it fails...

Here's some of the text from the cells: -

WHITEHOUSE FPS-DSN2-015-1672
WEST DYKE ROAD MCG-DSN3-022-1562
CHURCH LANE CCTV-DSN3-023-0440
GREEN LANE FPS-DSN3-024-1100
GREW GRASS UWC-DSN3-025-0110

As you can see there's not equal words to begin with, I want the CR
before the group that is held together by the - hyphen...

Nick

 

[Gary''s Student]

Have patience...we are making good progress. Your examples removed part of
my confusion. We clearly do not need the -2

Try this new version instead:

Sub batman()
For Each r In Selection
s = Split(r.Value, " ")
s(UBound(s)) = Chr(10) & s(UBound(s))
r.Value = Join(s, " ")
Next
End Sub


1. replace the macro
2. select the cells
3. run the macro

Post back if more modifications are needed.

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure if it is important to the OP or not, but your routine leaves a
trailing space at the end the first line formed by the split. I guess you
could use the Replace function like this to remove the trailing space...

Sub batman()
Dim r As Range
Dim s As Variant
For Each r In Selection
s = Split(r.Value, " ")
s(UBound(s)) = Chr(10) & s(UBound(s))
r.Value = Join(s, " ")
r.Value = Replace(r.Value, " " & Chr(10), Chr(10))
Next
End Sub

Here is an alternate subroutine which, as a by product of how it works, does
not introduce the trailing space in the first place...

Sub AddCR()
Dim Cell As Range
Dim Contents As String
For Each Cell In Range("A1:A5")
Contents = Cell.Text
Mid$(Contents, InStrRev(Contents, " ")) = vbLf
Cell.Value = Contents
Next
End Sub

Note my preference for the predefined VB constant vbLf instead of the
Chr(10) function call.

Rick

 

[NickTheBatMan]

Have patience...we are making good progress. Your examples removed part of
my confusion. We clearly do not need the -2

Try this new version instead:

Sub batman()
For Each r In Selection
s = Split(r.Value, " ")
s(UBound(s)) = Chr(10) & s(UBound(s))
r.Value = Join(s, " ")
Next
End Sub

1. replace the macro
2. select the cells
3. run the macro

Post back if more modifications are needed.

Weeeeeeeeeeeeee

Worked like a dream

Wish I could understand how to do all this clever stuff.........

VMT

Nick

 

[NickTheBatMan]

Many thanks for all that Rick, it doesn't matter about the space at
the end of the first line...

Didn't know about vblf, not sure if it's necessary as chr10's doing
what I want anyway...

For info all, this is me creating Points Of Interest layer for my
Garmin GPS so that I can upload it and it looks good on the device

It's worked so far...

Nick