CODEWITHSUNDEEP
vbaexcel
BradleyS
BradleyS

Reputation: 215

Run-time error 91 - Object variable or With block variable not set

Can someone point out what's wrong in my code as I'm getting a...

Run-time error 91 - Object variable or With block variable not set

on this line in the code below

Loop While c2.Address <> e

Dim Cell As Range
Dim SrchRng2 As Range
Dim c2 As Range, e As String

'Check each row in column - if BLUE text (set by CF) change to #N/A
For Each Cell In Intersect(Columns("E"), ActiveSheet.UsedRange)
    If Cell.DisplayFormat.Font.ColorIndex = 5 Then Cell.Value = "#N/A"
Next
On Error GoTo NoBlueText
'Search column E for cells with #N/A and clear cells across columns E:G in row
Set SrchRng2 = ActiveSheet.Range("E2", ActiveSheet.Range("E" & Rows.Count).End(xlUp))
Set c2 = SrchRng2.Find("#N/A", LookIn:=xlValues)

If Not c2 Is Nothing Then
    e = c2.Address
Do
            ActiveSheet.Range("E" & c2.Row & ":G" & c2.Row).Cells.ClearContents
        Set c2 = SrchRng2.FindNext(c2)
    Loop While c2.Address <> e
End If

NoBlueText:

Upvotes: 1

Views: 661

Answers (1)

user3598756
user3598756

Reputation: 29421

since you're first placing "#N/A" in cells and then look for them, wouldn't it be simpler to act directly at the first stage?

With ActiveSheet
    With .Range("E2", .Cells(.Rows.count, "E").End(xlUp))
        For Each cell In .Cells
            If cell.DisplayFormat.Font.ColorIndex = 5 Then cell.Resize(, 3).ClearContents
        Next
    End With
End With

Upvotes: 4

Related Questions