Home

Excel VBA Substitute

Use Substitutewhen you want to replace specific text in a text string; use Replacewhen you want to replace any text that occurs in a specific location in a text string. Syntax. expression. Substitute(Arg1, Arg2, Arg3, Arg4) expressionA variable that represents a WorksheetFunctionobject. Parameters. Name Excel SUBSTITUTE Function The Excel SUBSTITUTE function replaces characters with another in a specified string. EXCEL VBA EXPLANATION. Example: Excel SUBSTITUTE Functio

Použití s funkce DOSADIT (SUBSTITUTE) REPLACE ve VBA. Přepsat dnes na zítra. Ve větě, Práci udělám dnes. Dim Hodnota As String MsgBox (Práci udělám dnes) Hodnota = Replace(Práci udělám dnes, dnes, zítra) MsgBox (Hodnota) Podrobněji o VBA bude popsáno v samostatném článku Note that it uses the VBA Replace function, so you need to rename your 'Replace` subroutine. Sub ReplaceText () For Each c In Sheets (Sheet1).Range (A1:A629).Cells c = Replace (c.Value, ;) Next c End Sub. Note: This will only work if you have values in the cells, no Formulas Explanation of VBA Replace String: We have seen from the above examples that the Replace function is the replacement of the substitute function of excel. It has three mandatory arguments and other arguments are optional. We can use the optional arguments together or one at a time Úvodem do funkce NAHRADIT. Jak nahradit ve zvoleném textu zadaný řetězec jiným v zadané pozici (pořadí) využitím funkce v Excel, aneb praktické využití funkce NAHRADIT (v angličtině REPLACE), jednak její použití v listu až po využití funkce ve VBA.. Jak na funkci NAHRADIT prakticky od A do Z ukazuji v tomto článku, pro přehlednost je rozdělen na kapitoly The REPLACE function can be used in VBA code in Microsoft Excel. Let's look at some Excel REPLACE function examples and explore how to use the REPLACE function in Excel VBA code: Replace(alphabet, bet, hydro) Result: alphahydro Replace (alphabet, a, e) Result: elphebet Replace(alphabet, a, e, 2) Result: lphebet Replace(alphabet, a, e, 1, 1) Result: elphabe

This example uses the Sub statement to define the name, arguments, and code that form the body of a Sub procedure. ' Sub procedure definition. ' Sub procedure with two arguments. Sub SubComputeArea(Length, TheWidth) Dim Area As Double ' Declare local variable. If Length = 0 Or TheWidth = 0 Then ' If either argument = 0 EXCEL VBA Substitute メソッド 複数の文字列の置換(大量・一括変換・置換リスト) はじめに 今回説明するのは、WorksheetFunction.Substitute メソッドの利用方法を説明いたします Step 1: Go to VBA, and in the Insert menu tab, select Module. Step 2: Now, write a sub-category in the name of a performed function or in any name as per your choice, as shown below. Code: Sub VBA_Replace2 () End Sub. Step 3: Now define a dimension as Long as we will be selecting a complete sentence The SUBSTITUTE function in Excel replaces one or more instances of a given character or text string with a specified character (s). The syntax of the Excel SUBSTITUTE function is as follows: SUBSTITUTE (text, old_text, new_text, [instance_num]) The first three arguments are required and the last one is optional The Excel VBA SUBSTITUTE function is very similar to the Replace Statement. The syntax of both these functions is almost the same, the results they deliver are the same too. The point that is very important while using the Substitute function is that: this function is not readily available in the VBA functions but under the VBA WorksheetFunctions

WorksheetFunction.Substitute method (Excel) Microsoft Doc

学习完 Excel VBA 基础、VBA 变量以及程序结构,在本章《VBA 过程和函数》,将学习 VBA 中的另一个基础又核心的概念,过程(Sub)。. 在今天的这篇文章,先学习 VBA 过程的基础,包括如何写、传入参数、执行和调用过程等 It works similar to the Substitute function in VBA. While dealing with test string or text data values, it is an obvious thing to replace or substitute something with something else, joining two cell data into one or splitting one cell data to multiple things. These are all common tasks we do day in day out at our workplace This tutorial will demonstrate how to use the Find and Replace methods in Excel VBA. VBA Find. Excel has excellent built-in Find and Find & Replace tools. They can be activated with the shortcuts CTRL + F (Find) or CTRL + H (Replace) or through the Ribbon: Home > Editing > Find & Select. By clicking Options, you can see advanced search options: You can easily access both the Find and Replace methods using VBA

