Excel vba range replace

VBA Replace Function to Replace Characters in a String

The VBA Tutorials Blog

Introduction — VBA Replace

Use the VBA Replace function to replace a substring of characters in a string with a new string. VBA Replace is similar to the Excel SUBSTITUTE function; both can be used to replace a portion of a string with another.

Example — VBA Replace

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. ItвЂ™s loaded with VBA shortcuts to help you make your own macros like this one — weвЂ™ll send a copy to your email address below.

Tutorial — VBA Replace

This is a very basic macro. I defined my string, then instantly changed it. You likely wonвЂ™t be using your Replace function in this manner. In practice, youвЂ™ll probably be passing a cell, like Range(«a1») . Yep, you can pass cells to the Replace function, too!

Anyway, when the macro finishes, the string str1 will be changed from:

All instances of fish were replaced with the string cat . This is the same way the Excel SUBSTITUTE() function replaces portions of a string.

You can see how a macro like this could be useful for replacing a вЂњ.csvвЂќ with a вЂњ.xlsxвЂќ extension in a list of file names, for example.

Okay, time to talk more about the VBA Replace function. The VBA Replace function requires 3 arguments, but it can accept as many as 6 arguments. IвЂ™ll give you a nice description of each of these arguments, then IвЂ™ll show you a few examples:

VBA Replace Function Arguments

We know how to do a basic find and replace in a string using the Replace VBA function. LetвЂ™s look at a few examples of how the optional Replace arguments can change your results.

Replace first 2 instances in a string

Result: One cat, two cat, red fish, blue fish
Once the VBA Replace function finds the first 2 instances of the word cat , it stops replacing them and the macro ends.

Replace last occurrence of substring in a string

Result: One fish, two fish, red fish, blue cat
This is kind of an interesting one. I use the VBA function strReverse to write the string backward, and then I search for the first instance of the backward string inside the main string. The Replace function is really replacing the first instance of your string, but IвЂ™ve reversed the string so the first instance is really the last instance. It sounds confusing, but itвЂ™s a neat little trick!

Replace starting at the 10th character

Result: two cat, red cat, blue cat
ThatвЂ™s right. When you specify a starting position, Replace truncates the characters from the 1st character to the Nth character you specify. The first 9 characters are missing! HereвЂ™s how you can fix that if you still want all the characters:

Replace starting at the 10th character and keep whole string

Result: One fish, two cat, red cat, blue cat
The first fish remains and the rest are replaced. I retained the first 9 characters by using the VBA Mid function.

ReaderвЂ™s Note: IвЂ™ve created a really awesome user-defined function to help you replace the Nth occurrence of a substring in a string if thatвЂ™s what youвЂ™re searching for!

Application Ideas — VBA Replace

The Replace function of VBA is great for manipulating strings. One great use is to replace date/time stamps, file extensions or personalized greetings for the person logged into a computer. IвЂ™m sure you have other great uses for it and IвЂ™d love to hear about them!

For more VBA tips, techniques, and tactics, join our VBA Insiders email series using the form below. After you do that, share this article on Twitter and Facebook.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

Читать еще: Скачать видео с нета

VBA Excel. Переменная диапазона ячеек (As Range)

Присвоение диапазона ячеек объектной переменной в VBA Excel. Адресация ячеек в переменной диапазона и работа с ними. Определение размера диапазона. Примеры.

Присвоение диапазона ячеек переменной

Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.

Присваивается переменной диапазон ячеек с помощью оператора Set:

В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.

Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.

Адресация ячеек в диапазоне

К ячейкам присвоенного диапазона можно обращаться по их индексам, а также по индексам строк и столбцов, на пересечении которых они находятся.

Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

Обращаться в переменной диапазона можно не только к отдельным ячейкам, но и к части диапазона (поддиапазону), присвоенного переменной, например,

обращение к первой строке присвоенного диапазона размерностью 5х5:

и обращение к первому столбцу присвоенного диапазона размерностью 5х5:

Работа с диапазоном в переменной

