How to insert columns until a cell condition is metClosed

Question

Hi everyone.

I'm not very good in macro and hope that anyone could give me some pointers. I have a worksheet (ie. Project A) whereby it contains headings as such in row 1,
- Name in Column A
- Role in Column B
- Date Range (ie. 1/9/16 to 7/9/16) from Column C onwards to Column I.


I have a specific date (1/8/16) listed (ie. C10) in a another sheet (ie. Menu) within the same workbook that I need to apply on cell "B1" of worksheet (Project A) , which means replacing 1/9/16 in B1 to 1/8/16 instead. I want to know, how can I create my macro whereby, not only it can replace the date to 1/8/16, it will also insert columns as well in order to adjust accordingly, so that date range of 1/8/16 to 31/8/16 is inserted before reaching 1/9/16.


The ideal outcome is 1/8/16 will start at "B1" and 1/9/16 will now be found in "AH1". Thanks for your understanding. :)

TrowaD · Answer

Hi Bubblebluez,

Try the following code:
Sub RunMe()
Dim x As Integer

Sheets("Project A").Select
Columns("C:C").Insert
Range("C1").Value = Sheets("Menu").Range("C10").Value
x = 3

Do Until Cells(1, x).Value = Cells(1, x + 1).Value + 1
    Columns(x + 1).Insert
    Cells(1, x + 1).Value = Cells(1, x).Value + 1
    x = x + 1
Loop

End Sub


Best regards,
Trowa

How to insert columns until a cell condition is met

1 response

Similar discussions

Subscribe To Our Newsletter!