• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula string leading to run time error #13

I am trying to write a code segment as below
Sub makecols()
' makecols Macro

    Dim Rowinsert As Integer 'no. of rows to insert for each channel type count
    Dim Myrange As Range 'define the working range
    Dim Selchannel As Integer 'set up counter for number of channel types
    Dim Rowcount As Integer
    Dim Channel
    Sheets("volincols").Select ' making the selection the active worksheet
    Set Myrange = Range("datarange")
    Channel = Array("FS", "CSD", "CPC", "MT", "ECOM") 'names of the channel put in an array
    For Selchannel = 1 To 5 'iterate thru as many times as there are channels
    Rowcount = "=+countifs(" & Myrange & "," & Channel(Selchannel - 1) & ")" 'counts no. of rows for a given channel name
    For Rowinsert = 3 To Rowcount Step 12 ' iteration for no. of rows=no. of months;increments in steps of 12
    Rows("rowinsert:rowinsert+10").Select 'selects rows
    Selection.Insert Shift:=xlDown 'inserts rows
    Next Rowinsert
    Next Selchannel
End Sub
The variable Rowcount is supposed to count the rows for the channel name picked from the array - but I am getting a run time error at this point...am I writing the formula wrong?


Excel Ninja
monoj chakraborty
As You've written You Rowcount-code line and pressed <ENTER>
You should have seen that the whole line is red. = There is unknown syntax.
If You're writing code, then why You won't use eg Worksheetfunction.Countifs?