AngelofDev.com

Where I go to AFK

Menu
  • Home
  • How To
    • VBA
  • KOA:DW / GOG
    • Tips & Tricks
    • Tools & Calculators
  • Minecraft Plugins
  • King Tools for KOA & GOG
  • Outlast: Asteroid Defense
  • King of Avalon Player Stats Tracker
Menu

VBA How To Write a Find & Replace Procedure and Call it

Posted on May 16, 2018May 16, 2018 by admin

A procedure is an effective way of cutting down the repetition of manually writing blocks of code you will be frequently using throughout your source code.

The below example will show different methods of writing a Find & Replace procedure in VBA to suit your needs and how to call it within another macro.

The procedure we will be creating will use the Range.Replace method, below is a summary of the regular expression and the specific parameters that we will be using for our needs. You can read more about this expression and see the full list of parameters here.

Expression: Replace( What , Replacement , LookAt , SearchOrder , MatchCase , SearchFormat , ReplaceFormat )

Parameters

NameRequired/OptionalDescription
WhatRequiredThe string you want Microsoft Excel to search for.
ReplacementRequiredThe replacement string.
LookAtOptionalCan be one of the following XlLookAt constants: xlWhole or xlPart .
SearchOrderOptionalCan be one of the following XlSearchOrder constants: xlByRows or xlByColumns .
MatchCaseOptionalTrue to make the search case sensitive.
SearchFormatOptionalThe search format for the method.
ReplaceFormatOptionalThe replace format for the method.

Find & Replace By Column

'Find and Replace by a specific Column.
Sub
FindReplaceCol(col As String, findString As String, replaceWithString As String)

    Columns(col).Replace What:=findString, _
    Replacement:=replaceWithString, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByColumns, _
    MatchCase:=False, _
    SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

Called with

Call FindReplaceCol("A", "replacethistext", "with this text") 'Find and Replace within column A. 

 

Find & Replace By Row

'Find and Replace by a row.
Sub
FindReplaceRow(row As Integer, findString As String, replaceString As String)

    rows(4).Replace _
    What:=findString, _
    Replacement:=replaceString, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    MatchCase:=False, _
    SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

Called with

Call FindReplaceRow(4, "replacethistext", "with this text") 'Find and Replace within row 4. 

 

Find & Replace By Range

'Find and Replace by a specific Range.
Sub FindReplaceRange(cell1 As String, cell2 As String, findString As String, replaceString As String)

    Range(cell1, cell2).Replace _
    What:=findString, _
    Replacement:=replaceString, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    MatchCase:=False, _
    SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

Called with

Call FindReplaceRange("A2","E10", "replacethistext", "with this text") 'Find and Replace within range A2:E10. 

  • Excel
  • Macro
  • Microsoft
  • vba
  • vba-code
  • visual basic
  • Recent Posts

    • How to add Fantom’s SCREAMO NFT to MetaMask Wallet November 1, 2021
    • How I determine which cryptocurrency assets to invest in. May 23, 2021
    • Cryptocurrency Staking Calculator May 20, 2021
    • How To Fix: PowerBI Query Editor “Cannot modify this step because there are errors in previous steps” May 20, 2021
    • How to stop your torrent client spending BitTorrent Tokens (BTT) February 17, 2021

    King Tools for KOA

    King Tools for KOA

    Get it on Google Play

    Outlast: Asteroid Defense

    Outlast: Asteroid Defense

    Get it on Google Play
    © 2022 AngelofDev.com | Powered by Minimalist Blog WordPress Theme