Loop through column and change contents

[Jake]

Hi,

How do I loop through columns in a spreadsheet where one contains
forename and the other contains name and have these copied to a third
column as <forename> <name>?

Also how do I loop through another column containing strings and insert
a dash between the third and forth character in that string?

Thanks a lot for help on this

regards

jake

 

[Mike H]

Jake,

Q1. If the names have a space between them use text to columns with space as
a delimiter.

Q2. Right click your sheet tab, view code and paste the code below in and
run it. Change the column to suit

Sub stantial()
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
c.Value = Left(c.Value, 3) & "-" & Mid(c.Value, 4)
Next
End Sub

Mike

 

[Jake]

Jake,

Q1. If the names have a space between them use text to columns with space as
a delimiter.

They have a space as delimiter, but I prefer to do it programmatically
so I can learn from this.

Say that forenames are in col A and names are in B and I want the
concenated strings pasted into the C column. How would that be in vba?

Q2. Right click your sheet tab, view code and paste the code below in and
run it. Change the column to suit

Sub stantial()
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
c.Value = Left(c.Value, 3) & "-" & Mid(c.Value, 4)
Next
End Sub

Thanks, it works like a charm. What if this column was in Sheet1 and I
want the result in column C in Sheet2?

regars and thanks a lot for the help

Geir