Excel SUBSTITUTE Function Excel, VB

DOSADIT SUBSTITUTE textová funkce Excel Školení konzultac

Excel VBA Replace() is a simple yet very useful string function. As the name suggests, Replace() is used to replace a set of characters in a string with a new set of characters. The basic syntax of a VBA Replace function looks like this When you write code in VBA, you can write it in a Sub Procedure, or a Function Procedure. A Function Procedure is able to return a value to your code. This is extremely useful if you want VBA to perform a task to return a result. VBA functions can also be called from inside Excel, just like Excel's built-in Excel functions If you want Excel VBA to perform some actions, you can use a sub. Place a sub into a module (In the Visual Basic Editor, click Insert, Module). For example, the sub with name Area. Sub Area (x As Double, y As Double

Description. The Excel VBA Replace function searches for a substring within a string and replaces occurrences of the substring with a second substring. The syntax of the function is: Where the function arguments are: The string that you want to search. The substring that you want to find (that is to be replaced) Sub GetFileAttributes() Dim myFile As String myFile = C:\Users\marks\Documents\Folder\ReadOnlyFile.xlsx 'If the file is Read-Only, display message box If (GetAttr(myFile) And vbReadOnly) <> 0 Then MsgBox The file is Read only End If End Sub. To check for other attributes, replace vbReadOnly in the code above with the required settings Sub Sample1() Dim i As Long For i = 2 To 10 If Cells(i, 1) = 土屋 直美 Then Cells(i, 1) = Replace(Cells(i, 1), 土屋, 田中) End If Next iEnd Sub. サンプル2. 次の例は、セルB2に入力されている数式「=Sheet2!A1」のSheet2をSheet3に置き換えます。 Excel users can execute a VBA sub. Excel users cannot execute VBA functions. What is a VBA Sub? A sub can be described as a small program within the VBA Editor that performs a specific action in Excel. It is used to break large pieces of code into smaller parts that can be easily managed

Substitute Function in Excel VBA for cell range - Stack

Subroutines. A Sub is a small chunk of code that you write to do a specific job. You can run this Sub by pressing F5 in the VBA Editor, you can run it by assigning the Sub to a button on a spreadsheet, and you can even run it from the menu bar at the top of the Editor. In fact, there's quite a lot of different ways you can run your Subs Excel VBA - Die wichtigsten Befehle Folge 24. Zeichen ersetzen in Excel VBA mit der Replace Funktion

Sub Macro1() ' Dim Cel As Range, rng As Range Set rng = Selection.SpecialCells(xlCellTypeVisible) With Application .ScreenUpdating = False For Each Cel In rng Cel.Value = Substitute(Trim(Cel), CHAR(160), ) Next End With End Su The REPLACE function below starts at position 4 and replaces 2 characters with -P. 5. The REPLACE function below starts at position 1 and replaces 3 characters with an empty string. In other words, this formula removes the first 3 characters. You can also use Excel's Find and Replace feature to quickly find specific text and replace it with. Replace - Substitute. Replace ist eine sehr nützliche Funktion, um innerhalb einer Zeichenkette etwas auszutauschen. Tatsächlich kennt Excel-VBA eine Art Replace auch schon in VBA 5 also E97. Diese kann man über Worksheetfunction.Substitute erreichen. Der Nachteil der Substitute ist die begrenzte Zeichenkettenlänge Excel マクロ・VBAの学習・活用方法の記事一覧 If str = Then str = brankStr ' 空白セルのため固定値 Else str = Replace(str, strSrc, strTarget) ' 文字列置換を実行 End If Next MultipleSubstitute = str ' 置換結果をセルに表示 End Function =MultipleSubstitute(B3,E3:F4,セルが空白です。.

VBA Replace String How to Replace Text in String Using VBA

