Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell value and deleting the row with that value in it
I have a userform that uses a textbox (tbExistingProjectNumber) to find an
existing project number and replace it with a new project number from another textbox on the same userform (tbChangeProjectNumberTo). My problem is that if the value in tbExistingProjectNumber starts with a "B" I need it to find the cell with that value on Sheet9 and delete the entire row that had that cell in it. Here is my code, thank you in advance for any help you can give. Private Sub cbChangeButton_Click() ' Activate Sheet9 Sheet9.Activate ' Delete from Sheet9(Budget Proposals) If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") Then Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).SelectRow.Delete Selection.Delete Shift:=x1Up End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell value and deleting the row with that value in it
On Apr 14, 11:41*am, aintlifegrand79
wrote: I have a userform that uses a textbox (tbExistingProjectNumber) to find an existing project number and replace it with a new project number from another textbox on the same userform (tbChangeProjectNumberTo). *My problem is that if the value in tbExistingProjectNumber starts with a "B" I need it to find the cell with that value on Sheet9 and delete the entire row that had that cell in it. *Here is my code, thank you in advance for any help you can give. Private Sub cbChangeButton_Click() ' * Activate Sheet9 * * Sheet9.Activate ' * Delete from Sheet9(Budget Proposals) * * If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") Then * * Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).SelectRow.Delete * * Selection.Delete Shift:=x1Up * * End If Private Sub cbChangeButton_Click() dim strDelete as String Sheet9.Activate ' Delete from Sheet9(Budget Proposals) strDelete = CStr(Left(tbExistingProjectNumber.Value, 1)) If strdelete = "B" Then Sheet9.Columns(1).Find(strDelete).SelectRow Selection.Delete Shift:=xlShiftUp End If End Sub Does that work better? Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell value and deleting the row with that value in i
Thanks for the help, but no that didn't work I got the debugger on the line:
Sheet9.Columns(1).Find(strDelete).SelectRow Any other suggestions? "cht13er" wrote: On Apr 14, 11:41 am, aintlifegrand79 wrote: I have a userform that uses a textbox (tbExistingProjectNumber) to find an existing project number and replace it with a new project number from another textbox on the same userform (tbChangeProjectNumberTo). My problem is that if the value in tbExistingProjectNumber starts with a "B" I need it to find the cell with that value on Sheet9 and delete the entire row that had that cell in it. Here is my code, thank you in advance for any help you can give. Private Sub cbChangeButton_Click() ' Activate Sheet9 Sheet9.Activate ' Delete from Sheet9(Budget Proposals) If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") Then Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).SelectRow.Delete Selection.Delete Shift:=x1Up End If Private Sub cbChangeButton_Click() dim strDelete as String Sheet9.Activate ' Delete from Sheet9(Budget Proposals) strDelete = CStr(Left(tbExistingProjectNumber.Value, 1)) If strdelete = "B" Then Sheet9.Columns(1).Find(strDelete).SelectRow Selection.Delete Shift:=xlShiftUp End If End Sub Does that work better? Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell value and deleting the row with that value in it
Untested, uncompiled.
Option explicit Private Sub cbChangeButton_Click() dim FoundCell as range If ucase(Left(tbExistingProjectNumber.Value, 1)) = "B" Then with Sheet9.Columns(1) 'I'd include all the parms to the .find statement! set foundcell = .cells.Find(tbExistingProjectNumber.Value) end with if foundcell is nothing then 'not found else foundcell.entirerow.delete end if End If End sub aintlifegrand79 wrote: I have a userform that uses a textbox (tbExistingProjectNumber) to find an existing project number and replace it with a new project number from another textbox on the same userform (tbChangeProjectNumberTo). My problem is that if the value in tbExistingProjectNumber starts with a "B" I need it to find the cell with that value on Sheet9 and delete the entire row that had that cell in it. Here is my code, thank you in advance for any help you can give. Private Sub cbChangeButton_Click() ' Activate Sheet9 Sheet9.Activate ' Delete from Sheet9(Budget Proposals) If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") Then Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).SelectRow.Delete Selection.Delete Shift:=x1Up End If -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell value and deleting the row with that value in i
Dave seems to have worked, thank you very much for your help.
"Dave Peterson" wrote: Untested, uncompiled. Option explicit Private Sub cbChangeButton_Click() dim FoundCell as range If ucase(Left(tbExistingProjectNumber.Value, 1)) = "B" Then with Sheet9.Columns(1) 'I'd include all the parms to the .find statement! set foundcell = .cells.Find(tbExistingProjectNumber.Value) end with if foundcell is nothing then 'not found else foundcell.entirerow.delete end if End If End sub aintlifegrand79 wrote: I have a userform that uses a textbox (tbExistingProjectNumber) to find an existing project number and replace it with a new project number from another textbox on the same userform (tbChangeProjectNumberTo). My problem is that if the value in tbExistingProjectNumber starts with a "B" I need it to find the cell with that value on Sheet9 and delete the entire row that had that cell in it. Here is my code, thank you in advance for any help you can give. Private Sub cbChangeButton_Click() ' Activate Sheet9 Sheet9.Activate ' Delete from Sheet9(Budget Proposals) If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") Then Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).SelectRow.Delete Selection.Delete Shift:=x1Up End If -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding duplicate cell and deleting both | Excel Worksheet Functions | |||
Finding a cell match then deleting a row. | Excel Discussion (Misc queries) | |||
Finding a word, then deleting every row above that? | Excel Programming | |||
Finding and Deleting | New Users to Excel | |||
Finding and Deleting last row | Excel Programming |