Работать с диапазоном в переменной можно точно также, как и с диапазоном на рабочем листе. Все свойства и методы объекта Range действительны и для диапазона, присвоенного переменной. При обращении к ячейке без указания свойства по умолчанию возвращается ее значение. Строки

равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

Важно: если вы планируете работать только со значениями, используйте переменные массивов, код в них работает значительно быстрее.

Преимущество работы с диапазоном ячеек в объектной переменной заключается в том, что все изменения, внесенные в переменной, применяются к диапазону (который присвоен переменной) на рабочем листе.

Пример 1 – работа со значениями

Скопируйте процедуру в программный модуль и запустите ее выполнение.

Обратите внимание, что ячейки диапазона на рабочем листе заполнились так же, как и ячейки в переменной диапазона, что доказывает их непосредственную связь между собой.

Пример 2 – работа с форматами

Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

Опять же, обратите внимание, что все изменения форматов в присвоенном диапазоне отобразились на рабочем листе, несмотря на то, что мы непосредственно с ячейками рабочего листа не работали.

Пример 3 – копирование и вставка диапазона из переменной

Значения ячеек диапазона, присвоенного переменной, передаются в другой диапазон рабочего листа с помощью оператора присваивания.

Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

В примере используется тот же диапазон, что и в первых двух, так как он уже заполнен значениями и форматами.

Find and Replace VBA Specific to a sheet, column and range

Does anyone know of some generic VBA code that will perform a find/replace? I need to search through about 10000+ cells (all in one column) for a string of text and replace it with another string of text. Each cell contains about 12,000+ characters (with spaces), so the contents of each cell are rather large.

I have tried the standard find and replace but that throws up an error about formula being too big..essentially because there 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 part of the VBA in excel..

Sub findrep()
Dim target, cell As Range
Dim i, k As String
i = «Text to find»
k = «Text to replace»
Set target = Sheets(«Sheet1»).Range(Range(«F1»), Range(«F65536»).End(xlUp))
For Each cell In target
If cell.Value = i Then cell.Value = k
Next cell
End Sub

Re: Find and Replace VBA Specific to a sheet, column and range

Instead of looping through each cell, it would be more efficient to use the Replace method of the Range object.

Sub findrep()
Dim i As String
Dim k As String
i = «Text to find»
k = «Text to replace»
Columns(«F»).Replace what:=i, replacement:=k, lookat:=xlPart, MatchCase:=False
End Sub

Change the parameters, accordingly.

Re: Find and Replace VBA Specific to a sheet, 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 so weak, I wouldn’t go as far to even call it a bandaid. Plus, It does not work hardly at all (Maybe my PC is too fast)

Читать еще: Vb net работа с excel

Here is an example. Lets say you got reports like

Now we are recording information for the current calendar year, so the year should always be the current year. 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 programmer has trouble. I have to use data from their sheets to import into a database to maintain and generate reports. If they leave a bad year in this spreadsheet, it will mess with the database.

here is how you would fix it, thanks to the OPs solution:

Sub findrep()
Dim target, cell As Range
Dim i, k As String

k = rptYear
Set target = Sheets(«Sheet1»).Range(Range(«F2»), Range(«F65536»).End(xlUp))
For Each cell In target
If cell.Value <> rptYear and cell.value <> vbnull Then cell.Value = k
Next cell
End Sub

The OP method will take longer (not by much, unless you’re on a dying dinosaur PC), but since you now have the option to conditionally replace, you can then get everything you need to. The Replace method will only work if you know what you’re trying to replace, and since there is an infinite amount of possibilities to be entered with a keyword, it’s impossible to replace all errors.

I am not an expert with VBA in Excel, if there is an even better way yet, let me know.

VBA Replace Function to Replace Characters in a String

The VBA Tutorials Blog

Introduction — VBA Replace

Example — VBA Replace

Make powerful macros with our free VBA Developer Kit

Tutorial — VBA Replace

Anyway, when the macro finishes, the string str1 will be changed from:

All instances of fish were replaced with the string cat . This is the same way the Excel SUBSTITUTE() function replaces portions of a string.

