• 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

    Hui...

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

vletm

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?
 
Top