Invalid or unqualified reference

[MrHawkins88]

Hello,

When I run this code I get a compile error that says "Invalid or unqualified reference" when I looked into it the error seems to be saying that I am not using a With statement properly. But I am not using a with statement at all.

Has anyone got any suggestions?

Sub deletingdata()
 
Dim i           As Integer
Dim j           As Integer
Dim WS          As Worksheet
Dim toprow      As Range
Dim lastrow     As Range
Dim AFcolumn    As Range
 
i = 0
For Each WS In Worksheets
    i = i + 1
    Sheets(WS.Name).Activate
    
    If i <> 1 Then
    
        Range("D:D").Cells.Replace What:="nan", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
        On Error Resume Next
        Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        ActiveSheet.UsedRange
        Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3).Row
                        
        Set AFcolumn = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Column
                        
        lastrow = Range("AFCol & 65536").End(xlUp).Row
        For j = lastrow To toprow Step -1
        If Cells(j, AFcolumn).Value <> "00*555" Then Cells(j, o).Delete shift:=xlUp
        Next j
        
    End If
    
End Sub

Thanks in advance!

 

[MrHawkins88]

Sorry the line of code highlighted when i get the error is

Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _

 

[John Davis]

Hello,

When I run this code I get a compile error that says "Invalid or unqualified reference" when I looked into it the error seems to be saying that I am not using a With statement properly. But I am not using a with statement at all.

Has anyone got any suggestions?

Sub deletingdata()
 
Dim i           As Integer
Dim j           As Integer
Dim WS          As Worksheet
Dim toprow      As Range
Dim lastrow     As Range
Dim AFcolumn    As Range
 
i = 0
For Each WS In Worksheets
    i = i + 1
    Sheets(WS.Name).Activate
    
    If i <> 1 Then
    
        Range("D:D").Cells.Replace What:="nan", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
        On Error Resume Next
        Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        ActiveSheet.UsedRange
        Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3).Row
                        
        Set AFcolumn = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Column
                        
        lastrow = Range("AFCol & 65536").End(xlUp).Row
        For j = lastrow To toprow Step -1
        If Cells(j, AFcolumn).Value <> "00*555" Then Cells(j, o).Delete shift:=xlUp
        Next j
        
    End If
    
End Sub

Thanks in advance!

I believe that after your End If and before your End Sub you need a Next.

 

[MrHawkins88]

Hello,

Thanks for the reply, looking at the macro now I see that you are correct for it to run properly I do need another next.

However, the bad news is I still have the same error appearing when I run this macro with the same line of data highlighted.

Any other suggestions?

 

[Michael M]

Lastrow should be dimmed as Long
Also, shouldn't this line be

lastrow = Range("AFCol & 65536").End(xlUp).Row

lastrow = Cells(Rows.Count, "AFCol").End(xlUp).Row

AND
I think AFCol should be AFColumn !
If you choose Option Explicit in your VBA preferences it would eliminate or identify a lot of syntax errors for you.

 

[MrHawkins88]

Thanks for the help guys, Michael I will look into option explicit.

I'm sorry to say though I am still getting the same fault code, if anyone is able to suggest how to fix it, that would be much appreciated.

It says "Compile error: Invalid or unqualified reference"

and the line

Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3).Row

is highlighted

anyway here is the rest of the code...

Sub DeletingExcessData()
Dim i           As Integer
Dim j           As Integer
Dim WS          As Worksheet
Dim toprow      As Range
Dim lastrow     As Long
Dim AFcolumn    As Range
i = 0
For Each WS In Worksheets
    i = i + 1
    Sheets(WS.Name).Activate
    
    If i <> 1 Then
    
        Range("D:D").Cells.Replace What:="nan", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
        On Error Resume Next
        Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        ActiveSheet.UsedRange
        Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3).Row
                        
        Set AFcolumn = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Column
                        
        lastrow = Cells(Rows.Count, "AFCol").End(xlUp).Row
        For j = lastrow To toprow Step -1
        If Cells(j, AFcolumn).Value <> "00*555" Then Cells(j, o).Delete shift:=xlUp
        Next j
        
    End If
    
    Next
    
End Sub

Thanks again for the help people!

 

[Norie]

The most probable reason for this error is because the Find isn't, well, finding anything.

Also why are toprow and AFColumn declared as Ranges? Isn't just the integer value you want.

And why is ActiveSheet.UsedRange just kind of sitting in the middle, it must be feeling left ouy.

Try this, it compiles and runs but I don't know if it does what you want - nothing to test it on.

Option Explicit
 
Sub deletingdata()
Dim I As Long
Dim J As Long
Dim ws As Worksheet
Dim rngFnd As Range
Dim toprow As Long
Dim lastrow As Long
Dim AFcolumn As Long
 
    For Each ws In Worksheets
 
        I = I + 1
 
        If I <> 1 Then
            With ws
                .Range("D:D").Cells.Replace What:="nan", Replacement:="", LookAt:=xlPart, SearchOrder _
                                                                                          :=xlByColumns, MatchCase:=False
                '.Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
                Set rngFnd = .UsedRange.Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                                             SearchDirection:=xlNext, MatchCase:=False)
 
                If Not rngFnd Is Nothing Then
                    toprow = rngFnd.Offset(0, 3).Row
                End If
 
                Set rngFnd = .UsedRange.Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                                             SearchDirection:=xlNext, MatchCase:=False)
 
                If Not rngFnd Is Nothing Then
                    AFcolumn = rngFnd.Column
                End If
 
                lastrow = .Cells(Rows.Count, AFcolumn).End(xlUp).Row
 
                For J = lastrow To toprow Step -1
                    If .Cells(J, AFcolumn).Value <> "00*555" Then
                        .Cells(J, 0).Delete shift:=xlUp
                    End If
                Next J
 
            End With
 
        End If
 
    Next ws
 
End Sub

 

[MrHawkins88]

Wow you guys are running rings around me

Thanks for your help Norie, it doesn't do exactly what I am after, but as you said you do not have any data.

Anyway I think that I should be able to play around with it to make it do what I need with some slight alterations.

Thanks for everyone's contributions!

 

[Ruddles]

Surely the .Find commands are the unqualified references? The compiler is complaining that there isn't a With statement so it doesn't know what to stick in front of .Find to qualify it completely.

Should it be just plain Find?

 

[Norie]

Ruddles

Did you try Find just on it's own?

Won't work I'm afraid - Sub or Function not defined...

Though you are right about the Find not referring to anything.

That would have kicked off a compile error, I thought the OP was getting a run-time error.

That's why I said the Find wasn't finding anything.

I think the code I posted dealt with both problems, the one you point out and the possible problem I spotted.