VBA (Offset, InsertColumn, CountIF

Skiera2

New Member
Joined
Mar 24, 2011
Messages
7
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

cb12

Board Regular
Joined
Dec 20, 2008
Messages
117
How about trying a loop perhaps something like

Code:
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

New Member
Joined
Mar 24, 2011
Messages
7
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"")))"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top