(Solved) – Excel VBA Column Insertion Using Loop Function

(solved)-–-excel-vba-column-insertion-using-loop-function

I’ve got dates in Row 2 and have the following code to insert a column based on whether the date in B1 is less than the date in B2, C2, etc….

Sub Test3()

If DateValue(Cells(1, 2).Value) < DateValue(Cells(2, 2).Value) Then
Range("B2").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(2, Selection.Column).Select
ActiveCell.Value = Range("B1")

ElseIf DateValue(Cells(1, 2).Value) < DateValue(Cells(2, 3).Value) Then
Range("C2").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(2, Selection.Column).Select
ActiveCell.Value = Range("B1")

End If

End Sub

The above code works and adds a column at the right place and puts a date in row 2 of the column.
Obviously it would be much easier for me to loop this but I am having trouble getting the loop to work. Here is what I have so far:

Sub DateLoopTest()
Dim i As Integer
i = 1
Do Until DateValue(Cells(1, 2).Value) < DateValue(Cells(2, i   1).Value)
Cells(2, i   1).EntireColumn.Select
i = i   1
Loop
ActiveCell.EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(2, Selection.Column).Select
ActiveCell.Value = Range("B1")

End Sub

I am getting Run-Time error '13': Type mismatch

No matter how much I mess with it I can't get it to loop like I want in my 1st example. Any suggestions

Leave a Reply

Your email address will not be published. Required fields are marked *