• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

Macro stopped working

I have been using the below Macro [provided by Rgonzo1971] which finds a specific word and then deletes all rows above it, but it has now stopped working with the error Object Variable or With block Variable not set

CountrpartyRow = Cells.Find(What:="Counterparty Code", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row
    If CountrpartyRow > 1 Then
        Rows("1:" & CountrpartyRow - 1).Delete
    End If

Can an Expert assist please.
0
Jagwarman
Asked:
Jagwarman
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Does the phrase "Counterparty Code" exist in the spreadsheet AFTER the active cell?

If not, then you need to test for that.
0
 
NorieData ProcessorCommented:
Could be because the search term isn't being found.

Has the data changed? Is the code meant to look at a specific sheet?

If you use code like this you can avoid the error.
Dim rngFnd As Range
    
    Set rngFnd = Cells.Find(What:="Counterparty Code", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
     
    If rngFnd Is Nothing Then
        MsgBox "'Counterparty Code' not found."
    Else
        CountrpartyRow = rngFnd.Row
        If CountrpartyRow > 1 Then
            Rows("1:" & CountrpartyRow - 1).Delete
        End If
    End If

Open in new window

0
 
Wilder1626Commented:
You just need to add Counterparty Code in the spreadsheet after the active cell.

It is probably failing because it is missing. Just tested it and it does not fail when Counterparty Code is showing.
0
 
JagwarmanAuthor Commented:
many thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now