Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Copying or deleting row based on value found in other ws
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 50
Default Copying or deleting row based on value found in other ws
I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Copying or deleting row based on value found in other ws
See if this code helps you find your problems. I dont know if you have
sheet1 - sheet7 defined. they should be
sheets("Sheet1")
to
sheets("Sheet7")

Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
Set c = Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue)
If c Is Nothing Then
ufErrorHandler.Show
Else
For i = 1 To 7
Sheets("sheet" & i).Columns(1). _
c.Value = tbChangeProjectNumberTo.Value
Next i

If CStr(Left(c.Value, 1) = "P") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then

' Activate Sheet8
With Sheets("Sheet8")
' Determine the next empty row
NextRow = .Range("A1").End(xlDown).Row + 1
' Transfer to Sheet8(Experience List)
.Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Set c1 = Sheets("Sheet7").Columns(1). _
Find(tbExistingProjectNumber.Value)
.Cells(NextRow, 2) = c1.Offset(0, 3).Value
.Cells(NextRow, 3) = c1.Offset(0, 8).Value
End With
End If

If CStr(Left(c.Value, 1) = "B") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then

Set c2 = Sheets("Sheet9").Columns(1). _
Find(tbExistingProjectNumber.Value).Value
c2.Value = tbChangeProjectNumberTo.Value

End If
End If
End Sub


"aintlifegrand79" wrote:

I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Copying or deleting row based on value found in other ws
I found a couple of more errors. Try this code instead

Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler

For i = 1 To 7
Set c = Sheets("Sheet1").Columns(1).Find(tbExistingProject Number.Value)
If c Is Nothing Then
ufErrorHandler.Show
Else

Sheets("sheet" & i).Columns(1). _
c.Value = tbChangeProjectNumberTo.Value

If CStr(Left(c.Value, 1) = "P") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then

' Activate Sheet8
With Sheets("Sheet8")
' Determine the next empty row
NextRow = .Range("A1").End(xlDown).Row + 1
' Transfer to Sheet8(Experience List)
.Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Set c1 = Sheets("Sheet7").Columns(1). _
Find(tbExistingProjectNumber.Value)
.Cells(NextRow, 2) = c1.Offset(0, 3).Value
.Cells(NextRow, 3) = c1.Offset(0, 8).Value
End With
End If

If CStr(Left(c.Value, 1) = "B") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then

Set c2 = Sheets("Sheet9").Columns(1). _
Find(tbExistingProjectNumber.Value).Value
c2.Value = tbChangeProjectNumberTo.Value
End If
End If
Next i
End Sub


"aintlifegrand79" wrote:

I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Copying or deleting row based on value found in other ws
I think in you find stgatements you need xlpart (NOT XLWHOLE). I made the
modifications below.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler

For i = 1 To 7
Set c =
Sheets("Sheet1").Columns(1).Find(tbExistingProject Number.Value, _
LookIn:=xlvlaues, lookat:=xlPart)
If c Is Nothing Then
ufErrorHandler.Show
Else

Sheets("sheet" & i).Columns(1). _
c.Value = tbChangeProjectNumberTo.Value

If CStr(Left(c.Value, 1) = "P") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then

' Activate Sheet8
With Sheets("Sheet8")
' Determine the next empty row
NextRow = .Range("A1").End(xlDown).Row + 1
' Transfer to Sheet8(Experience List)
.Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Set c1 = Sheets("Sheet7").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlvlaues, lookat:=xlPart)
.Cells(NextRow, 2) = c1.Offset(0, 3).Value
.Cells(NextRow, 3) = c1.Offset(0, 8).Value
End With
End If

If CStr(Left(c.Value, 1) = "B") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then

Set c2 = Sheets("Sheet9").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlvlaues, lookat:=xlPart)
c2.Value = tbChangeProjectNumberTo.Value
End If
End If
Next i
End Sub


"aintlifegrand79" wrote:

I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Copying or deleting row based on value found in other ws
Sorry! I made a couple of typos. this should be my final change. Again
sorry! this code is very hard to follow.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler

For i = 1 To 7
Set c = Sheets("Sheet" &
i).Columns(1).Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
If c Is Nothing Then
ufErrorHandler.Show
Else
c.Value = tbChangeProjectNumberTo.Value

If CStr(Left(c.Value, 1) = "P") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then

