fixing error in specific code based on run another code

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
thanks but the error still shows despite of it delete the data as in the second macro .
 
Upvote 0
can only suggest you place copy of your workbook with dummy data on file sharing site like dropbox & provide link to it.

Dave
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top