Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
I am trying to put together a macro to run behind a worksheet that
will preform the following procedures: IF column B contains "PRO", "NOP" or "VAL" at the end of a text string (eg. 2007/4/SH4A/90507/PRO) and Column CW =1, then copy the row to the next availble row down and replace "PRO" with "CONP", "NOP" with "CONN" and "VAL" with "CONV" in the target name (i.e. the pasted row). Then go back and change the original copied names from "PRO" to "VoidP", "NOP" to "VoidN" and "VAL" to "VoidV". Im not sure of the best way to search and replace etxt strings in VB and all attempts to date have been in vein. Any advice on the best way of achieving this would be gratefully appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
On 16 May, 13:05, wrote:
I am trying to put together a macro to run behind a worksheet that will preform the following procedures: IF column B contains "PRO", "NOP" or "VAL" at the end of a text string (eg. 2007/4/SH4A/90507/PRO) and Column CW =1, then copy the row to the next availble row down and replace "PRO" with "CONP", "NOP" with "CONN" and "VAL" with "CONV" in the target name (i.e. the pasted row). Then go back and change the original copied names from "PRO" to "VoidP", "NOP" to "VoidN" and "VAL" to "VoidV". Im not sure of the best way to search and replace etxt strings in VB and all attempts to date have been in vein. Any advice on the best way of achieving this would be gratefully appreciated. Hi Jamie Try this: Sub TextReplace() Dim SrcCell As Range Dim DstCell As Range For Each SrcCell In Range(Cells(1, "B"), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2)) If Cells(SrcCell.Row, "CW").Value = 1 Then Select Case Right(SrcCell, 3) Case "PRO" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Left(SrcCell, Len(SrcCell) - 3) & "CONP" SrcCell = Left(SrcCell, Len(SrcCell) - 3) & "VoidP" Case "NOP" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Left(SrcCell, Len(SrcCell) - 3) & "CONN" SrcCell = Left(SrcCell, Len(SrcCell) - 3) & "VoidN" Case "VAL" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Left(SrcCell, Len(SrcCell) - 3) & "CONV" SrcCell = Left(SrcCell, Len(SrcCell) - 3) & "VoidV" End Select End If Next End Sub Regards Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
On 16 May, 13:05, wrote:
I am trying to put together a macro to run behind a worksheet that will preform the following procedures: IF column B contains "PRO", "NOP" or "VAL" at the end of a text string (eg. 2007/4/SH4A/90507/PRO) and Column CW =1, then copy the row to the next availble row down and replace "PRO" with "CONP", "NOP" with "CONN" and "VAL" with "CONV" in the target name (i.e. the pasted row). Then go back and change the original copied names from "PRO" to "VoidP", "NOP" to "VoidN" and "VAL" to "VoidV". Im not sure of the best way to search and replace etxt strings in VB and all attempts to date have been in vein. Any advice on the best way of achieving this would be gratefully appreciated. Hi Jamie Try this: Sub TextReplace() Dim SrcCell As Range Dim DstCell As Range For Each SrcCell In Range(Cells(1, "B"), Cells(Rows.Count, 2).End(xlUp)) If Cells(SrcCell.Row, "CW").Value = 1 Then Select Case Right(SrcCell, 3) Case "PRO" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Left(SrcCell, Len(SrcCell) - 3) & "CONP" SrcCell = Left(SrcCell, Len(SrcCell) - 3) & "VoidP" Case "NOP" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Left(SrcCell, Len(SrcCell) - 3) & "CONN" SrcCell = Left(SrcCell, Len(SrcCell) - 3) & "VoidN" Case "VAL" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Left(SrcCell, Len(SrcCell) - 3) & "CONV" SrcCell = Left(SrcCell, Len(SrcCell) - 3) & "VoidV" End Select End If Next Set DstCell = Nothing End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
On 16 May, 13:05, wrote:
I am trying to put together a macro to run behind a worksheet that will preform the following procedures: IF column B contains "PRO", "NOP" or "VAL" at the end of a text string (eg. 2007/4/SH4A/90507/PRO) and Column CW =1, then copy the row to the next availble row down and replace "PRO" with "CONP", "NOP" with "CONN" and "VAL" with "CONV" in the target name (i.e. the pasted row). Then go back and change the original copied names from "PRO" to "VoidP", "NOP" to "VoidN" and "VAL" to "VoidV". Im not sure of the best way to search and replace etxt strings in VB and all attempts to date have been in vein. Any advice on the best way of achieving this would be gratefully appreciated. Hi Jamie Try: Sub TextReplace() Dim SrcCell As Range Dim DstCell As Range Application.ScreenUpdating = False For Each SrcCell In Range(Cells(1, "B"), Cells(Rows.Count, 2).End(xlUp)) If Cells(SrcCell.Row, "CW").Value = 1 Then Select Case Right(SrcCell, 3) Case "PRO" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Replace(SrcCell, "PRO", "CONP") SrcCell = Replace(SrcCell, "PRO", "VoidP") Case "NOP" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Replace(SrcCell, "NOP", "CONN") SrcCell = Replace(SrcCell, "NOP", "VoidN") Case "VAL" Set DstCell = Cells(Rows.Count, 2).End(xlUp)(2) SrcCell.EntireRow.Copy Cells(DstCell.Row, 1) DstCell = Replace(SrcCell, "VAL", "CONV") SrcCell = Replace(SrcCell, "VAL", "VoidV") End Select End If Next Application.ScreenUpdating = True Set DstCell = Nothing End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
Thanks scoop that works great. Ive put it in the worksheet code but it
doesnt run automatically. Should it? or is there something further required. Thanks Again Jamie |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
On 16 May, 16:44, wrote:
Thanks scoop that works great. Ive put it in the worksheet code but it doesnt run automatically. Should it? or is there something further required. Thanks Again Jamie Hi Jamie Depends what you want - to manually run the macro, either ToolsMacroMacros, select TextReplace and click Run or Alt+F8, select TextReplace and click Run. To run it manually but more easily, place a command button on the worksheet, right-click it View Code, paste the vba (minus the "Sub TextReplace()" and "End Sub" lines) into the button's click sub; now the macro will run when you click the button. To run it automatically, right-click the worksheet's tabView Code to open the vba editor. In the main window there should be a drop-down list showing "(General)". Change that to "Worksheet". A default Private Sub will be presented; next to the first drop-down list is another saying "SelectionChange", from that list select "Change" to start the required event sub. Now decide when you want the macro to fire, I suspect you may only want it to work when you enter something in column CW so start your macro with: Private Sub Worksheet_Change(ByVal Target As Range) Dim SrcCell As Range Dim DstCell As Range If Target.Column < 101 then Exit Sub '101 is column CW's number Application.ScreenUpdating = False For Each SrcCell In Range(Cells(1, "B"), Cells(Rows.Count, 2).End(xlUp)) .....rest of the code as already posted.... End Sub Now the macro will fire every time you change the worksheet but will only completely execute if you've just changed a cell in column CW. Play around with Target (the changed range) if you want something else. Regards Steve |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relacing partial text strings
Thanks for taking the time to explain, thats fantastic and very much
appreciated Thanks again Jamie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
entering partial text | New Users to Excel | |||
Relacing default Excel message box when user attempts to change locked cell on protected sheet. | Excel Programming | |||
Summing partial strings. | Excel Worksheet Functions | |||
Sumproduct and finding partial strings | Excel Programming |