查找函數加上循環給出 1004 錯誤 (Find function couple with loop gives 1004 error)

問題描述

查找函數加上循環給出 1004 錯誤 (Find function couple with loop gives 1004 error)

我目前正在編寫一個代碼，該代碼可以獲取員工編號並將其替換為同一單元格中的姓名。它與一個循環一起工作，在隱藏的表格中尋找匹配項。

問題是由於某種原因，我總是從我的 Find fonction 中得到一個 1004 錯誤，由於某種原因我無法通過所有 google 解決：這是我的代碼：

Sub Employe()
Dim ash As Worksheet
Set ash = ActiveSheet
        Dim i As Integer
        k = 4
        no = 0
        nom = ""
        nos = ""
            For i = 1 To 4 'Goes trough the 4 employee nb input
            ash.Select
            k = k + 1
            no = Cells(k, 3).Value 'Gets the employee number value 

                If no <> "" Then 'look if loop cell is empty
                nos = CStr(no)
                Sheets("Liste Employé").Select 'select the hidden sheets (not hidden as of right now we'll get to that other problem later)

                Dim foundRng As Range
                Set foundRng = Range("A2:A91").Find(nos) 'Go look for the matching number in reference sheets range

                If foundRng Is Nothing Then
                   MsgBox ("Entrer un numéro d'employé valide")

                   Else
                   nom = CStr(foundRng)
                   ash.Select
                   Cells(k, 3).Value = foundRng 'give the value in original sheet

                End If

            End If

        Next
End Sub

問題是鏈接到這一行：

Set foundRng = Range("A2:A91").Find(nos) 

返回1004錯誤。

我認為它與“foundRng”的值 不重置每個循環，但不知道如何修復它。

謝謝，愛

參考解法

方法 1:

There's no need to select a sheet before using Find (so you can safely hide it with no problem)

Set foundRng = Sheets("Liste Employé").Range("A2:A91").Find(what:=nos, lookAt:=xlWhole)

VLookup might be easier:

Sub Employe()
    Dim ash As Worksheet, rngInfo As range, res, c As Range
    Set ash = ActiveSheet
    Dim i As Long
    k = 4

    Set rngInfo = Sheets("Liste Employé").Range("A2:A91")

    For each c in Range("A4:A8").Cells
        If c.Value <> "" Then
            'lookup the name from ColB
            res = application.vlookup(CStr(c.Value), rngInfo, 2, False)
            If Not IsError(res) Then
                c.Value = res
            Else
                MsgBox "Entrer un numéro d'employé valide"
            End If
        End If        



    Next
End Sub

(by Patates Pilées、Tim Williams)

參考文件

1. Find function couple with loop gives 1004 error (CC BY‑SA 2.5/3.0/4.0)