Run time 424 error when using a Range in For loop

[ukmohan81]

Hi Experts, 

I am a novice to VBA scripting learning to code.  I am building a utility using VBA to work on one my Excel workbooks.  The utility is supposed to look at worksheet "Workshop Listing" - read each of the cells in a specific column, which holds the dates.  There is another worksheet called Calendar which has the calendar structure built.  Based on the date value read, the sub is supposed to copy the text from another column in the workshop listing sheet and go to the calendar worksheet and insert copied text under the date value row in the calendar.  While I am trying to run the code and understand the issues, I am unable to debug the error I am encountering when I m calling the range and using it in a For loop.  Error is - Run time error '424': Object required.  Can anyone help me understand why this error is showing up and what should I correct in calling the range in the For loop?   Below is the code :

Sub Calendar_function()
Dim dates_input, c_copy_addr As Range
Dim c_m, c_d, c_fmrow, c_fmaddr, c_nm, c_nmaddr As String
Dim c_s_rg, c_t_range As Range
Dim c_t_cell, c_t_blank As String
Dim c_det_cell As Range

MsgBox "This is the Calendar routine"
MsgBox "This routine will loop through each cell of the range provided as input and inserts the corresponding meeting text from another column into the respective calendar section based on the date it belongs"

'Set dates_input = Application.ActiveWorkbook.Worksheets("Workshop Listing").Range("K2:K66")
'Set dates_input = Application.InputBox("Please select the Column of cells to be considered for processing", "Data Range")'MsgBox ("the range selected is" & dates_input)
'Debug.Print dates_input.Cells(1, 1).Value

For Each cell In Worksheets("Workshop Listing").Range("K2:K66")
Set c_copy_addr = cell.Address.Offset(0, -7)
c_m = Month(cell.Value)
MsgBox "current cell selected is" & cell.Address & " The month derived is" & c_m
c_d = Day(cell.Value)
ActiveWorkbook.Sheets("Calendar").Activate
c_fmrow = Range("AE:AE").Find(What:=c_m, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True).Row
c_fmaddr = c_fmrow & 1
c_nm = Month(cell.Value) + 1
c_nmaddr = Range("AE:AE").Find(What:=c_nm, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True).Address
Set c_s_rg = Range("c_fmaddr:c_nmaddr")
c_t_range = c_s_rg.Find(What:=c_d, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True).Offset(1, 0)
c_t_cell = c_s_rg.Find(What:=c_d, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True).Offset(1, 0)
If IsEmpty(c_t_cell) = "TRUE" Then
Range(c_t_range).EntireRow.Insert
c_det_cell = Range(c_t_cell).Offset(1, 0)
Range(c_copy_addr).Copy c_det_cell
End If
'ActiveWorkbook.Sheets("Workshop Listing").Activate
Next cell
End Sub

[#2]

@ukmohan81 Can you please indicate on which line the error occurs precisely?

I expect it is on this line:

c_fmrow = Range("AE:AE").Find(What:=c_m, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True).Row

and happens when the c_m value cannot be found. Better to use an object variable:

        Dim FoundCell As Range
        Set FoundCell = Nothing
        Set FoundCell = Range("AE:AE").Find(What:=c_m, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True)
        If Not FoundCell Is nothing Then
            c_fmrow = FoundCell.Row
        Else
            'Not found, do something!
        End If

Some comments on your code:

- The variable cell is not declared

- Your DIM statements are wrong:

Dim dates_input, c_copy_addr As Range

only declares c_copy_addr As Range, dates_input is declared as variant. The correct syntax is:

 Dim dates_input As Range, c_copy_addr As Range