Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding and copying from 1 worksheet to another.
I have a userform uses a button 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a value and copying it to another cell | Excel Worksheet Functions | |||
Finding all hyperlinks in row a, copying to new worksheet | Excel Programming | |||
finding duplicate then copying macro.. | Excel Discussion (Misc queries) | |||
finding names and copying | Excel Programming |