CODEWITHSUNDEEP
excelvba
Jamey Cane
Jamey Cane

Reputation: 25

"Find and Replace" on active cell in For Loop generates Run-time Error '91' Object-variable or With block variable not set

I am attempting to simulate the "Find All" function on the Mac version of Excel (a feature the Windows version has).

I believe I have set this macro up to parse through each Active, used cell in the Active Sheet. When it finds a cell that has the letter "K" in it, I remove it (replace it with "") and then multiply the remaining value in the cell by 1000.

This is what I have.

Sub TestingFindAndReplace()

For Each Cell In ActiveSheet.UsedRange.Cells
    Cells.Find(What:="K", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True). _
        Activate

    ActiveCell.Replace What:="K", Replacement:="", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=True
      ActiveCell = ActiveCell * 1000
Next Cell

The script finds, replaces, and multiplies as it should. However, when it runs out of cells that contain "K"

Run-time Error '91' Object-variable or With block variable not set

pops up and prevents me from moving onto the next portion of the macro.

I'm guessing when it no longer has any cells left to make "Active" (as it does at the end of the highlighted line) the line after it, which is expecting an "ActiveCell", trips up.

I tried setting the ActiveCell, Cell, and Cells as an object because from what I've been able to find, it looks like I need to define some sort of object as something in order to proceed.

Upvotes: 0

Views: 186

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

Similar but IMHO slightly better

Sub Demo()
    Dim r As Range
    Dim c As Range
    Dim FirstAddr As String

    Set r = ActiveSheet.UsedRange
    Set c = r.Find(What:="K", LookIn:=xlValues, Lookat:=xlPart, MatchCase:=True)
    Do Until c Is Nothing
        c.Value2 = Replace$(c.Value2, "K", "")
        If IsNumeric(c.Value2) Then
            c.Value2 = c.Value2 * 1000
        End If
        Set c = r.FindNext(c)
    Loop
End Sub

Upvotes: 1

DisplayName
DisplayName

Reputation: 13386

I guess you''are after something like this (explanations in comments):

Sub main()
    Dim f As range

    With ActiveSheet.UsedRange ' reference wanted range
        Set f = .Find(what:="K", LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=True) ' try and find first cell matching searched value in referenced range
        If Not f Is Nothing Then ' if found
            Do
                f.Replace what:="K", replacement:="", lookat:=xlPart, MatchCase:=True ' replace matched string in found cell
                f.value = f.value * 1000 ' update found cell
                Set f = .FindNext(f) ' try and find next occurrence of searched value in referenced range
            Loop While Not f Is Nothing
        End If
    End With
End Sub

Upvotes: 0

Related Questions