placing "X" at offset point

[jarhead58]

Hi everyone!!

The following code had been working;

Sub Prodigal()Dim x As Range


If IsEmpty(Sheets("Watched").Range("I2")) = True Then
    Sheets("Watched").Range("I2") = "X"
Else
[COLOR=#ff0000]    If IsEmpty(Sheets("Watched").Range("I2")) = False Then[/COLOR]
[COLOR=#ff0000]        Set x = Sheets("Watched").Range("I2:I26").Find("X", LookIn:=xlValues)[/COLOR]
[COLOR=#ff0000]        If x.Address <> "$I$26" Then[/COLOR]
[COLOR=#ff0000]            x.Offset(7, 0) = "X"[/COLOR]
[COLOR=#ff0000]ElseIf x.Address = "$I$26" Then[/COLOR]
    
    MsgBox "Time to change the range!"


End If
End If
End If
End Sub

But when I changed the range in the red section to the following;

Sub Prodigal()Dim x As Range


If IsEmpty(Sheets("Watched").Range("I2")) = True Then
    Sheets("Watched").Range("I2") = "X"
Else
[COLOR=#ff0000]    If IsEmpty(Sheets("Watched").Range("I2")) = False Then[/COLOR]
[COLOR=#ff0000]        Set x = Sheets("Watched").Range("I2:I500").Find("X", LookIn:=xlValues)[/COLOR]
[COLOR=#ff0000]        If x.Address <> "$I$500" Then[/COLOR]
[COLOR=#ff0000]            x.Offset(7, 0) = "X"[/COLOR]
[COLOR=#ff0000]ElseIf x.Address = "$I$500" Then[/COLOR]
    
    MsgBox "Time to change the range!"


End If
End If
End If
End Sub

It didn't work; no errors! Any ideas?

TIA

 

[jarhead58]

Getting a runtime error '91'

Object variable or With block variable not set

 

[Fluff]

In that case you don't have an X in the range, try

Sub Prodigal()
    Dim x As Range
    
    With Sheets("Watched")
        If IsEmpty(.Range("I2")) Then
            .Range("I2") = "X"
        Else
            Set x = Range("I2:I500").Find("X", , xlValues, xlWhole, xlByRows, xlPrevious, False, , False)
            If Not x Is Nothing Then
                If x.Row < 494 Then
                    x.Offset(7, 0) = "X"
                Else
                    MsgBox "Time to change the range!"
                End If
            Else
                MsgBox "No X"
            End If
        End If
    End With
End Sub

 

[jarhead58]

In that case you don't have an X in the range, try

Sub Prodigal()
    Dim x As Range
    
    With Sheets("Watched")
        If IsEmpty(.Range("I2")) Then
            .Range("I2") = "X"
        Else
            Set x = Range("I2:I500").Find("X", , xlValues, xlWhole, xlByRows, xlPrevious, False, , False)
            If Not x Is Nothing Then
                If x.Row < 494 Then
                    x.Offset(7, 0) = "X"
                Else
                    MsgBox "Time to change the range!"
                End If
            Else
                MsgBox "No X"
            End If
        End If
    End With
End Sub

Actually there are 3 previous 'X' but nothing is happening.

 

[jarhead58]

I used it referring to a blank sheet and it did it for I2 but nothing after

 

[Fluff]

Not sure why that happened, I tested it on a blank sheet & it put Xs all the way to I499.

 

[jarhead58]

Not sure why that happened, I tested it on a blank sheet & it put Xs all the way to I499.

Hmmm....I copy and pasted what you have but changed it to sheet1 instead of watched. It put the X in I2 but then No X pops up after that

 

[Fluff]

Oops, it's missing a . from Range on this line

Set x =[COLOR=#ff0000] .[/COLOR]Range("I2:I500").Find("

 

[jarhead58]

Oops, it's missing a . from Range on this line

Set x =[COLOR=#ff0000] .[/COLOR]Range("I2:I500").Find("

That was it! Just a lil bitty . messed that all up lol!

 

[Fluff]

Yup, I took it out to get the tool tip for the Find arguments & forgot to put it back in

 

[jarhead58]

Thanks again for your help Fluff!!