You can see how a macro like this could be useful for replacing a вЂњ.csvвЂќ with a вЂњ.xlsxвЂќ extension in a list of file names, for example.

VBA Replace Function Arguments

We know how to do a basic find and replace in a string using the Replace VBA function. LetвЂ™s look at a few examples of how the optional Replace arguments can change your results.

Replace first 2 instances in a string

Result: One cat, two cat, red fish, blue fish
Once the VBA Replace function finds the first 2 instances of the word cat , it stops replacing them and the macro ends.

Replace last occurrence of substring in a string

Replace starting at the 10th character

Replace starting at the 10th character and keep whole string

Result: One fish, two cat, red cat, blue cat
The first fish remains and the rest are replaced. I retained the first 9 characters by using the VBA Mid function.

Читать еще: Цвета vba excel

ReaderвЂ™s Note: IвЂ™ve created a really awesome user-defined function to help you replace the Nth occurrence of a substring in a string if thatвЂ™s what youвЂ™re searching for!

Application Ideas — VBA Replace

For more VBA tips, techniques, and tactics, join our VBA Insiders email series using the form below. After you do that, share this article on Twitter and Facebook.

Oh, and if you have a question, post it in our VBA Q&A community.

Replace Worksheet Function and Replace in VBA

Ctrl+H Replace shortcut key (#ctrlh)

Select the range of cells to be changed

Use Ctrl+H or the Edit (menu), Replace, then in the dialog

*
Replace With: (leave blank to remove string)
use button: Replace or use button: Replace All

Find What: !
Replace With: enter a space by hitting the spacebar
use button: Replace or use button: Replace All

The problem is the remove the asterisks, and change the exclamation points to spaces.

Use tilde in front of certain characters (question, asterisk, tilde)
— see #wildcards for substitutions for ?, or *, or

Buttons on Replace dialog:
Replace ALL will replace all occurences in all cells
Replace without the all will restrict to all characters in the selected cells, one cell at a time with each invocation starting with the active cell.

SUBSTITUTE Worksheet Function (#substitute)

or the match the macros below replace with spaces and trim the result

Application.SUBSTITUTE in VBA

REPLACE in VBA (#vba)

REPLACE used in a standard macro on Preselected Text Cells (#macro)

Speed and efficiency considerations can be seen in Proper, and other Text changes including use of SpecialCells and selections, more tips on speed can be found in in Slow Response.

The next example is a Change Event macro and because it would apply only to one worksheet a high degree of customization would be explected.

REPLACE used in a CHANGE event macro (#changeevent)

You can force an entry by hitting F2 then Enter.

Use of Fill-Down with Ctrl+D or with the fill handle will not constitute a change event.

Worksheet Event Macros are installed with the worksheet by right-clicking on the sheettab, choose ‘view code’, and then paste in your macro, unlike standard macros which are installed in standard modules, The following four topics were copied from my strings page on 2005-10-09 when this page was created.

Case sensitivity (#sensitivity)

WS Functions: to make something case sensitive as in Data Validation, change the “Allow” dropdown to Custom, then, assuming the active cell is A1, enter =Exact(A1,UPPER(A1)) to ensure uppercase, or =EXACT(A1,LOWER(A1)), to ensure lowercase, and click the “error alert” tab and create an appropriate error message. As mentioned in the table at the top, FIND is case sensitive, and SEARCH is not. SUBSTITUTE(value,to,from) is entirely case sensitive. UPPER, LOWER, and PROPER are not case sensitive but will convert the letter case.

VBA: usage is case sensitive. Application. — invokes an Excel function so it will be case sensitive if the Excel function is case sensitive. To make VBA not case sensitive you can use UCASE or LCASE as in IF UCASE(myvar) = «ABC» THEN .

There are a few VB functions that can be made case insensitive with an operand (InStr, Sort, Replace, Find, Select)
InStr(1, Range(«b2»), «text») as binary comparison (default is 0: vbBinaryCompare)
InStr(1, Range(«b2»), «Text», 1) as textual comparison (1: vbTextCompare)