current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I'm trying to select a range with todays date only, but receiving Runtime error 91 on the Cells.find(TodayLast).Activate line. I can't understand what seems to be the problem.

 Sub Escalation()

Dim rng As Range
Dim rngsend As Range
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim strto As String
Dim rngHeader As Range
Dim TodayFirst As Range
Dim TodayLast As Range
Dim LastDate As String

' Finds the area of today's range
LastRow = Sheets("Escal").Range("A65536").End(xlUp).Row
Cells(LastRow, 1).Activate
Set TodayLast = ActiveCell
Cells.find(TodayLast).Activate
Set TodayFirst = ActiveCell
Range(TodayFirst, TodayLast.Offset(0, 6)).Select

'Sorterar breacharna - Sorts the breaches
Selection.Sort Key1:=Range("G1"), Key2:=Range("B1"), Key3:=Range("D1")

'A loop that divides the various comps and enters a GoSub formula that prepares mails
Cells(TodayFirst.Row, 7).Activate
Set CompanyFirst = ActiveCell
Do Until IsEmpty(CompanyFirst)
Cells.find(What:=CompanyFirst, LookIn:=xlValues, SearchDirection:=xlPrevious).Activate
Set CompanyLast = ActiveCell
GoSub PrepareMail
Cells(CompanyLast.Row + 1, 7).Activate
Set CompanyFirst = ActiveCell

Loop

Cells(LastRow, 1).Select

Exit Sub
share|improve this question
    
What type does TodayLast have? Is it Range? –  makciook Jul 24 '13 at 13:16
    
Yes it's Range, same as TodayFirst –  Artem Korol Jul 24 '13 at 13:17
    
you must have done something right ... your code works well here - even with a single TodayLast (Win7+XLS2010) ... why is the "find" in your code lower case ... typo or unknown keyword in VBA? –  MikeD Jul 24 '13 at 13:30
    
Not a typo, VBA converts it to lowercase automaticaly. Actualy when i run the whole code for the first time it works fine, after i try to run it again it gives this error. I tried to reset TodayLast to Nothing at the end, but it does not help. What can be the issue here? –  Artem Korol Jul 24 '13 at 13:45
    
I mean if I open the excel document and run the macro it will produce the correct email, if i will try to run it again it will show this error. If i close the document, open it and run the macro it will work, again only once. –  Artem Korol Jul 24 '13 at 13:56
add comment

1 Answer

up vote 0 down vote

It's possible that "Escal" isn't the activesheet. You fully qualify the range when finding the last row, but don't in any of the subsequent code. You also have a bunch of unnecessary activates and selects, which lead to exactly the kind of bug you are experiencing. Declare object variables and use those instead. You'll lose the overhead of making sure the correct object is active and avoid this kind of bug in the future.

You should also ALWAYS require variable declaration. (Tool --> Options...)

Try re-writing your code like this:

Sub AvoidRTE91()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim todayLast As Range
    Dim todayFirst As Range
    Dim todayRange As Range

    Set ws = ThisWorkbook.Sheets("Escal")
    lastRow = ws.Range("A65536").End(xlUp).Row

    ' added message for debugging purposes. Remove once you figure out what is wrong.
    MsgBox ws.Cells(lastRow, 1) & " is in cell " & ws.Cells(lastRow, 1).Address
    Set todayLast = ws.Cells(lastRow, 1)
    Set todayFirst = ws.Cells.Find(todayLast)
    ' you don't have to select the range to work with it
    Set todayRange = ws.Range(todayFirst, todayLast.Offset(0, 6))
    ' now instead of using selection, use todayRange...

End Sub
share|improve this answer
    
Last row is being declared correctly, now ws.Range produces an error "Method Range of object Worksheet failed" –  Artem Korol Jul 24 '13 at 14:18
    
Are you running my code as is, or have you incorporated it into yours? –  Jon Crowell Jul 24 '13 at 14:22
    
I tried both ways, your code run seperately works fine, but when added inside my programm i get this error. –  Artem Korol Jul 24 '13 at 14:32
    
What is the full line of code that is failing in yours? –  Jon Crowell Jul 24 '13 at 14:56
    
This one Set todayRange = ws.Range(todayFirst, todayLast.Offset(0, 6)) –  Artem Korol Jul 25 '13 at 10:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.