VBA (Offset, InsertColumn, CountIF

[Skiera2]

Hello,

I'm trying to insert a number of column in the middle of worksheet. The number of columns are dynamic based on the date range in another tab. I need to take the total number of columns in that tab and dynamically change the number of columns i add in based on whether a toggle choice is "month", "quarter", or "year". I am getting stuck on "InsertColumn" both in the location i want and the correct number of columns that need to be entered. This is my code so far:
'This macro is used to add the correct number of columns to the Financial Evaluation tab so that the equity costs
'are captured prior to COD and computed in the overall IRR
'
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

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")))"


Msg = Cols_to_Insert
MsgBox Msg

End Sub

 

[cb12]

How about trying a loop perhaps something like

For i = 1 To Cols_To_Insert
    With Cells(1, 6)
        .EntireColumn.Insert
    End With
Next i

I've used (1, 6) as I assume you will always insert in column G if not you would need to find a way of defining which column to insert from.

 

[Skiera2]

cb12,

Thank you for that feedback. I will probably modify my code to see if this works faster. I was currently able to address my problem with the following:

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


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