Run Time Error 91 - Object Variable or Block Variable Not Set

zalik22

Board Regular
Joined
Dec 14, 2010
Messages
79
Not sure what is wrong, can someone please help? Getting error at "' Find which column "Local Start Time" appears in" step.

VBA Code:
Sub MacroKeywords()
' Keyboard Shortcut: Ctrl+i
'Application.ScreenUpdating = False

Dim rngKeywordsFound    As Range, _
    rngAgentGroup   As Range


'Start of function to delete unwanted rows and columns
With ActiveSheet.Cells
Set rngAgentGroup = .Find("Agent Group", LookIn:=xlValues, LookAt:=xlPart)
Set rngKeywordsFound = .Find("Keywords Found", LookIn:=xlValues, LookAt:=xlWhole)
If Not rngAgentGroup Is Nothing And Not rngKeywordsFound Is Nothing Then
'AgentGroup and KeywordsFound found
If rngKeywordsFound.Column = rngAgentGroup.Column - 1 Then
'KeywordsFound column is adjacent to AgentGroup, clear only columns left of rngKeywordsFound
Range(Cells(1, 1), Cells(1, rngKeywordsFound.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
ElseIf rngKeywordsFound.Column = 1 Then
'KeywordsFound is first column. Only clear columns between rngKeywordsFound and AgentGroup
Range(Cells(1, rngKeywordsFound.Column + 1), Cells(1, rngAgentGroup.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
Else
'KeywordsFound column is not adjacent to AgentGroup. Clear columns between and then columns to left of rngKeywordsFound
Range(Cells(1, rngKeywordsFound.Column + 1), Cells(1, rngAgentGroup.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
Range(Cells(1, 1), Cells(rngKeywordsFound.Column - 1)).EntireColumn.Delete Shift:=xlToLeft
End If
Range("A1:A" & rngKeywordsFound.Row - 1).EntireRow.Delete Shift:=xlToLeft
End If
End With
'End of function to delete unwanted rows and columns

    Dim sortAdd As String
    Dim sortRange As Range

'   Find which column "Local Start Time" appears in
Rows("1:1").Find(What:="Local Start Time", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
sortAdd = ActiveCell.Address(0, 0)

' Convert entries in Date column to valid dates
Columns(ActiveCell.Column).TextToColumns Destination:=Range(sortAdd), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True

' Format columns
Columns(ActiveCell.Column).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

' Set sort range by using current region
Range("A1").CurrentRegion.Sort _
        key1:=Range(sortAdd), order1:=xlAscending, Header:=xlYes

End Sub
 
Last edited by a moderator:

zalik22

Board Regular
Joined
Dec 14, 2010
Messages
79
What is the point of asking for help, if you ignore the help that is given?
I have a lot of code and you helped me correct the issue. I tried to change the code with what you posted and that snippet worked but not the other portions of the code. You did help a lot, I would not have fixed the issue without your help. Thanks again for everything!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,904
Office Version
  1. 365
Platform
  1. Windows
What portions of the code did not work?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,904
Office Version
  1. 365
Platform
  1. Windows
If you want to post the code that does not work, I will take a look if you want.
 

zalik22

Board Regular
Joined
Dec 14, 2010
Messages
79
If you want to post the code that does not work, I will take a look if you want.
Hi, I played with it and got it to work. It was definitely your help that made everything work. Thanks again for all your help, I am sure I will need more help in the future.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,904
Office Version
  1. 365
Platform
  1. Windows
Glad you were able to sort it & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,660
Members
410,567
Latest member
SCraig123
Top