This is my whole macro so far...


Private Sub cmd2_Click()
Dim Mrange
Dim Mrows As Long
Dim Mcolumns As Long
Dim Mcell
Dim McolumnC
Dim MrowC
Dim MST
Dim MST1


Dim McolumnC2
Dim MrowC2


'Picks up the range from the form

Mrange = ChangeToDateFormat.RefEdit1

Range(Mrange).Select

Mcell = ActiveCell.Address

' Counts the Rows and Columns of the selection

Mrows = Selection.Rows.Count
Mcolumns = Selection.Columns.Count

' Adds Columns to work with

Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

' Adds the forumals in the added columns
Range(Mcell).Select
McolumnC = Range(Mcell).Column
MrowC = Range(Mcell).Row


ActiveCell.FormulaR1C1 = "=RC[1]&""/""&RC[2]&""/""&RC[3]"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[2],5,2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],4)"

' Mark formulas and Copy them
MST1 = ActiveCell.Address
MST = ActiveCell.Offset(0, 4).Address

Range("" & MST1 & ":" & MST).Copy

'Zero means stay in the same Row or Column

MST = ActiveCell.Offset(myRows, 4).Address

Range("" & MST1 & ":" & MST).Paste

' Copy and paste special values, Result

MST = ActiveCell.Offset(Mrows, 0).Address

Range("" & MST1 & ":" & MST).Copy
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False

' Delete the old and unneeded columns

MST1 = ActiveCell.Offset(0, 1).Address
MST = ActiveCell.Offset(Mrows, 4).Address

Range("" & MST1 & ":" & MST).Select

Selection.Delete Shift:=xlToLeft
End Su

--
Ctec
-----------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=48098