时间戳

首页 > 解决方案 > 使用 VLOOKUP VBA 删除单元格值

excel - 使用 VLOOKUP VBA 删除单元格值

问题描述

我试图删除基于 VLOOKUP 搜索的单元格值，但它向我抛出错误“对象_global 的方法范围失败 1004”当我尝试删除用户名时出现错误。

Private Sub Delete_Click()
Dim sht As String
Dim username as String

'Selects the worksheet based on combobox value.
sht = Me.Combobox1.Value
'Use a VLOOKUP to Search for the username on a worksheet previously selected on
'sht variable. 
username = Application.WorksheetFunction.VLookup(Me.textbox1.Value,
 Worksheets(sht).Range("A:F"), 1, 
False)
'Delete the username found on the VLOOKUP
Range(username).Clear

标签： excelvbacellvlookup

解决方案

你的语法有很多错误。请尝试此代码。我认为它实现了你的想法。

Private Sub Delete_Click()

    Dim Sht             As Worksheet
    Dim UserName        As String
    Dim R               As Long
    
    ' Sets the worksheet based on Combobox value.
    Set Sht = Worksheets(Me.Combobox1.Value)
    UserName = Me.TextBox1.Value

    ' Use MATCH to Search for the username on worksheet Sht
    On Error Resume Next
    R = Application.WorksheetFunction.Match( _
                    UserName, Sht.Columns("A"), 0)
    If Err Then
        MsgBox """" & UserName & """ wasn't found."
    Else
        'Delete the username found by the MATCH function
        Sht.Cells(R, "A").ClearContents
    End If
End Sub

推荐阅读