insert column macro

[Altec101]

Hello,

Does anyone know how to have a macro in Excell insert a new colum
automatically?

Also, if i was comparing dates in two columns, how could i have i
automatically enter the difference in the column newly created?

Thanks,

Chri

 

[Ikaabod]

Yes, you can insert a column automatically and have it enter th
difference b/t two dates. What events will trigger the macro to inser
a column and enter the difference?

 

[Altec101]

I would like to have it insert the column and data when the macro is run
manually.

 

[Ikaabod]

Here's an example of how you might go about it, tho obviously your
ranges will likely differ:
Sub MyMacro()
Range("C:C").EntireColumn.Insert
With Range("C1")
..Value = "=(B1-A1)"
..NumberFormat = "General"
End With
End Sub

Then you can create a macro button to Call MyMacro when pressed.

 

[Altec101]

Thanks for the reply.

If i had 2 columns of data with multiple rows (say 50 rows - the amount
of rows will be different each time) and wanted to get a date difference
between column C and D and have it automatically input the difference
into column E, is there a way I can do that ?

 

[Ikaabod]

Give this a go
Sub MyMacro()
Dim i As Integer
i = 0
Range("E:E").EntireColumn.Insert
Range("E1").Select
Do
If IsEmpty(ActiveCell.Offset(i, -1)) = False Then
ActiveCell.Offset(i, 0).Value = "=(D1-C1)"
i = i + 1
End If
Loop Until IsEmpty(ActiveCell.Offset(i, -1))
Range("E:E").NumberFormat = "General"
End Sub

 

[Ikaabod]

Let's try this then...
Sub MyMacro()
Dim i As Integer
i = 0
Range("E:E").EntireColumn.Insert
Range("E1").Select
Do
If IsEmpty(ActiveCell.Offset(i, -1)) = False Then
ActiveCell.Offset(i, 0).FormulaR1C1 = "=(rc[-1]-rC[-2])"
i = i + 1
End If
Loop Until IsEmpty(ActiveCell.Offset(i, -1))
Range("E:E").NumberFormat = "General"
End Sub

 

[Altec101]

Thanks for the help