Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paradox then paste into excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paradox then paste into excel
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. -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paradox then paste into excel
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 -- Regards, Tom Ogilvy "Steved" wrote in message ... 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. -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paradox then paste into excel
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paradox then paste into excel
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. -----Original Message----- 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 wrote: 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 -- Dave Peterson . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paradox then paste into excel
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 "Steved" wrote in message ... 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. -----Original Message----- 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 wrote: 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 -- Dave Peterson . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paradox then paste into excel
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 "Steved" wrote in message ... 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. -----Original Message----- 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 wrote: 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 -- Dave Peterson . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel to Word : Paste specialPaste Link Excel Chart Obj doesn't | Charts and Charting in Excel | |||
Excel 2007 Pivot Table from a Paradox File | Excel Discussion (Misc queries) | |||
Paste and Paste Special No Longer Working - Excel 2003 | Excel Discussion (Misc queries) | |||
data from Paradox to Excel | Setting up and Configuration of Excel | |||
import paradox database into excel | Excel Discussion (Misc queries) |