Everything You Need to Know About Vba
Find and Supervene upon VBA Specific to a sail, column and range
- theguy
-
- #1
Does anyone know of some generic VBA code that will perform a find/supervene upon? I demand to search through nigh 10000+ cells (all in one column) for a string of text and replace information technology with another string of text. Each cell contains nearly 12,000+ characters (with spaces), so the contents of each cell are rather large.
I have tried the standard find and replace only that throws upwards an error about formula being too big..essentially considering in that location is too much text in each cell.
I am trying the code below but when I run it, nothing happens. I have inserted the code in the module role of the VBA in excel..
Sub findrep() Dim target, cell As Range Dim i, chiliad As String i = "Text to notice" k = "Text to replace" Set target = Sheets("Sheet1").Range(Range("F1"), Range("F65536").End(xlUp)) For Each cell In target If jail cell.Value = i Then jail cell.Value = thousand Next cell End Sub -
- #2
Re: Discover and Replace VBA Specific to a sheet, column and range
Instead of looping through each jail cell, it would be more efficient to utilize the Replace method of the Range object...
Sub findrep() Dim i As Cord Dim thou As String i = "Text to find" k = "Text to supercede" Columns("F").Replace what:=i, replacement:=1000, lookat:=xlPart, MatchCase:=Simulated Terminate SubAlter the parameters, appropriately.
-
- #iii
Re: Find and Replace VBA Specific to a sail, column and range
I'd like to point out the the OP's method is more versatile and seems to work 100% of the time.
The replace method is and so weak, I wouldn't go every bit far to even call it a bandaid. Plus, It does not work hardly at all (Maybe my PC is too fast)
Hither is an instance. Lets say you got reports like
|---------------------------
|Customer | Month | Year |
|------------|-------|-------|
| Bobby | 1 | 2016 |
------------ |-------|-------|
| James | 1 | 2015 |
|------------|------|--------|
| Ann | 1 | 2016 |
------------|-------|-------|
| Dave | 1 | 216 |
------------|-------|-------|
| |MONTH Full |
|------------|-------|-------|
| Bobby | 2 | 2014 |
------------ |-------|-------|
| James | 2 | 2017 |
|------------|------|--------|
| Ann | 2 | 2016 |
------------|-------|-------|
| Dave | two | 201|
------------|-------|-------|
| |Calendar month Full |
------------------------------At present we are recording information for the current agenda yr, then the year should ever exist the current yr. Well, you can't rely on the data entry people to enter data correctly, and why should they? They are basically paid less than a person getting $15 to flip hamburgers.
This is where my job as the developer has problem. I have to use information from their sheets to import into a database to maintain and generate reports. If they exit a bad yr in this spreadsheet, it will mess with the database.
hither is how you would gear up information technology, thanks to the OPs solution:
Sub findrep()
Dim target, cell As Range
Dim i, k As Stringk = rptYear
Set target = Sheets("Sheet1").Range(Range("F2"), Range("F65536").End(xlUp))
For Each jail cell In target
If cell.Value <> rptYear and cell.value <> vbnull So cell.Value = k
Next cell
End SubThe OP method will have longer (not past much, unless yous're on a dying dinosaur PC), but since you now have the selection to conditionally supersede, you can so get everything you lot need to. The Replace method volition only piece of work if you know what you lot're trying to replace, and since there is an infinite corporeality of possibilities to be entered with a keyword, it's impossible to supercede all errors.
I am not an expert with VBA in Excel, if there is an even better way nevertheless, let me know.
-
- #4
Re: Notice and Supplant VBA Specific to a sheet, column and range
Quote
I'd like to bespeak out the the OP'due south method is more versatile and seems to work 100% of the time.
The OP stated it didn't work.Quote
The supercede method is so weak, I wouldn't go every bit far to even call it a bandaid.
What'south weak virtually it?Quote
merely since you now have the selection to conditionally replace
Do you even know how supersede works?It doesn't loop, it can look at part of the cell as opposed to the whole cell, you lot tin can go far example sensitive, you lot can specify searching past rows or columns, you can search formats.
You lot can also write the code to loop through an array of values to search for and replace.
Information technology's very versatile, information technology isn't a bandaid and if it doesn't work, it'south probably coded wrong.
Sub findrep() Dim i As Cord Dim k Every bit String i = "*" thousand = 2016 With Range("F2", Range("F" & Rows.Count).End(xlUp)) .Columns(1).Supersede what:=i, replacement:=thousand, lookat:=xlWhole, MatchCase:=Fake End With End Sub -
- #5
Re: Find and Supercede VBA Specific to a sail, column and range
Quote from skywriter;769692
The OP stated information technology didn't work.
I'm non sure why, could have been failed implementation, equally I took information technology and it pluged into my module nicely
Quote from skywriter;769692
What's weak about it?
Really? My entire post explained why. I'll say the important role again
since you now have the pick to conditionally replace, you can then get everything y'all need to. The Replace method will simply work if you know what you lot're trying to supersede, and since there is an infinite amount of possibilities to be entered with a keyword, it'southward impossible to replace all errors.
Quote from skywriter;769692
Do y'all even know how replace works?
It doesn't loop, information technology can expect at function of the jail cell as opposed to the whole jail cell, you tin can make information technology case sensitive, you lot can specify searching by rows or columns, you can search formats.
I never said it loops. Infact I pointed it out directly. Thus information technology taking less time to utilize. And Yes, I do know how to utilise information technology, but in what I've shown, information technology'due south a poor fix.
Quote from skywriter;769692
It'southward very versatile, it isn't a bandaid and if it doesn't work, it's probably coded wrong.
It's not versatile, and I have already proven that.
Quote from skywriter;769692
Sub findrep() Dim i Equally String Dim grand Equally Cord i = "*" k = 2016 With Range("F2", Range("F" & Rows.Count).End(xlUp)) .Columns(1).Replace what:=i, replacement:=m, lookat:=xlWhole, MatchCase:=Fake Finish With End SubThat is poor code. You're only replacing i. i is i value. In that location tin be an space amount of possibilities to be entered with a keyword
-
- #6
Re: Find and Replace VBA Specific to a sail, column and range
The OP stated he was trying to detect and supervene upon text inside text. He stated in that location is much more text in the cell than what he is looking for.
Please explain to me how your code does that.BTW you can't assign a value to k using m = rptYear as y'all need quotes around information technology. That is unless rptYear is a string, but you didn't define rptYear, nor do I run across you assigning a value to it. If rptYear is a string so what do nosotros need one thousand for, that makes no sense, employ rptYear or assign a value to 1000?
Sub findrep() Dim target, jail cell Every bit Range Dim i, k As Cord grand = rptYear Ready target = Sheets("Sheet1").Range(Range("F2"), Range("F65536").End(xlUp)) For Each cell In target If cell.Value <> rptYear And cell.Value <> vbNull Then jail cell.Value = thou Next jail cell End SubDisplay More than
-
- #7
Re: Find and Replace VBA Specific to a canvas, column and range
I was just cutting and pasting and rewriting using examples hither.
Nosotros don't need k, since I accept a variable in identify to use. The OP was trying to do inline replacing, where I was doing the unabridged cell, everywhere the user entered the wrong data.
-
- #8
Re: Find and Supercede VBA Specific to a sheet, column and range
Quote
so the year should always be the current yr
Quote
That is poor code. Yous're but replacing i. i is 1 value. There can be an infinite amount of possibilities to exist entered with a keyword
Electric current year is always the current year, information technology's only ever one value.
I could only as hands made k = the current yr with some code instead of hard coding it to 2016.
Dim k Equally Long k = Year(Date)
Or I could have put that code into the supersede function without defining a variable, if my hard coding is an example of what you refer to as non versatile.
i simply looks for the non blank cells and replaces whatsoever is in them with thousand, the same as your code, I don't run into where it's poor coding.Quote
since you now have the option to conditionally supervene upon, you can then get everything you lot need to. The Replace method will but work if y'all know what y'all're trying to replace, and since there is an infinite corporeality of possibilities to be entered with a keyword, it's incommunicable to supercede all errors.
You can apply variables and wild cards. You lot can put the values in a range of cells into an array and use that every bit your detect and replace criteria.
I don't know what you mean past supplant all errors, what errors?
-
- #9
Re: Find and Supervene upon VBA Specific to a canvas, column and range
Quote from skywriter;769706
Current year is always the electric current year, it's only ever one value.
I could just every bit easily made grand = the electric current year with some code instead of hard coding information technology to 2016.
Dim thou As Long chiliad = Year(Date)Once again you lot missed my original post. I never said electric current year was more than than one value. I said there is more than one value that needs fixing.
Quote from skywriter;769706
You can use variables and wild cards. You can put the values in a range of cells into an array and employ that as your find and supervene upon criteria.
VBA won't accept my wildcard character *, it throws a syntax error
Quote from skywriter;769706
I don't know what you mean past supersede all errors, what errors?
Is half my original post invisible?
-
- #10
Dear sir
Please review my lawmaking and edit I need become to the next sheet
Code below is WORK or notIndividual Sub SeletReplace()
Worksheets("3G U21").Range("A2:A100000").Select
Selection.Replace what:="U21-", replacement:=" ", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=Faux, _
ReplaceFormat:=FalseWorksheets(ActiveSheet.Index + one).Select
Worksheets("3G U08").Range("A2:A100000").Select
Pick.Replace what:="U08-", replacement:=" ", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=Faux, _
ReplaceFormat:=FalseWorksheets(ActiveSheet.Index + 1).Select
Worksheets("4G L21").Range("A2:A100000").Select
Selection.Replace what:="L21-", replacement:=" ", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=Faux, SearchFormat:=False, _
ReplaceFormat:=FalseMsgBox "DONE"
Terminate SubThanks
lotto Beginner -
- #xi
Dear sir
Please review my code and edit I need go to the next canvas
Lawmaking below is Piece of work or non
[...]
Give thanks you
lotto BeginnerHi, Lotto.
I'm a little confused on what you're asking. I'd like to help, but I don't feel this thread is the appropriate identify. Do you desire to create a new thread, and explicate what your trying to practice?
EDIT: I plugged your code into a new Excel workbook, with 3 generic spreadsheets, all consisting of garbage examination data. Your code worked without whatsoever errors or unwanted outcomes. If you make up one's mind to make a new thread, please explicate what's going incorrect for you, if anything at all.
-
Newly created posts will remain inaccessible for others until canonical by a moderator.
The last answer was more than than 180 days agone, this thread is nearly likely obsolete. It is recommended to create a new thread instead.
Source: https://www.ozgrid.com/forum/index.php?thread/113327-find-and-replace-vba-specific-to-a-sheet-column-and-range/
0 Response to "Everything You Need to Know About Vba"
Post a Comment