I have two sheets in my workbook: One containing names with French and other European characters, the other containing a list of ISO entities for these characters, like these:
à#224
á#225
â#226
ã#227
ä#228
å#229
æ#230
I need to replace these characters in Sheet 1 with the equivalent ISO entity from Sheet2 and print the value into the same cell in Sheet1.
I am using the following vba code to find numeric codes in a column and replace them with the same code along with descriptive text:
Range("Q:Q").Select
Selection.Replace What:="11", Replacement:="11 - GSA"
Selection.Replace What:="10", Replacement:="10 - NYS OGS"
Selection.Replace What:="1", Replacement:=" 1 - Dist Sale"
Selection.Replace What:="2", Replacement:=" 2 - Direct Sale"
Selection.Replace What:="3", Replacement:=" 3 - No Sale: Warranty Exchange"
The problem as you might already see is that when the replacement of "1" happens, Excel will replace the 1 in the 10 and 11. Of course I want to only replace the number 1. Is there a better way to code this operation?
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?
Is it possible to run a find and replace macro on a protected sheet? I know you can't use the built in Find and Replace but was hoping that I could get around that by using a macro.
View 9 Replies View RelatedI have a spreadsheet which is linked to other spreadsheets. When I use "find and replace" on this spreadsheet, it causes one of my linked cells to move down one row. Why does it do this? This particular cell has nothing to do with the "find and replace" option.
I have no idea how to stop this from moving this cell down.
I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.
View 9 Replies View RelatedI have some records that include greek addresses. The greek alphabet contains letters that are common to the english one (A,B,E,Z,H,I,K,M,N,O,P,T,Y,X) and records with mixed characters will cause me problem. What can I do to find and replace any of those letters with the greek version of the letter.
example:
for a record:
T.È.5123 (this is the Greek abbreviation for P.O.BOX 5123)
I'm looking for a function to replace the first letter T (wether is Greek or English) with the Greek version of it
example 2:
KYMHÓ 6 (kimis is the street, 6 the number)
I should replace English (first four ) letters with the Greek version...
example 3:
BAËÁÙPITOY 14 (valaoritou is the street, 14 the number) ...........
I download several sets of data from an internet site. The only way I can do this is to copy and paste into Excel. The problem is, that all of the cells with numbers in them show up as text, and when I press F2, I find that there is a blank space at the end of each number.
Find and replace, searching for a blank space does not work - Excel "cannot find any data to replace". That function does work on cells in which I have created my own space at the end of a number, but not on these numbers. That leads me to believe that that "blank space" is actually some other invisible character that I need to be searching for. I've attached a bit of my data - maybe somebody else can identify my phantom character.
To get around this, I have been using the formula =(left(A1,len(A1)-1), and that works, it's just that it is a pain to create another table of 20-some columns with this formula, then copy those to values, then for each column convert text to columns to change the text to values, then search and replace for #value! where the original cells were blank.
I need help with a formula (no VBA) that will find all the semi-colons ";" and pound signs "#" in a cell and replace them with this "<br> - ".
Example of Cell A1: ;#Red;#Green;#Blue;#
I started with this:
=REPLACE(MultChoice,SEARCH(";#",MultChoice),1,"<br> - ")
but it only replaces the first character.
I basically want to find and replace certain characters in a formula, but the formula is quite intricate so I will give some background to the problem first:
I have a complex spread sheet which is used to check if a data array matches another data array (both data arrays are different sizes). One array has a set of parameters for a variable and the other array has the variables and the actual values for each variable. I have a section with the following formulas to check if the actual value (C121&D121) matches the parameters ($A$9:$A$108, $B$9:$B$108) & ($C$9:$C$108, $D$9:$D$108)
I have the following formula in cell AB121:
=(C121=INDEX($B$9:$B$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
In cell AC121:
=(D121=INDEX($D$9:$D$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
And have similar formulas from AD121 to AY121. I mention this to point out that excel regards this as an inconsistent formula, I haven't copied and pasted it over, I have had to re-enter it in each cell adjusting the parameter sections.
The problem I have is that currently this is set up to check through a data set and wrap around every 100 rows ('100' Entered in $D$1) but I want to adjust the formula when there is a different amount of rows to check. So basically the only way I can think to be able to do this is to have a formula or something in VBA (which I have very low level experience with) to be able to go into the formula and change certain characters within the formula. For example, if I wanted to check 95 rows instead of 100, it would need to have 95 in $D$1, which I enter in manually, but then how to go in and replace (101:101)-INT(ROW(100:100) with (96:96)-INT(ROW(95:95) in 2 sections in the 1 formula then do the same with the inconsistent formula from AC121:AY121?
I have about 40,000 lines in Column A that have text like so...
13HMPS32TRE600001OP000000601
13L9KUPSOTE600001OP000000601
I need a formula to change all TR and OT to characters QT. I did a find and replace but its changing other columns of text that I don't need changed.
I have list of email ids and some of which contains accented characters.I need to replace these accented characters with regular character.
Example:
rian.pieda@intt.com.cn
ario.sancez@acca.com
i'm looking for macro for find and replace characters in a locked sheet, if a cell contains for example Monday (First day of week)
I want with a command or macro to change the (First day of week) in (Top of the Week) like this Monday (Top of the Week)
In a sheet (4) I have a pivot refreshed by a VBA macro code. Since I have defined a Chart on this pivot, I need to copy this pivot in a new sheet to avoid to reduce rows using a filter of the Chart. I copy this pivot using this VBA code
Sheet4.Select
Sheet4.Copy After:=Sheet2
I have e new sheet with a number Sheet(n) not equal to maximum sheet number +1
For a new run of the VBA macro I need to delete this sheet
Sheets(n).Select where (n) is not = maximum sheet number +1
ActiveWindow.SelectedSheets.Delete
Then I need to avoid the message box where I need to confirm to delete the sheet because the sheet is not empty.
Is there a solution to copy a pivot in a new sheet replacing the previous pivot and vithout changing the number of the sheet?
Every day for each date of month I have to copy data from mails to my workbook and a last sheet named report in which I need last row value from 7 or 8 sheets for this I have developed following codes which seems to me very long procduer.
Code: ....
Is there a way to Clear all code of a particular Sheet Module in Excel VBA?
Or is there some codes to copy a sheet without its code (In Sheets(1).Copy)?
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
Working with a macro enabled workbook that has many sheets what would be a good code that would save Sheet1 into a new MACRO enabled workbook so that the 'Buttons' (which are linked to macros) on this specific sheet will be the only macros that will be copied to the new workbook as opposed to the rest of the macros that are found in different sheets?
Here is what I g=have:
[Code] ......
I am trying to look up a numerical value in Sheet 1 Cell:F7 and find that value in Sheet 2 Column B, then offset 29 columns in the row where the value was found. The number will not have any duplicates within Sheet 2.
View 8 Replies View RelatedI want to do is add data into Sheet A each day then press a button which will copy the data in a range and paste it into Sheet B, but I want to create a list of all data so I need it to find the next empty cell and start the paste from there (if that makes sense).
I want it to create a data base on one sheet from a daily import, I have a code to copy one cell to next empty cell but dont know how to duplicate it to a range.
I hope I have given you enough info this is what I have done so far
Public Sub CopyData()
Dim ws As Worksheet, bi As Byte, vData(1 To 1)
Set ws = Sheets("A")
For bi = 1 To 1
vData(bi) = Application.Choose(bi, ws.Range("A1"))
[Code]....
I am using Excel 2003, Windowx XP Professional, and Internet Explorer 7
In one Excel 3003 example workbook sheet1 contains data copied in from Internet Explorer 7 that keeps changing position on the sheet. From a cell, EG C5, in sheet2 I want to use functions to find a name in Sheet1 and pull the value on the same row say seven columns to the right back into sheet2 cell C5.
My knowledge of Excel is very limited but this would seem to be a simple common thing to have to do, but have been unable figure it out. If this has been answered elsewhere I appologise, this is my first time to this forum which I found with google.
I found another thread Find And Replace Vba." I have looked and looked but can not find or figure out how, or what, to change to search formulas instead of the calculated value of the cell. I am writing code that will copy 2 sheets to new sheets and then rename the new sheets. Sheet1 and Sheet2 are the original sheets with Sheet2 having formulas that reference cells in Sheet1. I am creating new Sheet3 from Sheet1 and new Sheet4 from Sheet2 and wanto to find and replace all references to Sheet1 in Sheet4 to reference Sheet3 instead.
View 4 Replies View RelatedI create a monthly excel sheet regarding customer & their payment
like this..
Suppose..
Month January 2007
Customer A 2000
Customer B 2000
Cust C 3000
Feb
Cust D 3000
Cust E 4000
Cust A 2000
Mar
Cust B 2000
Cust A 2000
Cust G 4000
Now, I want to do somthing like that If a customers Name repeat in another month, I mean Cust. A name has been already entered in Month January & if it comes again in february then it should be automatically replaced or highlight with red line Or something like that.
Switched to Excel after using OpenOffice and I'm stuck on knowing what an old a 'find and replace' formula would be in Excel. It would remove a specific character (or word) ONLY if it was the last characters in a cell.
The old find and replace for open office:
Find: (.*)/$
Replace: $1
It's not that important now to delete a word, mainly the last slash '/' ONLY if it's the last character e.g. this data has 2 rows with a '/' as the last character
website.com/page
website.com/page/
website.com/page/page/
Running my old find and replace formula would remove the last slashes, but leave the others
website.com/page
website.com/page
website.com/page/page
Need simple replacement to the find and replace but a formula is also right.
Attached is an excel file that contains all possible values in the first column (9238 of them) and a comma separated list of a subset of those values in the second column. I need to find a way to replace the smallest value with 1, the next smallest value with 2, ... , and the largest value with 9238 in every instance. In other words, I need their rank value.
OhioBlockGroup.xlsm‎
What I a trying to do is I have a sheet with 3 pages and am using excel auto complete to change data in my cells to save typing out every time. for example if I type 22.. in sheet 1 cell a1 and tab away, it replaces it with an address and post code stored in auto complete. is it possible to store my info in another sheet, if it matches the 22.. in say sheet 3 replace it with the text in the next cell to the right
View 5 Replies View RelatedQuestion: I have two sheets named Sheet1 and Sheet2. Sheets looks like this:
Sheet1
A B
1 John Smith
2 Jane Doe
3 Joe Smo
4 Jackie Chan
Sheet2
A
John Smith
Joe Smo
Joe Smo
Joe Smo
Jane Doe
Bruce Lee
Here is the logic: If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet2 column A cell value to sheet1 column A value (id).
is it possible to replace the value of all the cells which have value =customer in the entire sheet.
for this first i will have to first search for all those columns which have value = customer and then replace them all.
I need the 9th to the 15th character in my cell to be my Sheet name.
This is what I have got but it is coming up an error. It will always be Cell A4 and will always be characters 9 to 15 (These are Numerical the rest of the cell is Letters)
ActiveSheet.Name = Mid(A4, 9, 6)
When trying to import an excel file (.xlsx) from the web to a local excel file on my computer, I have tried to set up a macro to automate this process. I recorded a macro using the "record macro" function, but when trying to import the .xlsx file, the URL was too long and Excel does not allow for such long URLs.
The URL in question is:
[URL]
One solution which I thought of was to shorten the URL with tinyurl.com, which converted the URL to: [URL] . I then used this in the macro recording and it indeed worked. I then tried to substitute all instances of the tinyurl in the VBA code with the long URL, but the
Code:
.SourceDataFile = "http://www.transelectrica.ro/widget/...let_excel=true"
was regarded as an invalid cell/argument.
Is there any way in which I can make this work? I need to have the whole URL, as in the end I plan to have an automated process with the URL changing every 10 seconds with the system clock (and thus refreshing the imported table every 10 seconds), so going through tinyurl every time is not an option.
