dynamic # of column insert in middle of data

[Skiera2]

Hi,

First time user today and no luck with my first post. I need help.

I need to be able to enter a dynamic number of columns (they change based on the construction period for the project and the reporting period (month, quarter, year)). I can get one column to be inserted, but not the dynamic number that i need. Please help. Been working on this for two days and I know there is a better way than writing a code to highlight the desired number of columns and then insert.

Dim NumberOfColumns As Double
Dim Cols_to_Insert As Double
Dim Period As Double
NumberOfColumns = WorksheetFunction.CountIf(Sheets("Construction").Range("H6:BY6"), ">0")
Period = Worksheets("Financial Evaluation").Range("Fin_mon_length")
Cols_to_Insert = WorksheetFunction.RoundUp(NumberOfColumns / Period, 0)
'Range("G1").Offset(0, 1).EntireColumn.Cells(1, Cols_to_Insert).Insert
'Columns("H:H).Select
Selection.Insert Shift:=Application.CountIf(Range("h10:iz10"), ">0")
Worksheets("Financial Evaluation").Range("C95") = "=XIRR(OFFSET(range("G94"),0,1,1,COUNTIF(range("H10:IZ10"),">0")),OFFSET(range("G10"),0,1,1,COUNTIF(range("H10:IZ10"),">0")))"
Thank you,
Erin

 

[Tigs81]

Hi

I would do something like this to insert the number of rows that you need.

Do While Cols_to_Insert <> 0
    Columns("H:H").Select
    Selection.Insert
    Cols_to_Insert = Cols_to_Insert - 1
Loop

Thanks
Tigs

 

[Skiera2]

Tigs81,

Thank you very much...that worked. Its slower than i would like since its a loop, but it does the job.

Thank you again and thank you for your time posting.

Have a great day,
Skiera2

 

[Skiera2]

Tigs81,

I was able to figure out a faster method than the loop...here is the full code:
Dim NumberOfColumns As Double
Dim Cols_to_Insert As Double
Dim Period As Double
NumberOfColumns = WorksheetFunction.CountIf(Sheets("Construction").Range("H6:BY6"), ">0")
Period = Worksheets("Financial Evaluation").Range("Fin_mon_length")
Cols_to_Insert = WorksheetFunction.RoundUp(NumberOfColumns / Period, 0)
Range("H1:" & Chr(Asc("H") + Cols_to_Insert - 2) & "1").Select
Range("H1:" & Chr(Asc("H") + Cols_to_Insert - 2) & "1").EntireColumn.Insert

'THis copies over the equations in the last column before the operation data starts
' accross to the correct number
Range(Cells(10, 7 + Cols_to_Insert), Cells(11, 7 + Cols_to_Insert)).Select
Selection.AutoFill Destination:=Range(Cells(10, 8), Cells(11, 7 + Cols_to_Insert)), Type:=xlFillDefault

Range(Cells(90, 7 + Cols_to_Insert), Cells(94, 7 + Cols_to_Insert)).Select
Selection.AutoFill Destination:=Range(Cells(90, 8), Cells(94, 7 + Cols_to_Insert)), Type:=xlFillDefault


'This loop works, but take a VERY long time.
'Do While Cols_to_Insert <> 0
' Columns("H:H").Select
' Selection.Insert
' Cols_to_Insert = Cols_to_Insert - 1
'Loop


Worksheets("Financial Evaluation").Range("C95") = "=XIRR(OFFSET(R94C7,0,1,1,COUNTIF(R10C8:R10C248,"">0"")),OFFSET(R10C7,0,1,1,COUNTIF(R10C8:R10C248,"">0"")))"
end sub