CODEWITHSUNDEEP
excelvbacellvlookup
Eduardo Urbiola
Eduardo Urbiola

Reputation: 11

Delete cell value with VLOOKUP VBA

I am trying to delete a cell value based on a VLOOKUP search but it's throwing me error " method range of object _global failed 1004 " The error appears when I am trying to delete the username.

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

Upvotes: 0

Views: 494

Answers (1)

Variatus
Variatus

Reputation: 14383

There are many mistakes in your syntax. Please try this code. I think it implements your idea.

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

Upvotes: 1

Related Questions