More Efficient Alternative To Find-Replace Loop?
Feb 1, 2010
I was tasked with looking through a 10,000 row by 20 column spreadsheet for 628 different ID Codes and replacing them with their Descriptions. The ID Codes could appear individually in any of these cells (200,000 cells!).
The list of ID's and Descriptions hardly ever changes, so I decided to create the two-dimensional array as part of the find-replace macro shown below (only a few lines of each dimension are shown).
My question isn't related to that (although if you can suggest a better alternative please do). The main "work" of the macro is the loop at the end of the array declarations, which essentially loops through all 628 ID codes in array dimension 1 and does a Find-ReplaceAll with its corresponding Description from array dimesion 2.
This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop, but I was hoping one of the gurus might suggest an even better method than 628 loop iterations. If not, so be it, the end users will appreciate what I've done and then have to find something to do with their "free time."
View 5 Replies
ADVERTISEMENT
Jan 25, 2007
I have a sheet with data on it. In column 5 I have adate range and I would like to delete and values which have "01/01/1900". Currently I have created a loop but for some reason it does nothing. I can use the find option and it picks up the cells with the ones on. For some reason it seems that it cant find them cells.
The code is as follows
Sub jdate()
rowcn = 8
target_sheet = "Status Report"
Do
'status_flag = 0
If Sheets(target_sheet).Cells(rowcn, 5) = "01/01/1900" Then
Sheets(target_sheet).Cells(rowcn, 5) = ""
End If
rowcn = rowcn + 1
Loop While Sheets(target_sheet).Cells(rowcn, 1) > 0
End Sub
View 9 Replies
View Related
May 25, 2007
I have a question about loops. I've read that loops are slow and there are better ways to achieve the same results but I do not know how to accomplish the same without using a loop. I've attached a code that looks in the first sheet and if in column "f" if criteria is met it will copy the row to a separate sheet. Then is goes to a separate sheet and does the same thing after it skips a row then copy header information. What i'm doing is consolidating multiple sheets to one for prioritizing data. The loop works but I've been going back and trying to make my workbook a little more effecient and really cannot figure out how I would do it differently.
Sub AVAILABLEBILLETS()
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application. ScreenUpdating = False
Dim j As Integer
Dim I As Integer
Dim K As Integer
Dim fillcell As String
View 9 Replies
View Related
Jul 16, 2013
I'm looking for some code to loop through some rows of text and then if the text contains / replace that with a space. My code is below:
Code:
do until intdemandrow = 1
If InStr(Range(cells(intdemandrow, 1).Value, "/") Then
replace(cells(intdemandrow, 1).value, "/", " ")
intdemandrow = intdemandrow - 1
end if
loop
View 4 Replies
View Related
Feb 18, 2009
I am trying to creat a code that with take the value of an active cell and depending on this value will assign a backcolour to a label corresponding to the cell. so far so good.
I then want the code to offset to the next cell in the range read its value and assign a colour to that cells backcolor.
here is an idea.
range("A1").select
for n = 1 to 4
if activecell = "A" then
Label1.BackColor = RGB(0, 0, 0)
else
if activecell = "B" then
Label1.BackColor = RGB(0, 0, 255)
else
if activecell = "C" then
Label1.BackColor = RGB(0, 255, 0)
end if
activecell.offset(0,1).select
next n
Firstly I would like the next loop (refering to A2 in this example) to refer to Label2 not Label1 and so on.
secondly the example would loop through 4 cells in one row (A1:A4) but I would like the code to apply to several rows ie (A1:D4).
so thats 16 cell and 16 labels. I could code this in a very inefficient way but I am sure ther is a simple method.
View 9 Replies
View Related
Sep 5, 2008
Dim myRow As Long
Dim myCol As String
Dim Search As String
Sheets("Master Holdings").Range("H2")
Application.Screenupdating=false
mycol="E"
For myRow=6000 To 1 Step -1
If Activesheet.Cells(myrow, mycol).Value Search Then
Rows(myRow).Delete
End If
Next myRow
Application.ScreenUpdating=True
End Sub
Is there a better way to perform this code?
View 9 Replies
View Related
Mar 22, 2007
I have this excel project that captures identification numbers using a scanner. once the scanner gets the info, it will mark the date/time the person was scanned. my problem is once the info scanned is not on the list, it will continue searching till i do an escape or ctrl-break.
heres the
Private Sub cmdOK_Click()
ActiveWorkbook. Sheets("list 2007").Activate
Range("F25").Select
Do
If ActiveCell.Value <> txtID.Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = txtID.Value
ActiveCell.Offset(0, 2).Value = Now()
Range("F25").Select
txtID.Value = ""
txtID.SetFocus
End Sub
View 7 Replies
View Related
Feb 8, 2013
I have a lot of data with the wrong number format. I want to find all cells with this particular number format and change it to another.
I know I can do this by looping through all the cells on the sheet or by using the find method, but both ways require looping. I am hoping there is a quicker way. I originally thought there was a SpecialCells type that was format conditional (not conditional formatting), but there doesn't seem to be one.
View 8 Replies
View Related
Jun 15, 2006
I have values in row A and I have values in row C. I want to create a loop to look up xxx and replace it with the values consecutively in row C. Look at example for a better Idea. I found a way to find and replace, but I not sure how to use it with qoutes. I was thinking maybe I dont even need row A and just supply a list and excel could have the chunk of data in the code itself.
View 2 Replies
View Related
Feb 5, 2009
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
View 6 Replies
View Related
Jul 2, 2013
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?
View 4 Replies
View Related
Feb 5, 2014
[Code] .....
Trying to repeat a 550 or so character statement with a find/replace however I am getting type mismatch errors. When I use a smaller message in the "replace" it works.
I need it to post a message exactly as long as what I have in there. How do I get it to work?
View 6 Replies
View Related
Apr 20, 2006
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.
View 3 Replies
View Related
Mar 18, 2009
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.
View 2 Replies
View Related
Oct 26, 2009
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.
Example
Find all occurences of And or AND and replace with and
Find all occurences of With or WITH and replace with with.
Find all occurences of Or or OR and replace with or
View 9 Replies
View Related
Apr 7, 2009
I want a marco that can "find & replace" in a column, let's say K. Let's just say I want to find "AAA" and replace "BBB" and "CCC" and replace "DDD", I just need the code so I can change it myself. It will be more than 40+ replacement.
View 9 Replies
View Related
Mar 8, 2007
I have this code on a tab containing a series of dependent dropdowns. There are two dropdowns in each row, Dropdown2 being dependent on the choice in Dropdown1. This code replaces any contents of Dropdown2 with "Select..." if Dropdown1 changes. (Dropdown1 and Dropdown2 are NOT names, those are just the way I refer to them).
Code:.....
View 3 Replies
View Related
Feb 20, 2007
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
View 4 Replies
View Related
Jun 9, 2009
I was wondering if it is possible to do this unique find and replace that is explained below.
I have many columns with data from our database. I have one column that has my html layout in it. I want to find within the html text data and replace it with data from another column for that row.
Example: We have [[manufacturer]] in the html area that we want to replace with data from our column called Manufacturer. So, it will replace the text in the html named [[Manufacturer]] with that rows Manufacturer data we have.
Is that possible to do?
View 14 Replies
View Related
Oct 31, 2011
I have around 10 names which I want to replace with their code names. For Example, "Alan Richard" needs to be replaced with AR001. Problem is I don't know cell range and this name might appear more than once in the data.
View 4 Replies
View Related
Nov 15, 2006
I am trying to find and replace #N/A from a formula (link to another sheet) with nothing in the cell, using VBA.
Using this code it will do the first "find"
Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
ActiveCell.ClearContents
Trying this
Cells.Replace What:="#N/A", Replacement:=" ", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=True, _
ReplaceFormat:=False
LookIn:= is highlighted and I get a "named argument not found"
Don't understand that because LookIn:=xlValues works in the first code.
I did try and place After:=ActiveCell,before this, but that does not work. I Get the same error.
I have more then one cell with the #N/A which I would like to get rid of. I want this to work only on this one sheet. The other sheet that the formula is pulling from does have #N/A, and that is okay, I need it there.
View 9 Replies
View Related
Mar 2, 2007
I have a list of sales associates that are assigned numbers. For example, John Doe is 1022. The data I have to work with only shows his number 1022 but for my reports management wants the name John Doe. I have about 60 sales associates and have a VB code that 'finds and replaces'. The code is huge. It has slowly grown out of hand, with more sales associates added daily. I also have 22 sheets in one workbook with specific columns to search for the numbers to replace with the names.
This is what I would like to do. Have a sheet named associates, as more associates are added I would just have to add their name and number. Have two columns with headers column A - Associates column B Associate #. Name ranges for the two columns and write a code that says look in the sheet associate, at named range 'numbertoname' and if the current sheet 'total sales' I am in has the number 1022 in column C replace it with the name John Doe. It seems like it should be easy but.......I have tried and tried today.
View 9 Replies
View Related
Sep 15, 2006
I am facing a strange problem with Find and Replace function of Excel. I converted a PDF document into a Word document. I copied the tables in the Word document to an Excel document. As the data had many ‘*’ characters, which I did not want, I used Find and Replace function to get rid of them. However, to my surprise, two things were happening.
1.When I tried to replace ‘*’ in a cell with ‘ Construction revenue ********************** ’, it was deleting all the contents of the cell.
2.When I tried to find all the cells with ‘*’ , it was selecting cells even without ‘*’
I tried various things like changing the format, pasting only values..etc, but nothing worked. Finally I had to use the Replace function of VBA to do the task. For further analysis I am attaching my Excel file with that data.
View 3 Replies
View Related
May 15, 2014
In the attached file I want to do a Find/Replace for 599 --> 599.0 and 428 --> 428.0. When I do a F/R for each it tells me it has made 3 and 5 replacements respectively but the data is not changed?
View 7 Replies
View Related
Jan 6, 2014
how to find and replace to prefix and suffix string/text, see this below :
JACK MANHOLD ----------- JACK
SISCA JACK HOLAND ------- JACK
ANDREW JACK ------------- JACK
View 14 Replies
View Related
Aug 16, 2007
I'm doing a search simular to this
=VLOOKUP(D*,Sheet2!A*:B*,2,FALSE)
and I want it replaced with this
=VLOOKUP(D3,Sheet2!A1:B42,2,FALSE)
HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search?
View 13 Replies
View Related
Sep 11, 2008
I'm using Excel 2007 and trying to replace about 2000 commas with full stops. I want to use find and replace but keep getting a message saying that Excel cannot find the data I'm searching for.
Excel help suggests I haven't clicked on "find" before clicking "replace" but I did.
I've tried highlighting the column in which I want to make the replacements, but same message.
Also tried highlighting nothing, same message.
Tried copying and pasting the whole thing into a new file, but same message.
View 14 Replies
View Related
Mar 26, 2009
Assume someone typed the hereunder date ad TEXT.
The Formula bar shows: ="01/01/2009"
The cell presents: 01/01/2009
I need to remove the equation sign as well the two inverted-commas.
I am familiar with the "Text to Column" feature, the use of SUBSTITUTE Function, a short macro and also the Find&Replace is also a good idea but, as far as I understand,
it must be run Twice(!)
I am looking for a way to use Find&Replace in "One! shot" - meaning, to put the TWO different(!) characters [ the '=' and the " ] in the 'find what' window,
leaving the 'replace with' window empty.
I know that something similar to that can be done in a "Word" document - but can it be accomplished in Excel?
View 6 Replies
View Related
May 14, 2009
I have two groups of numbers. The first group is x amount of rows and each row has 6 numbers. The second group is x amount of rows and each row is 7 numbers.
Each number from the first group needs to check to see if it exists in the second group. If it does exist, the cell should change to a highlighted cell, or switch to an 'X' or something else that identifies it has been matched. Note: Numbers may be duplicated in both groups. Is there an easy way to do this, or will it involve programming? I'm attaching an example of the "group" setup.
View 5 Replies
View Related
Jul 19, 2009
I have various formulas (such as: =(D3-$E$3)/$E$3 )
which continues for each row (IE next row 3 is replaced with 4 etc)
I have made som errors when I made the formulas and therefore I have the following questions:
1. Is it possible to find and replace all E's with another letter (F in my case). I haven't been able to resolve this, when it has to be done inside formulas?
The optimal would be if I could set 'Display formulas' under tools, and find and replace in the formulas displayed.
2. Is there a smart way of swapping D3 with E3 for all formulas? (So the result will be: =($E$3-D3)/$E$3 and etc for all formulas)
View 5 Replies
View Related