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.

Relacing partial text strings
 

Look in the vba help index for REPLACE

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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.

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

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

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

Relacing partial text strings
 

simpler

Sub replaceem()
For Each c In range("yourrange")
c.Replace "cccc", "ccccxx"
c.Replace "dddd", "ddddXX"
'etc
Next c
End Sub

--
Don Guillett
SalesAid Software

"Scoops" wrote in message
oups.com...
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

Relacing partial text strings
 

even simpler
Sub replaceem1()
With range("yourrnge")
.Replace "cccc", "ccccxx"
.Replace "dddd", "ddddXX"
End With
End Sub


--
Don Guillett
SalesAid Software

"Scoops" wrote in message
oups.com...
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

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

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

Relacing partial text strings
 

Thanks for taking the time to explain, thats fantastic and very much
appreciated

Thanks again

Jamie