I don't understand where the error is.

There's no shame in our love for gay porn, and we make sure to always respect the actors and their work.
https://gayporn.name/channels/men/
https://freegayporn.club/categories/anal/
https://gaypornwebsite.com/categories/twink/

Poe is saying that is due to xlToRight and xlDown that are not recognized in VB6.
try changing those to 4161 and 4121

"Poe is saying"?

Are you really just asking some glorified chatbot then not even sanity checking the result?

Both of those are members of the xlDirection enum in the Excel Object Library. If OP didn't have a reference to that, problems would come up long before the presented snippet.

Code:

---

    Enum XlDirection
        xlDown = -4121                                      ' &HFFFFEFE7&
        xlToLeft = -4159                                    ' &HFFFFEFC1&
        xlToRight = -4161                                    ' &HFFFFEFBF&
        xlUp = -4162                                        ' &HFFFFEFBE&
    End Enum

---

OP, You might be better off asking this over in the Office Development subforum, I don't think this is a VB6-specific issue, and you'll find a lot more people over there familiar with Excel VBA. Unless you're having an issue where the code works in Excel VBA but not VB6.

I dont work with excel and if nobody can help why not. u didnt give anything useful yourself. so u are as bad as Poe.
coding is about trial and error.

I would suggest fully qualifying your range references - this is particularly important when you're getting VBA to work across multiple worksheets. I've adjusted your code below on the first line by adding CurWS and then on the second line by adding bsWS to the respective secondary range objects. Hopefully, that helps.

Code:

---

For Each cell In CurWS.Range("N7", CurWS.Range("N7").End(xlToRight))
    For Each cell2 In bsWS.Range("D19", bsWS.Range("D19").End(xlDown))
        If cell.Value = cell2.Value Then
            MsgBox "Duplicate Name"
            CurWS.Range("N7").ClearContents
            Exit For
        Else
        End If
    Next cell2
Next cell

---

Quote:

---

Originally Posted by BizzyBee

Code:

---

  For Each cell In curWs.Range("N7", Range("N7").End(xlToRight))
        For Each cell2 In bsWs.Range("D19", Range("D19").End(xlDown))

---

---

Any Range not qualified by the Worksheet on which it appears will be assumed to be on the current Worksheet.
You need to dot a few Worksheet qualifiers in there, something like this (untested):

Code:

---

  For Each cell In curWs.Range("N7", curWs.Range("N7").End(xlToRight))
        For Each cell2 In bsWs.Range("D19", bsWs.Range("D19").End(xlDown))

---

Regards, Phill W.

Quote:

---

Originally Posted by baka

I dont work with excel and if nobody can help why not. u didnt give anything useful yourself. so u are as bad as Poe.
coding is about trial and error.

---

I'm just saying "AI" chatbots are still at the point where basic sanity checks should be applied to their results, like taking a few seconds to see if a constant is or is not defined.

And I pointed OP to someplace that could help.

Quote:

---

Originally Posted by BizzyBee

Code:

---

  For Each cell In curWs.Range("N7", Range("N7").End(xlToRight))
        For Each cell2 In bsWs.Range("D19", Range("D19").End(xlDown))
        If cell.Value = cell2.Value Then
    MsgBox "Duplicate Name"
    curWs.Range("N7").ClearContents
    Exit For
    Else
    End If
   
    Next cell2
    Next cell

---

Method 'Range' of object 'Worksheet' failed... but why?

The error appears on line 2.

---

Dan has already given you the answer as to why you are getting that error. It is important that you qualify your objects completely.

As a personal preference, I always declare objects and work with it. I also try to avoid unnecessary loops. What I have understood is that you are checking for duplicates from the first range in the second range and then removing them. If my understanding is correct then try this.

Code:

---

Option Explicit
   
Sub Sample()
    Dim curWs As Worksheet
    Dim bsWs As Worksheet
   
    '~~> Change this to the relevant worksheets
    Set curWs = Sheet1
    Set bsWs = Sheet2
   
    Dim lCol As Long
    Dim LRow As Long
   
    '~~> Find the last column in row 7
    lCol = curWs.Cells(7, curWs.Columns.Count).End(xlToLeft).Column
    '~~> Find the last row in Column D
    LRow = bsWs.Range("D" & bsWs.Rows.Count).End(xlUp).Row
   
    '~~> Create our ranges
    Dim rngCur As Range
    Dim rngbs As Range
   
    With curWs
        Set rngCur = .Range(.Cells(7, 14), .Cells(7, lCol))
    End With
   
    Set rngbs = bsWs.Range("D19:D" & LRow)
   
    '~~> Loop and check for duplicates and clear them
    Dim aCell As Range
    For Each aCell In rngCur
        If Application.WorksheetFunction.CountIf(rngbs, aCell.Value2) > 0 Then aCell.ClearContents
    Next aCell
End Sub

---