vba - 如何修复类型不匹配错误？

Question

嗨，我有以下代码:

Private Sub Search_Click()
Dim Name As String
Dim f As Range
Dim r As Long
Dim ws As Worksheet
Dim s As Integer
Dim FirstAddress As String
Dim str() As String

Name = surname.Value

With ws
Set f = Range("A:A").Find(what:=Name, LookIn:=xlValues)
If Not f Is Nothing Then
With Me
    firstname.Value = f.Offset(0, 1).Value
    tod.Value = f.Offset(0, 2).Value
    program.Value = f.Offset(0, 3).Value
    email.Value = f.Offset(0, 4).Text

    SetCheckBoxes f.Offset(0, 5) '<<< replaces code below

    officenumber.Value = f.Offset(0, 6).Text
    cellnumber.Value = f.Offset(0, 7).Text
    r = f.Row '<<<<<<<<< using this to locate the row of "found"
End With
    findnext
        FirstAddress = f.Address
Do
    s = s + 1
    Set f = Range("A:A").findnext(f)
            Loop While Not f Is Nothing And f.Address <> FirstAddress
    If s > 1 Then
       Select Case MsgBox("There are " & s & " instances of " & Name, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

       Case vbOK
            findnext
       Case vbCancel

       End Select

    End If

Else: MsgBox Name & "Not Listed"

End If

End With

End Sub

我想使用更新按钮:

private Sub update_Click()

Dim Name As String
Dim f As Range
Dim ws As Worksheet

With ws
Set f = .Cells(r, 1)  '<<<<<<<<<<<<< Mismatch type error

    f.Value = surname.Value
    f.Offset(0, 1).Value = firstname.Value
    f.Offset(0, 2).Value = tod.Value
    f.Offset(0, 3).Value = program.Value
    f.Offset(0, 4).Value = email.Value
    f.Offset(0, 5).Value = GetCheckBoxes
    f.Offset(0, 6).Value = officenumber.Value
    f.Offset(0, 7).Value = cellnumber.Value

End With
End Sub

所以我想找到找到的单元格的行并将所有单元格替换为文本框中写入的任何内容(用新信息更新以前的信息) - 但是我在 set f = .cells(r,1) 时遇到错误我可以解决这个问题吗？

Answer 1

尝试使用全局变量r : 而是声明 Dim r As Long在 Private Sub Search_Click() , 使用 Public r As Long在模块最顶部的所有功能之外:

Public r As Long

Public Sub Search_Click()
'your code
End Sub

Public Sub update_Click()
'your code
End Sub

现在，在调用 Search_Click 之后 r将被初始化，然后您可以调用 update_Click .

附言不要忘记删除 Dim r As Long来自 Private Sub Search_Click() .

顺便说一句，在你的 update_Click和 Search_Click未初始化的潜艇 ws变量: Set ws = ThisWorkbook.Worksheets("Sheet1") .添加此行后更改 Range("A:A")至 .Range("A:A")在 Search_Click