Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Delete a specified value IF found in cell in ANY Sheets in a WorkBook
you don't specify where the listbox was created, form forms toolbar or control
toolbox. i assumed the forms toolbar. Sub Macro3() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets If wks.Range("J59").Value = Worksheets("sheet1").Shapes("List Box 4").Value Then wks.Range("J59").Value = "" End If Next wks End Sub -- Gary "Corey" wrote in message ... I have numerous sheets in a workbook, and have sometimes a numerical value in Cell J59. I have some code that does some other work for me, but now i need to add a piece of VBA that will look through ALL sheets int he workbook to see IF Listbox4.value is in Cell J59. If it is then Delete the value, if not found then do nothing. I am trying this to no avail: =========================== Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets On Error Resume Next If wks.Range("J59").Value = ListBox4.Value Then wks.Range("J59").Value = "" End If Next wks =========================== ctm.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Delete a specified value IF found in cell in ANY Sheets in a WorkBook
I have numerous sheets in a workbook, and have sometimes a numerical value in Cell J59.
I have some code that does some other work for me, but now i need to add a piece of VBA that will look through ALL sheets int he workbook to see IF Listbox4.value is in Cell J59. If it is then Delete the value, if not found then do nothing. I am trying this to no avail: =========================== Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets On Error Resume Next If wks.Range("J59").Value = ListBox4.Value Then wks.Range("J59").Value = "" End If Next wks =========================== ctm.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Delete a specified value IF found in cell in ANY Sheets in a WorkBook
Here is my entire code thus far:
Private Sub CommandButton3_Click() Application.ScreenUpdating = False If ListBox4.Value < "" Then If MsgBox("Are you Sure you want to Permanently DELETE" & vbCrLf & vbCrLf & vbTab & "No. " & vbTab & UserForm3.ListBox4.Value & vbCrLf & vbCrLf & " Once DELETED it can NOT be Reversed ?", vbYesNo, "....") = vbNo Then Exit Sub Else Dim rngFound As Range On Error Resume Next Sheets("Data").Visible = True Sheets("Data").Unprotect With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=ListBox4.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) rngFound.EntireRow.Delete xlShiftUp Sheets("Data").protect Sheets("Data").Visible = False Call finder End With End If Else MsgBox "You DID NOT Select a Number from the List the be Deleted !!", vbInformation, "...." Exit Sub End If ' Find Listbox4.value in Range("J59") in EACH Sheet Except for Sheets("Enter-Exit page") <===== Sheets("Enter-Exit Page").Select MsgBox "No. " & ListBox4.Value & " has been Removed.", vbInformation, "...." Unload Me Application.ScreenUpdating = True End Sub "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... you don't specify where the listbox was created, form forms toolbar or control toolbox. i assumed the forms toolbar. Sub Macro3() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets If wks.Range("J59").Value = Worksheets("sheet1").Shapes("List Box 4").Value Then wks.Range("J59").Value = "" End If Next wks End Sub -- Gary "Corey" wrote in message ... I have numerous sheets in a workbook, and have sometimes a numerical value in Cell J59. I have some code that does some other work for me, but now i need to add a piece of VBA that will look through ALL sheets int he workbook to see IF Listbox4.value is in Cell J59. If it is then Delete the value, if not found then do nothing. I am trying this to no avail: =========================== Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets On Error Resume Next If wks.Range("J59").Value = ListBox4.Value Then wks.Range("J59").Value = "" End If Next wks =========================== ctm.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Duplicates Across Two Sheets, Delete Everything else. | Excel Worksheet Functions | |||
Delete value if found in cell. | Excel Programming | |||
Delete a cell when found | Excel Programming | |||
Find text in another workbook and paste if found match - VBA | Excel Discussion (Misc queries) | |||
How to delete values of a cell if it is found in another coloumn | Excel Worksheet Functions |