Paradox then paste into excel

[Steved]

Hello from Steved. Please is the below possible.
The below VBA will allow me to type City and it will
change to 1-City. Can the below be taken one step further,
I copy from Paradox then paste into excel worksheet which
has the below VBA. Is it possible when I paste it from
Paradox into the excel worksheet it will find City then
change it to 1_City.

Thankyou.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
With Target
Select Case UCase(Left(.Value, 4))
Case "CITY": .Value = "1-City"
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub

 

[Steved]

Hello from Steved

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

The above works by moving the cursor over say City it then
changes it to 1-City, so I believe I am getting there,
just need it to to as I paste into Excel workSheet.
Cheers.

 

[Tom Ogilvy]

You don't need a macro

After you paste your data, select your data, then do

Edit=>Replace
Find what: City
Replace With: 1-City

select Find Entire Cells Only

 

[Dave Peterson]

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range

On Error GoTo ws_exit
Application.EnableEvents = False
For Each myCell In Target.Cells
If UCase(Left(myCell.Text, 4)) = "CITY" Then
myCell.Value = "1-City"
End If
Next myCell

ws_exit:
Application.EnableEvents = True
End Sub

 

[Steved]

Gentleman Thankyou.

Tom in my case I have 9 cities with over 4,000 rows
I put in a example to to explain as to what i require as
for the other Ciies I will use Dave VBA and build it.

Thankyou both again, you have saved me a lot of time

Cheers.

 

[Tom Ogilvy]

Probably a mistake to go that way. Using Replace will be much, much faster

Public Sub ChangeAll()
varr = Array("City", "Roskill", "Papakura", "Wiri", _
"North Shore", "Orewa", "Swanson", "Panmure", _
"Waiheke")
varr1 = Array("1-City", "2-Rosk", "3-Papa", "4-Wiri", _
"5-Shor", "7-Swan", "8-Panm", "9-Waih")

for i = lbound(varr) to ubound(varr)
Columns(1).Replace What:=varr(i), _
Replacement = varr1(i), _
Lookat:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

This assumes the content of the cells to be changed contain only one of the
test words. (not every cell in column A).

 

[Steved]

Thanks Tom.

-----Original Message-----
Probably a mistake to go that way. Using Replace will be much, much faster

Public Sub ChangeAll()
varr = Array("City", "Roskill", "Papakura", "Wiri", _
"North Shore", "Orewa", "Swanson", "Panmure", _
"Waiheke")
varr1 = Array("1-City", "2-Rosk", "3-Papa", "4-Wiri", _
"5-Shor", "7-Swan", "8-Panm", "9-Waih")

for i = lbound(varr) to ubound(varr)
Columns(1).Replace What:=varr(i), _
Replacement = varr1(i), _
Lookat:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False
Next
End Sub

This assumes the content of the cells to be changed contain only one of the
test words. (not every cell in column A).

--
Regards,
Tom Ogilvy





.