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

[zalik22]

Not sure what is wrong, can someone please help? Getting error at "' Find which column "Local Start Time" appears in" step.

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

 

[MARK858]

The error normally means that it is not finding the phrase. Check the spelling and the spacing between the words.

 

[Fluff]

Try

sortAdd = Range("1:1").Find("Local Start Time", , , xlPart, , xlNext, False, , False).Address(0, 0)

 

[zalik22]

Try

sortAdd = Range("1:1").Find("Local Start Time", , , xlPart, , xlNext, False, , False).Address(0, 0)

I couldn't get this to work. I replaced all of my Find which column "Local Start Time" appears in code. Is this right?

 

[Fluff]

Yes it is, if that is still giving you an error, then as Mark858 said, you need to check that "Local Start Time" appears in row 1 of the active sheet.

 

[zalik22]

Hi,

It does not appear in row 1, but shouldn't the code above it delete any data above and between "Keywords Found" and "Agent Group"?

 

[Fluff]

Does it appear in row 1 at the time that line of code runs?

 

[zalik22]

Strange, when I debug by pressing F8 line by line, the original code works but when I press the macro button to run it, I get the error.

 

[Fluff]

Is the button on the sheet you deleting the rows/columns from?

 

[zalik22]

Nope, it's on another spreadsheet.