With this code, you can enter multiple rows in the worksheet. When you run this code, you can enter the number of rows to insert and make sure to select the cell from where you want to insert the new rows. If you want to add rows before the selected cell, replace the xlToDown to xlToUp in the code. 4. Auto Fit Columns Astuce VBA : la fonction Replace. La fonction Replace remplace des valeurs par d'autres à l'intérieur d'une chaîne de caractères (c'est plus ou moins l'équivalent de la fonction Excel SUBSTITUE ). Utilisation : Replace (texte, valeur_avant, valeur_après) ou. Replace (texte, valeur_avant, valeur_après, départ, nombre, casse 文字列操作は、マクロVBAでプログラミングする上で必須です、データ整形、データクレンジング、データクリーニング、これらを行うVBAにおいて不可欠なものが、文字列操作関数です。ここでは、文字列操作に関するVBA関数の一覧と Replace関数、InStr関数、StrConv関数について解説します Fonction Excel SUBSTITUE - est très proche de la fonction VBA Replace. Cependant, a) vous ne pouvez pas choisir si elle doit être ou pas sensible aux majuscules (paramètre Compare ), b) le paramètre Start manque et c) l'argument no_position n'indique pas combien d'occurrences doivent être remplacées (comme Count dans Replace.

Školení konzultace - Jak na Excel (Word, PowerPoint

  1. Substituir Excel VBA texto Função replace palavas desejadas na folha de planilha substituir toda ou parte da palavra com Excel VBA, substituição de caracteres usando excel vba função Replace substituir por outro caracter desejado, observe que Replace() é a mesma função Substituir do excel mas no vba
  2. If you are familiar with VBA, you can also replace the alt-enter characters with space, comma, or any other character as you need with VBA in Excel. 1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and then paste below VBA code into the new module window
  3. Find and replace multiple values at once with VBA code. If you are tired of find and replace the values time and time again, the following VBA code can help you to replace multiple values with your needed texts at once. 1. Please create your conditions that you want to use which contain the original values and new values. See screenshot: 2
  4. Excel Questions . VBA for bypassing the replace file prompt when saving as a file in a macro (the prompt appears the file --- already exists. Do you want to replace the existing file?), when I run the macro, I am again prompted about replacing the file. Is there any that <acronym title=visual basic for applications>VBA</acronym.
  5. Replaceメソッドは、セル範囲内で条件に当てはまるセルの文字列を置換するものです。ReplaceメソッドはRangeオブジェクトのメソッドで、ワークシート操作の「検索と置換」の「置換」の機能をマクロVBAで使うものです。VBA関数のReplace関数とは全く違うものになりますので使い分けが必要です
  6. Excel VBA find and replace regex. Posted on November 24, 2018 July 20, 2020 by Tomasz Decker. You can't use regular expressions by writing an excel formula, but you can do it with a bit of VBA code. Follow these steps to add the proper reference in VBA and write a regular expression to find and replace data
  7. The Replace function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA Replace function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the Replace function, where we can use this Replace function and real-time.

Find and Replace All With Excel VBA April 14, 2014 / Chris Newman. What This VBA Code Does. These VBA macros will show you various ways to find and replace all for any text or numerical value. Find/Replace All Within A Specific Worksheet. Sub FindReplaceAll( Please find the steps for eliminating line breaks using Find and Replace: Select all cells where you want to remove or replace carriage returns. Press Ctrl+H to open the Find & Replace dialog box. In the Find What field enter Ctrl+J. It will look empty, but you will see a tiny dot. In the Replace With field, enter any value to replace carriage.

MS Excel: How to use the REPLACE Function (VBA

Excel VBA Replace Function. The Replace function is used to replace text in a string with something else. Suppose, for example, that you have a misspelled word in cell A5. You can use Replace to change the incorrect letters with the correct ones. You can use your spreadsheet and code from the Change Case section for this VBA has two types of procedures for creating and executing codes: Subroutine (or simply Sub) and Function. So far, the examples in this tutorial have always been tied to a Sub.From here on, we will continue to assume that it is a Sub unless it is explicit that it is a Function

Sub statement (VBA) Microsoft Doc

The VBA End Statement is used to mark the end of a specific VBA scope: You can End a Function, a conditional If statement, mark the End of an VBA Enum or VBA Type. The End statement cannot be used within loop, function or procedure to end its execution prematurely. For that we need to use the VBA Exit statement instead Re: Using VBA Replace function for single quote. The result can vary depending on the font used, but you can do the. following: in A1, enter =CHAR (ROW ()) and fill this down to A255. Now, you can make the symbols larger, obviously, by increasing the font of. the column, to, say, 14 pt

EXCEL VBA Substitute メソッド 複数の文字列の置換(大量・一括変換・置換リスト

A VBA Function can accept parameters and return results. Functions, however, can't be executed directly. On the other hand a VBA Sub procedure can be executed directly and can also accept parameters. Procedures, however, do not return values. We often use Subs and Functions often not thinking about their true potential and how much we can squeeze out of a regular VBA Function. Let's start. Learn how to use Microsoft Excel and Visual Basic for Applications now. Excel VBA MsgBox: Step-by-Step Guide and 19 Examples to Create a Message Box with Macros. =Create Excel VBA MsgBox End Sub Effects of executing macro example to create MsgBox. The following image illustrates the results of executing the macro example.. Hi there, I'm trying to delete/replace certain words in a worksheet, but only if they are an exact match. For example, I want to delete the exact words live and play from each cell in the worksheet. I do not want to delete any other words partially matching, e.g. clive, liverpool · Try this: Sub ReplaceEm() Dim re As Object Dim r As Long Dim. Common Excel tasks. These VBA codes will perform common Excel tasks quickly. AutoFit column widths. This code will automatically fit the column widths for all the columns of the worksheet. Sub AutofitAllColumns Cells.EntireColumn.AutoFit End Sub. This code will automatically fit the widths of specific columns on a worksheet

Don't replace the file, , No - 7 End If End Sub Output: Top. MsgBox in Excel VBA: vbRetryCancel MessageBox. Please find the following code and output. It will Display Retry and Cancel buttons.When we click Retry button, It will return value 4 as a output.And, When we click Cancel button, It will return value 2 as a output.. Instructions: Open an excel workbook. Press Alt+F11 to open VBA Editor. Insert a New Module from Insert Menu. Copy the above code and Paste in the code window. Save the file as macro enabled workbook. Open the workbook to test it, it will Run a Macro Automatically. You should see a message box as shown above VBA CDate ()function is one of the most commonly used date functions to convert a date stored in a String variable into a date data type. The example given below will help make the concept clear. Open your MS Excel and enter the date in cell A1 in the following format: March 24, 2014 Langkah-langkah nonton Excel Vba Find And Replace Free - Hi Teman-teman kali ini admin bagikan sebuah film Excel Vba Find And Replace merupakan film terbaru yang bisa dibagikan Dan untuk Daftar Lengkap Pemain Film Excel Vba Find And Replace bisa dilihat dari artikel-artikel yang telah kami posting sebelumnya.Mungkin yang sudah nonton film ini di bioskop, dan siapa tau ingin menyaksikan lagi.

VBA - Return a Value from a Sub Procedure - Automate Excel. Travel Details: A sub procedure is not designed to return a value - that is what a function procedure is used for.However, there may be a time where you are running some code in a sub procedure where you need to get a value from that sub procedure. The way to do this is to use Module or Global Variables or to use a Sub Procedure to. Excel VBA String Functions - Free download as Word Doc (.doc / .docx), PDF File (.pdf), Text File (.txt) or read online for free. Scribd is the world's largest social reading and publishing site. Open navigation men Our products are manufactured using the finest materials and processes and guaranteed to last One limit I ran into when using Excel VBA back in 2000 was memory limits. Excel loads itself into memory like all programs. VBA occupies a portion of that. When I ran a pivot table generation code, I hit the memory limit. So I had to create and. REPLACE in VBA (#vba) In VBA prior to Excel 2K use application.Substitute. REPLACE used in a standard macro on Preselected Text Cells (#macro) For assistance in installing the following standard code macro see Getting Started with Macros..

VBA Replace Function How to Use Excel VBA Replace Function

こちらは、エクセルVBAの Subステートメント の解説になります。 Subプロシージャを作成するために利用されるステートメントですので、しっかり覚えましょう。. また、 ステートメント とは「命令文」と訳されるようにコンピュータに具体的な動作を指定する 最小単位のプログラム です Substitute()函数在excel表格中可以使用,vba中不能用,有没有其他vba函数具有Substitute()的功能呢,或者又什么办法可以在vba中调用Substitute()函数?好多excel函数在vba中不能勇士,有没有通用的调用方法?谢谢 今まで意識しないで作成していた「Sub」プロシージャSub aaa() MsgBox testだぴょんEnd subな感じで、「Sub」の前に「Pub ~Excel VBAでいこう~「Public Sub」「Private Sub」って

Using Excel REPLACE and SUBSTITUTE functions - formula

  1. A vba procedure is usually of two types, a sub procedure or a function. Calling a procedure refers to using or executing a procedure. Procedures should reside in their appropriate modules. A VBA procedure, also referred to as a Macro, is defined as a set of codes which make Excel perform an action
  2. Replace text in a text file using VBA in Microsoft Excel. The macros below can be used to replace text in a text file, e.g. when you want to change a column separator in a text file. before you import it into an Excel worksheet or after you export a worksheet to a text file. Sub ReplaceTextInFile (SourceFile As String, _ sText As String, rText.
  3. Procedure Function e Sub definite dall'utente in VBA. In Excel Visual Basic, un set di comandi per eseguire un'attività specifica viene inserito in una procedura, che può essere una procedura Function o una procedura Sub (anche note come subroutine).. La principale differenza tra una procedura Function e una procedura Sub è che una Function restituisce un risultato, mentre una Sub non.
  4. Hi all, I need to find all <CR> character (carriage return) because when I export a table from Access to Excel, the data contains [] that are carriage returns. so i need to find these characters and replace with null can i do this with vba? many thanks · Hi, you could try something like this: Dim objExcel As Excel.Application ' Excel object Dim.
  5. User-Defined VBA Function & Sub Procedures. In Excel Visual Basic, a set of commands to perform a specific task is placed into a procedure, which can be a Function procedure or a Sub procedure (also known as functions and subroutines).. The main difference between a VBA Function procedure and a Sub procedure is that a Function procedure returns a result, whereas a Sub procedure does not
Excel substitute function usage(8 examples, with multiple

Activate the Visual Basic Editor by pressing ALT+F11. Right-click the project/workbook name in the Project window. Choose Insert -> Module. Type or paste the code in the newly created module. Close the VBE widow. Select the range which you want to remove duplicate values. On the Developer tab, in the Code group, click Macros In short, you cannot make a direct call to a sub procedure from the excel worksheet. VBA - Events. VBA, an event-driven programming can be triggered when you change a cell or range of cell values manually. Change event may make things easier, but you can very quickly end a page full of formatting. There are two kinds of events

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA. I personally use data tables as a way to store user settings without having to modify any VBA code Excel VBA入门(六)过程和函数. 前面讲过,VBA代码有两种组织形式,一种就是过程(前面的示例中都在使用),另一种就是函数。. 其实过程和函数有很多相同之处,除了使用的关键字不同之外,还有不同的是:. 函数有返回值,过程没有. 函数可以在Excel表格中像. Excel-VBAを利用しているとよくある悩み。「Public sub」と「Private sub」と「Sub」の3つの違いについて 悩む女性1 どのように使い分けするの? どんな使い方があるの Remove or Replace Line Breaks (Alt+Enter) in Excel with VBA. First of all, this code works with the Selection, so it will only execute on the selected cells. And the other thing is, this code turns off the Wrap Text command. More or less this is done so you can easily see the effect of the code but it's also there for practical reasons

Insert the below code in the VBA editor and change the value of last to the occurrence you want to find. In the below code I'm searching for the 2nd occurrence. Sub FindnthOccurence () Dim P As Long Dim reqP As Long Dim i as Long Dim last as Long P = 0 last = 2 'Chnage this value to find the nth occurence for i = 1 to las VBA variable is no different than other programming languages. To declare a variable in VBA you use the keyword Dim. Syntax for VBA Variable, To declare a variable in VBA, type Dim followed by a name: Sub Exercise () Dim <name> End Sub. Before we execute the variables we have to record a macro in Excel. To record a macro do the following Sub Get_Data_From_File() Application.GetOpenFilename End Sub That's it! To test, run the macro to watch as Excel presents us with the Open dialog box. Notice that the title of the dialog box is Open and the default file type filter is to display all file types. These are customizable options

Sub Auto_Open() UserForm1.Show End Sub. This will open the VBA form while opening Excel workbook. Change the UserForm1, if you have renamed the form. Now minimize the VBA editor window and come back to excel sheet. Save it as Macro Enable Workbook and close. Reopen the file and enable macros. The VBA form will appear in the excel sheet on. Excelの「検索と置換 (ショートカットCtrl+H)」の機能をVBAで使用する事になります。. このReplaseメソッドは一度設定すると、条件が保存されExcelの「検索と置換」でも条件が変更されます。. 条件が保存されるこの機能については注意しましょう。. Replace. VBA queries related to excel vba if a string contains a substring replace it. excel vba replace text in string. vba replace character. vba replace char with char. vba replace . vba replaceall. substiture vba. replace vba. vba replace string Excel VBAにおける文字列の置換方法には、 ・Replace関数 ⇒文字列の中から指定した文字列を置換 ・Replaceメソッド ⇒セル範囲の中から指定した文字列を置換. の2種類があり少々ややこしいですが、今回はReplace関数の方をご紹介します The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE(952-455-7865,-,) returns 9524557865; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards. Excel FIND Function

VBA Replace Function - How to Use in Exce

  1. The VBA Editor will automatically add an End Sub line a few lines below the Sub. Note: The general convention for typing names for any macro, function, or variable in VBA is to use lower case if there is only one word, and to use an uppercase letter at the start of every new word
  2. VBA is commonly used to automate Excel with macros, add new user defined worksheet functions (UDFs) and react to Excel events. Everything you would previously have done in Excel using VBA can be achieved with Python. Using Python as a VBA replacement has many benefits and is usually faster than VBA
  3. You can write an Excel VBA Macro to find alt +enter and then replace it with a space character, following these steps: #1 click on Visual Basic command under DEVELOPER Tab. #2 then the Visual Basic Editor window will appear. #3 click Insert -> Module to create a new module. #4 paste the below VBA code into the.
  4. Risultati di ricerca per 'Chiamare sub di moduli VBA di Excel' (newsgroup and mailinglist) 73 risposte Per B. Campanini. iniziato 2015-04-24 19:13:56 UTC. it.scienza.matematica. 53 risposte Evitare che sullo sfondo si vedano i fogli di lavoro. iniziato 2009-12-08 21:35:18 UTC..
  5. VBA - Regex & Replace. I am very new to VBA. Below is my code to identify & remove only those dots which are appearing after dates and not after the text. But it does not seem to work. Sub simpleRegexSearch () Dim strPattern As String: strPattern = [0-9]+ [\.] Dim strReplace As String: strReplace = \

一部の文字を置換する. Sub test1 () Dim str1, str2 As String str1 = あああーあああ str2 = Replace ( str1, あ, A, 1, 2) Debug. Print str2 End Sub. 5行目のReplace関数は、5つめの引数で置換する回数を指定しています。. 先頭から2回置換します。 This Excel VBA tutorial explains how to declare private variable, global variable / public variable in VBA, and explain the scope of variable. You may also want to read: For example, in Sub Procedure1, you have declared variableA = 1. In Sub Procedure2, if you use variableA, it will have value 0 (uninitiated) But you can change this and instruct Excel to allow your code to deal with run-time errors. NOTE : I'm going to use the terms sub, function and procedure interchangeably

Video: VBA 过程(Sub) 入门教程和实例 - 懒人Exce

Cleaning Data with Excel&#39;s SUBSTITUTE Formula | FreeVBA RegEx | How to Use Excel VBA Regular Expression?Rumus untuk Menghilangkan tanda &quot;-&quot; pada Nomor RekeningCount Rows Using Power Query | Free Microsoft Excel Tutorials

Once Inside the Worksheet Change Event, if the Target falls within the defined Range and the cell contents change, it will trigger an action inside VBA. Option Explicit. Private Sub Worksheet_Change ( ByVal Target As Range) 'Excel VBA with more cells in the range. If Not Intersect (Target, Range (B2)) Is Nothing Then this is an amazing work. i appreciate very much you sharing it with the rest of the world. i used it to select all the excel files within a bunch of folders and subfolders and sub-sub-folders and added a little extra to resize the page layout for all of them. so thank you. you saved a lot of people from a time-consuming task Sub OptionCompareText() Dim rCell As Range For Each rCell In Range(A1:A10) If rCell = CAT Then MsgBox rCell.Address & has & rCell & in it End If Next rCell End Sub Excel Dashboard Reports & Excel Dashboard Charts 50% Off. Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00