Loop through column and change contents

  • Thread starter Thread starter Jake
  • Start date Start date
J

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
 
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
 
Mike said:
Jake,

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

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

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