Replace C with A when C is followed by a number, but not otherwise?

I have =Income_Stmt!$c$1&Income_Stmt!c32 in a cell.
I want to write a macro that turns column C into column A.
I can't use search and replace because this would result in
=InAome_Stmt!$A$1&InAome_Stmt!A32.

Is there a way to do this?

0
Excel3321 (40)
10/25/2005 5:46:06 PM
excel 39879 articles. 2 followers. Follow

2 Replies
406 Views

Similar Articles

[PageSpeed] 26

Replace !$C with !$A and !c with !a
************
Anne Troy
www.OfficeArticles.com

"Excel@shoenfeltconsulting.com" <jimhome@starplace.com> wrote in message 
news:1130262366.796754.67140@g14g2000cwa.googlegroups.com...
>I have =Income_Stmt!$c$1&Income_Stmt!c32 in a cell.
> I want to write a macro that turns column C into column A.
> I can't use search and replace because this would result in
> =InAome_Stmt!$A$1&InAome_Stmt!A32.
>
> Is there a way to do this?
> 


0
ng1 (1444)
10/25/2005 5:53:16 PM
On 25 Oct 2005 10:46:06 -0700, "Excel@shoenfeltconsulting.com"
<jimhome@starplace.com> wrote:

>I have =Income_Stmt!$c$1&Income_Stmt!c32 in a cell.
>I want to write a macro that turns column C into column A.
>I can't use search and replace because this would result in
>=InAome_Stmt!$A$1&InAome_Stmt!A32.
>
>Is there a way to do this?

for a macro, try this:

=====================
Sub CtoA()
Dim c As Range
Dim Frm As String
Dim pos As Long

For Each c In Selection
    pos = 1
    Frm = c.Formula
    
        Do Until pos = 0
    pos = InStr(pos, Frm, "C")
 
 Select Case Mid(Frm, pos + 1, 1)
    Case 0 To 9, "$"
        Frm = Left(Frm, pos - 1) & Replace(Frm, "C", "A", pos, 1)
        pos = pos + 1
 End Select
 
        Loop
c.Formula = Frm
Next c
End Sub
=======================


--ron
0
ronrosenfeld (3122)
10/25/2005 7:13:23 PM
Reply:

Similar Artilces:

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Incrementing Numbers ?
Hello, I have a column with, e.g., two numbers in it. Say a 2 and the next one down is a 4. How do I select one or both so that I can simply just drag down and have a long column with: 2 4 6 8 10 etc. And, if there are already a lot of numbers in the column, must one delete them first for the scheme to work ? I tried a few methods, but just can't seem to get it to work. Also, couldn't find anything in the HELP menu for this. Thanks, Bob Select both cells then drag the bottom right corner down. The cursor turns into a small black cross when you hover over the bottom right corner ...

Display Serial Number on RMA Closing screen
In doing RMA clsoings, it is helpful to know which Serial Number we select from the RMA line to close and would like to see it display on the form. We had ePartners do a mod for us to see the Serial Number ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

Outlook 2003 : "For Follow Up"
When I flag a message (red, blue, green, yellow, etc) in Outlook 2003 is shows up in the "For Follow Up" default Serach Folder. However, if this same message is moved to the "Deleted Items" folder, it no longer shows up in the search. I have verified that "Deleted Items" is one of the folders that is searched by the "For Follow Up" search.... Any ideas? thanks, Jim Stalder Maybe there's some internal logic that says "Hey, this is a DELETED ITEM, why would I want to followup on it". I'll double check here but I wouldn't use th...

Finding two numbers simultaneously.
I select a value (1st number), then I need to find a second value located 5, 10 and 15 readings previous. The information is 90 rows long and 5 columns wide. Because these values don't always show up at 5, 10 and 15 readings apart, I have to keep looking until they do. The readings don't need to show up together in a group. I need one result for 5, then another for 10 and another for 15. I've been using conditional formatting to highlight the two numbers in the range of B2:F91 and counting out manually when the readings show up togetherin the 5, 10 and 15 spans. Readi...

I cannot follow links in Word 2007
I have just upgraded to Office 2007. I am enrolled online in a couple of classes. One of my classes, requires me to download Word Documents, and do assigments. In one of the documents, there are Hyperlinks to websites. Everytime I try to follow the hyperlink, I get an error message that says: "This Operation has been canceled, due to restrictions on this computer. Pleas Contatc your system administrator." I am my system's administrator, and have no idea why this is now working. Please Help. Hi Jbulger, Your PC's firewall may be causing this behaviour. -- ...

Payroll Batch Number
Is it possible to obtain the batch number of a transaction after it has been posted? I have been asked to create a report for our payroll department that lists the batch number in addition to the transaction detail that is contained in UPR30300. Thank you, Jeff Majchrzak No, the batch number is not available for reporting. -- Charles Allen, MVP "Jeff Majchrzak" wrote: > Is it possible to obtain the batch number of a transaction after it has been > posted? I have been asked to create a report for our payroll department that > lists the bat...

