Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro that adds formula to replace text
I have a list of Account and phone numbers that looks like this
A B C 1 2302009591 SMITH 2 2302009591 (123)123-0000 3 2302009591 (123)123-1000 4 2302009912 SAMS 5 --------- (123)123-1001 6 2302009949 HUGHES 7 2302009949 (123)123-1999 8 2302009947 WILLIAMS 9 2302009947 (123)123-2000 I want to create a macro that searches for the " ---------" and replaces it with the account number directly above it. For example in this case A5 would be replaced automatically with the results in A4. But since there are multiple occurance of " ---------" and each time I run this report those occurances are on in different rows, I need something that will do this replacement no matter what row this occures on. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro that adds formula to replace text
Hi,
No macro needed assuming you are using 2002 or later: 1. Select column A's data and press Ctrl+F 2. Enter -- in the Find what box and click Find All 3. The first hit is highlighted in the window at the bottom of the dialog box, hold down the Shift key and click the last entry. This should select all the hits in the window (and all the cells in the spreadsheet) 4. Click Close and don't move the cursor. 5. Type = press the Up Arrow key once, press Ctrl+Enter All the numbers should be copied down, over the ----------- 6. Select column A's data and choose Copy, then Edit, Paste Special, Values. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lawribird" wrote: I have a list of Account and phone numbers that looks like this A B C 1 2302009591 SMITH 2 2302009591 (123)123-0000 3 2302009591 (123)123-1000 4 2302009912 SAMS 5 --------- (123)123-1001 6 2302009949 HUGHES 7 2302009949 (123)123-1999 8 2302009947 WILLIAMS 9 2302009947 (123)123-2000 I want to create a macro that searches for the " ---------" and replaces it with the account number directly above it. For example in this case A5 would be replaced automatically with the results in A4. But since there are multiple occurance of " ---------" and each time I run this report those occurances are on in different rows, I need something that will do this replacement no matter what row this occures on. Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro that adds formula to replace text
Sorry, I guess I wasn't clear. I have to set this up in a macro because this
report has to be preparable by those who are less then computer savy. I need to be able to make this function a part of the macro so they don't have to use the "find" feature. Is it possible? "Shane Devenshire" wrote: Hi, No macro needed assuming you are using 2002 or later: 1. Select column A's data and press Ctrl+F 2. Enter -- in the Find what box and click Find All 3. The first hit is highlighted in the window at the bottom of the dialog box, hold down the Shift key and click the last entry. This should select all the hits in the window (and all the cells in the spreadsheet) 4. Click Close and don't move the cursor. 5. Type = press the Up Arrow key once, press Ctrl+Enter All the numbers should be copied down, over the ----------- 6. Select column A's data and choose Copy, then Edit, Paste Special, Values. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lawribird" wrote: I have a list of Account and phone numbers that looks like this A B C 1 2302009591 SMITH 2 2302009591 (123)123-0000 3 2302009591 (123)123-1000 4 2302009912 SAMS 5 --------- (123)123-1001 6 2302009949 HUGHES 7 2302009949 (123)123-1999 8 2302009947 WILLIAMS 9 2302009947 (123)123-2000 I want to create a macro that searches for the " ---------" and replaces it with the account number directly above it. For example in this case A5 would be replaced automatically with the results in A4. But since there are multiple occurance of " ---------" and each time I run this report those occurances are on in different rows, I need something that will do this replacement no matter what row this occures on. Can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro that adds formula to replace text
You can have your macro call this subroutine (or you could incorporate its
code into your macro's code if you want)... Sub ReplaceDashes() Dim R As Range On Error Resume Next Do Set R = Columns("A").Find("---------") If Err.Number Then Err.Clear Exit Do Else R.Value = R.Offset(-1).Value End If Loop End Sub -- Rick (MVP - Excel) "Lawribird" wrote in message ... Sorry, I guess I wasn't clear. I have to set this up in a macro because this report has to be preparable by those who are less then computer savy. I need to be able to make this function a part of the macro so they don't have to use the "find" feature. Is it possible? "Shane Devenshire" wrote: Hi, No macro needed assuming you are using 2002 or later: 1. Select column A's data and press Ctrl+F 2. Enter -- in the Find what box and click Find All 3. The first hit is highlighted in the window at the bottom of the dialog box, hold down the Shift key and click the last entry. This should select all the hits in the window (and all the cells in the spreadsheet) 4. Click Close and don't move the cursor. 5. Type = press the Up Arrow key once, press Ctrl+Enter All the numbers should be copied down, over the ----------- 6. Select column A's data and choose Copy, then Edit, Paste Special, Values. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lawribird" wrote: I have a list of Account and phone numbers that looks like this A B C 1 2302009591 SMITH 2 2302009591 (123)123-0000 3 2302009591 (123)123-1000 4 2302009912 SAMS 5 --------- (123)123-1001 6 2302009949 HUGHES 7 2302009949 (123)123-1999 8 2302009947 WILLIAMS 9 2302009947 (123)123-2000 I want to create a macro that searches for the " ---------" and replaces it with the account number directly above it. For example in this case A5 would be replaced automatically with the results in A4. But since there are multiple occurance of " ---------" and each time I run this report those occurances are on in different rows, I need something that will do this replacement no matter what row this occures on. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add If formula in macro (replace cell text value with numeric valu | Excel Discussion (Misc queries) | |||
Excel Macro does not Replace text in formulas for several workshee | New Users to Excel | |||
Creating a macro to find and replace text | Excel Worksheet Functions | |||
Replace Number with Text using Macro | Excel Discussion (Misc queries) | |||
Macro to delete and replace a text box | Excel Discussion (Misc queries) |