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

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

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

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

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