Error in VBA Code

[ameya]

I am getting the "object does not support this property or method error" (Run Time error 438) in the bolded line below. Any reasons as to why this does not work?

Dim rInitialDate As Range, rFinalDate As Range, rDateRange As Range, Calculation As Worksheet
    
With Worksheets("Calculation")
    [B]Set rInitialDate = .Find(What:=DateValue(InitialDate), After:=.Cells(1, 5), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext, SearchFormat:=False)[/B]
    Set rFinalDate = .Find(What:=DateValue(FinalDate), After:=.Cells(1, 5), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchFormat:=False)
    If rInitialDate Is Nothing Or rFinalDate Is Nothing Then
        MsgBox "Not able to find both dates"
    Else
        Set rDateRange = .Range(rInitialDate, rFinalDate)
    End If
End With
Dim startRow As Integer, endRow As Integer, rDateRangeIndex As Integer, rOEERange As Range
startRow = rInitialDate.Row
endRow = rFinalDate.Row
With Worksheets("Calculation")
    Set startOEECell = .Cells(startRow, 45)
    Set endOEECell = .Cells(endRow, 45)
    Set rOEERange = .Range(startOEECell, endOEECell)
End With
'to create new Range to reflect MC,Shift,Date
With Worksheets("Calculation")
    For i = startRow To endRow Step 1
        If Cells(i, 7) Is Not MC.Value Or Cells(i, 6) Is Not Shift.Value _
            Or DateValue(rDateRange(i - (startRow - 1))) < DateValue(InitialDate) _
            Or DateValue(rDateRange(i - (startRow - 1))) > DateValue(FinalDate) Then
                rDateRange(i - (startRow - 1)).Delete
                rOEERange(i - (startRow - 1)).Delete
        End If
    Next i
End With

 

[VBA Geek]

Set rInitialDate = [B][COLOR=#ff0000].USEDRANGE[/COLOR][/B].Find(What:=DateValue(InitialDate), After:=.Cells(1, 5), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext, SearchFormat:=False)

Try it like this.

 

[ameya]

Thanks! That seemed to work! However, I'm running into the "error object variable or with block not set run time error 91" in the line

startRow = rInitialDate.Row

Any ideas on that one?

 

[VBA Geek]

(rInitialDate is nothing) most certainly returns TRUE

if it is nothing then it means that what you wanted to find was not found, rInitialDate is not associated with any object and you cannot use it


maybe you want to Exit Sub after?:


MsgBox "Not able to find both dates"

 

[ameya]

Yeah I added the exit sub, thank you for the suggestion. Now, however, I keep getting the msgbox "not able to find both dates" even if the dates I input are clearly in my excel file.

 

[VBA Geek]

what is your InitialDate?

how are dates formatting on sheet? if you do CONTROL + F and try to find it manually, does it find it?

 

[ameya]

Oh man you're good! For some reason 7/1/2014 (along with the rest of the dates) is not recognized by control F. I will try to see how to fix this situation. Thanks for pointing out the problem!

 

[ameya]

Fixed it! The error was that I was searching in formulas not values.
Thank you VBA Geek!

 

[ameya]

Getting a new error now, and I wanted to know if you could take a look at it perhaps. In the code below, I'm getting an error in the .delete statements. Not sure why. If you want to see the entire document, here is a link to it if you would like to interact with the document yourself : http://jmp.sh/zA2Zrpb

Thanks so much!

With Worksheets("Calculation")
    For i = endRow To startRow Step -1
        If .Cells(i, 7) <> MC.Value Or .Cells(i, 6) <> Shift.Value _
            Or DateValue(rDateRange(i - (startRow - 1))) > DateValue(FinalDate.Value) _
            Or DateValue(rDateRange(i - (startRow - 1))) < DateValue(InitialDate.Value) Then
                rDateRange(i - (startRow - 1)).Delete
                rOEERange(i - (startRow - 1)).Delete
        End If
    Next i
End With

 

[VBA Geek]

How did you declare rDaterange and rOEERange?