fixing error in specific code based on run another code

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010
hi experts,
I hope find solution for this error application defined object defined error in this line
VBA Code:
 Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
if I run this code works well
Code:
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("BRANDS")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
 
 Set Rng = ws.Columns(2).Find(Sel, lookat:=xlWhole)
 If Not Rng Is Nothing Then
 Me.TextBox1.Value = ws.Cells(Rng.Row, "A")
 Me.TextBox2.Value = ws.Cells(Rng.Row, "B")
 Me.TextBox3.Value = ws.Cells(Rng.Row, "C")
 Me.TextBox4.Value = ws.Cells(Rng.Row, "D")
 Me.TextBox5.Value = ws.Cells(Rng.Row, "E")
 

 End If
End If
End Sub

but if I run this code will shows error in above code
Code:
Private Sub CommandButton4_Click()
    Dim lReply      As VbMsgBoxResult
    Dim ws          As Worksheet
    Dim strFind     As String
    
    strFind = Me.TextBox2.Value
    
    If Len(strFind) = 0 Then
        MsgBox "PLEASE WRITE  THE CODE", vbExclamation, "Entry Required"
        Me.TextBox2.SetFocus
        Exit Sub
    Else
        lReply = MsgBox("Are you sure you want To delete variation?", vbCritical + vbYesNo, "Confirm")
        If lReply = vbNo Then Exit Sub
    End If
    
    Set ws = Worksheets("brands")
    
    With ws.UsedRange.Columns(2)
        If WorksheetFunction.CountIf(.Cells, strFind) <> 0 Then
            .Cells.Find(What:=strFind, After:=.Cells(1, 1), MatchCase:=True).EntireRow.Delete
        Else
            MsgBox "Could Not find " & strFind & " On " & ws.Name, vbCritical, "Not Found"
            Exit Sub
        End If
    End With
    
    strFind = "VO" & strFind
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(strFind).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'Close Form
    Unload Me
End Sub
any help to fix it please?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,922
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try this update to your code & see if does what you want

VBA Code:
Private Sub CommandButton4_Click()
    Dim lReply      As VbMsgBoxResult
    Dim ws          As Worksheet
    Dim strFind     As String
    Dim FoundCell   As Range
    
    strFind = Me.TextBox2.Value
    
    Set ws = ThisWorkbook.Worksheets("brands")
    
    If Len(strFind) = 0 Then
        MsgBox "PLEASE WRITE  THE CODE", vbExclamation, "Entry Required"
        Me.TextBox2.SetFocus
        Exit Sub
    Else
        Set FoundCell = ws.Columns(2).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
        If Not FoundCell Is Nothing Then
            lReply = MsgBox("Are you sure you want To delete variation?", 276, "Confirm")
            If lReply = vbNo Then Exit Sub
            FoundCell.EntireRow.Delete
        Else
         MsgBox "Could Not find " & strFind & " On " & ws.Name, 48, "Not Found"
        End If
    End If
    
    
    
    strFind = "VO" & strFind
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(strFind).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'Close Form
    Unload Me
End Sub

Dave
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010
thanks but the error still shows despite of it delete the data as in the second macro .
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010

ADVERTISEMENT

the same error and the same line
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,922
Office Version
  1. 2019
Platform
  1. Windows
can only suggest you place copy of your workbook with dummy data on file sharing site like dropbox & provide link to it.

Dave
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,159
Office Version
  1. 2010
sorry my programm is not english , but just select combobox contain search and press button delete it will show the error
sorry about the language
1.xlsm
 

Forum statistics

Threads
1,147,958
Messages
5,744,043
Members
423,841
Latest member
barren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top