Page Number Watermark in Page Break Preview #2
It's making me crazy to. Does anyone have an answer? Pamela What's the question? Gord Dibben Excel MVP On Wed, 18 Feb 2004 10:16:05 -0800, Pamela <anonymous@discussions.microsoft.com> wrote: >It's making me crazy to. Does anyone have an answer? I don't understand this question? >-----Original Message----- >It's making me crazy to. Does anyone have an answer? >. > ...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Serial number in Queries
I am just wondering how to make the serial number through query, and it's not related with any data or table Means once I run query there is data will be show and I want to be serial number shows in the first of each raw. Ken Create a table that has the fields matching your query, plus an AutoNumber. Turn it into an Append query, and append the data to the table. The AutoNumber field gives you the sequence. If you need to programmatically reset the seed of the AutoNumber after you delete the data, see: http://allenbrowne.com/func-ADOX.html#ResetSeed -- Allen Browne - Microsof...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Number format 01-21-10
How do I format a number to show in thousands with comma seperator. For example I would like to show 1050040 as 1,050. Thanks for your assistance. IN EXCEL 2007 take the following action:- Highlight the cell(s) you wnat to change - right hand click and select Format Cells (window of same name should launch) - Number tab - click in the box called:- Use 1000 Separator (,) Hit OK If my comments have helped please hit Yes. Thanks. "Jamal" wrote: > How do I format a number to show in thousands with comma seperator. For > example I woul...

Real Programmers (TM) use MSFT C# not Linux languages (sez an expert)
Don't take my word for it, see what this nationally syndicated author and computer programming guru says... and note the reader's comment at the very end...about Linux...I think he's talking about Linux. Anyway the takeaway executive summary of this article is simple: C# rulz. RL http://programmingzen.com/2010/06/23/how-microsoft-is-changing-the-programm= ing-world/ How Microsoft is changing the programming world Posted on Jun 23rd, 2010 in .NET, Programming Languages | 47 comments Several years ago I knew a programmer, we=92ll call him Joe, who fancied himsel...

breaking up a phone number
I need to break up a phone number into three different columns. I have column A that contains the phone number xxx-xxx-xxxx, I need to split that up into three different columns. Column B would equal the area code... column C would equal the Prefix.. and column D would equal the digits.. any suggestions on how to accomplish this? thanks -- maximus73 ------------------------------------------------------------------------ maximus73's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25877 View this thread: http://www.excelforum.com/showthread.php?threadid=392596 ...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Numbering for column
Hi, Good Day! I seldom used microsoft excel but sometimes i do used it for some simple calculation. When I just opened an excel spreadsheet, the numbering of column is in number format(1, 2, 3...) and not in alphabet format(A, B, C...) May I know how can i reset it back to alphabet? Thanks in advanced. rgds, Phoebe Tools|Options|General|Uncheck R1C1 Reference Style (this is picked up from the first workbook you open in that session. So make sure you save your workbook after the change--and make a note of how you fixed it. It can happen again if the first workbook opened has R1C1 refe...

Loss of XPath Navigation in DOM after XSL in C#
Hi I am using C# and performing an XSL translation on a DOM like so XslTransform oXSL = new XslTransform(); oXSL.Load(Filename); oXMLOut=new XmlDocument() oXMLOut.Load(oXSL.Transform(oXMLIn,null)) where oXMLIn is an input parameter All seems well. The translation works and looking at oXMLOut.InnerXml I have what I want The problem is that I cannot navigate this new DOM using XPath. Is there some property of this new DOM that I must set? This has not been required where I have used XPath previosuly but this is the only time I want to use XPath on a new DOM resulting from XslTransform Did ...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Inventory item numbers
There are quantities for existing item numbers. What is the best method to move these balances to new item numbers: 1) Buy item number modifier from Professional Tools or 2) Use decrease adjustments to reduce the existing balances to zero, then post increase adjustments to the new item numbers. Jackie, Is there any reason that you would want to keep the history of what happened under the old number vs. the new one? If so, then go with option 2. Will the new item be set up differently than the old one? If so, most likely you'll want option 2 (this will be true for most 'major...

Numbering Add-In #2
I have a purchase order template I created with Excel 97 which includes an automatic counter field to give each PO its unique ID number. When I try to open this template in XP, I am told that I need to install the numbering add-in into the Library. However, I cannot find this add-in amongst those available in Excel. I have had no luck trying to source it on the Microsoft website, find reference to this specific add-in in Offline or Online help etc. WHERE IS IT???? This add-in is no longer part of Office: http://support.microsoft.com/default.aspx?scid=kb;en-us;288118&Product=xlw ...

Group header page numbering
Hello All Access Guru out there Would like to have some guide / code / example on how to reset a page numbering to 1 of each group? ie Total pages = 6 Group Pages Site 1 1 / 1 Site 2 1 / 1 Site 3 1 / 2 Site 3 2 / 2 Site 4 1 / 1 Site 5 1 / 1 thaks in advance Hi, Try this (But it's in french) http://officesystemaccess.seneque.net/ex_pagination_groupe.htm TopJB PWYS a �crit : > Hello All Access Guru out there > > Would like to have some guide / code / example on how to reset a page > numbering to 1 of each group?...