Can I make a Macro do the following for me ?

  • Thread starter Thread starter NickTheBatMan
  • Start date Start date
N

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
 
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
 
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
 
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.
 
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
 
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.
Click to expand...

Weeeeeeeeeeeeee :) :) :)

Worked like a dream :) :) :)

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

VMT

Nick
 
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
 
Back
Top