' Activate Sheet8
With Sheets("Sheet8")
' Determine the next empty row
NextRow = .Range("A1").End(xlDown).Row + 1
' Transfer to Sheet8(Experience List)
.Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Set c1 = Sheets("Sheet7").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
.Cells(NextRow, 2) = c1.Offset(0, 3).Value
.Cells(NextRow, 3) = c1.Offset(0, 8).Value
End With
End If

If CStr(Left(c.Value, 1) = "B") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then

Set c2 = Sheets("Sheet9").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
c2.Value = tbChangeProjectNumberTo.Value
End If
End If
Next i
End Sub


"aintlifegrand79" wrote:

I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 50
Default Copying or deleting row based on value found in other ws
Joel thanks for your help but when I tried the code it didn't work and I
messed with it for a while and still couldn't get it to work. I know my code
is a bit hard to follow and for that i am sorry but I really appreciate your
help.

"Joel" wrote:

Sorry! I made a couple of typos. this should be my final change. Again
sorry! this code is very hard to follow.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler

For i = 1 To 7
Set c = Sheets("Sheet" &
i).Columns(1).Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
If c Is Nothing Then
ufErrorHandler.Show
Else
c.Value = tbChangeProjectNumberTo.Value

If CStr(Left(c.Value, 1) = "P") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then

' Activate Sheet8
With Sheets("Sheet8")
' Determine the next empty row
NextRow = .Range("A1").End(xlDown).Row + 1
' Transfer to Sheet8(Experience List)
.Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Set c1 = Sheets("Sheet7").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
.Cells(NextRow, 2) = c1.Offset(0, 3).Value
.Cells(NextRow, 3) = c1.Offset(0, 8).Value
End With
End If

If CStr(Left(c.Value, 1) = "B") And _
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then

Set c2 = Sheets("Sheet9").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
c2.Value = tbChangeProjectNumberTo.Value
End If
End If
Next i
End Sub


"aintlifegrand79" wrote:

I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 50
Default Copying or deleting row based on value found in other ws
The debugger always highlights this portion of the code:
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then

I don't know if that will help at all but it seems to be where my code is
some how tripping up.

"aintlifegrand79" wrote:

I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Copying or deleting row based on value found in other ws
I copied your original code which had the CSTR() function. this is not
needed in the code. The problem is CSTR want to see a number. If you have a
P or E in front of the number CSTR will err.

Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
Set tbExistingProjectNumber = Range("A1")
Set tbChangeProjectNumberTo = Range("A1")
For i = 1 To 7
Set c = Sheets("Sheet" & _
i).Columns(1).Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
If c Is Nothing Then
ufErrorHandler.Show
Else
c.Value = tbChangeProjectNumberTo.Value

If Left(c.Value, 1) = "P" And _
Left(tbChangeProjectNumberTo.Value, 1) = "E" Then

' Activate Sheet8
With Sheets("Sheet8")
' Determine the next empty row
NextRow = .Range("A1").End(xlDown).Row + 1
' Transfer to Sheet8(Experience List)
.Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Set c1 = Sheets("Sheet7").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
.Cells(NextRow, 2) = c1.Offset(0, 3).Value
.Cells(NextRow, 3) = c1.Offset(0, 8).Value
End With
End If

If Left(c.Value, 1) = "B" And _
Left(tbChangeProjectNumberTo.Value, 1) = "P" Then

Set c2 = Sheets("Sheet9").Columns(1). _
Find(tbExistingProjectNumber.Value, _
LookIn:=xlValues, lookat:=xlPart)
c2.Value = tbChangeProjectNumberTo.Value
End If
End If
Next i
End Sub


"aintlifegrand79" wrote:

The debugger always highlights this portion of the code:
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then

I don't know if that will help at all but it seems to be where my code is
some how tripping up.

"aintlifegrand79" wrote:

I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a cell based on a keyword it found in another ghobbit Excel Programming 12 May 16th 06 04:47 AM
Populate a cell based on a keyword it found in another ghobbit[_2_] Excel Programming 0 May 11th 06 06:45 PM
Copying whole rows to different sheet once found Gordy w/Hi Expectations Excel Discussion (Misc queries) 8 October 31st 05 01:00 PM
Deleting a row containing a found cell Alan Excel Programming 6 May 16th 04 02:43 PM
Hi-light area based on name found in column A Annette[_3_] Excel Programming 10 April 1st 04 11:05 PM


All times are GMT +1. The time now is 10:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement