Solved

VBA FIND REPLACE IF

Posted on 2014-11-12
4
114 Views
Last Modified: 2014-11-17
Hi,

I need a snippet of code that will look at Col B and find entries "Savings and Deposits", once found, it replaces its adjacent entry in Column A with

"Payments & Cash Management"

Thanks
0
Comment
Question by:Seamus2626
4 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 125 total points
ID: 40437157
Do you actually need code, you could do it with formula; in a spare column:

=IF(B2="Savings and Deposits","Payments & Cash Management",A2)

Copy down as required and then copy and paste values into col A.

Thanks
Rob H
0
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 125 total points
ID: 40437223
this will do the job.  see the attached example workbook.

Option Explicit
 
Sub ReplacePro()
     
     'by ProfessorJimJam
    Dim lastrow As Long, rng As Range
     
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
     
    Application.ScreenUpdating = False
     
    With Sheet1
        .AutoFilterMode = False
        Set rng = .Range("B1:B" & lastrow)
        rng.AutoFilter field:=1, Criteria1:="Savings and Deposits"
        rng.Offset(0, -1).SpecialCells(12).Value = "Payments & Cash Management"
        .Cells(lastrow + 1, 2) = ""
        .AutoFilterMode = False
         
    End With
     
    Application.ScreenUpdating = True
     
    Set rng = Nothing
    
End Sub

Open in new window

EE.xlsm
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 125 total points
ID: 40444183
ok here is my version of the Macro. It is important to note the following:

1) This macro will look in the Activesheet so you position yourself in any sheet that you want to apply this macro for and then run FindReplace.
2) It will look in all occurrences in Col B for the exact: "Savings and Deposits" string but ignoring caps i.e. it will also look for "savings and Deposits" as well as "savings and deposits" but for sure it will ignore strings that are like:
"Savings & Deposits" or "Saving and Deposit" etc...
3) It will give you a feedback at the end of how many were found and replaced in Col A.

Please check it and in case you want to adapt it more we can cater basis your needs.

Sub FindReplace()
Dim WS As Worksheet
Dim lCount As Long
Dim cCell As Range
Dim sFind As String, sReplace As String
Dim FirstAddress As String

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS = ActiveSheet
sFind = "Savings and Deposits"
sReplace = "Payments & Cash Management"

'---> Start Process
With WS.Range("B:B")
    Set cCell = .Find(what:=sFind, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not cCell Is Nothing Then
        FirstAddress = cCell.Address
        Do
            cCell.Offset(, -1).Value = sReplace
            lCount = lCount + 1
            Set cCell = .FindNext(cCell)
        Loop While Not cCell Is Nothing And cCell.Address <> FirstAddress
    End If
End With

WS.Range("A:B").EntireColumn.AutoFit

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox "A total of " & lCount & " occurrences of " & sFind & " were found in Col B and corresponding cell in Col A were changed to " & replace

End Sub

Open in new window



The attached workbook has some weird samples that you may try.
gowflow
FindReplace.xlsm
0
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 125 total points
ID: 40444602
Sub kTest()
    
    Dim x As String, y As String
    
    With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(2))
        x = .Address
        y = .Offset(, -1).Address
        .Offset(, -1).Value = Evaluate("if(" & x & "=""Savings and Deposits"",""Payments & Cash Management"",if(len(" & y & ")," & y & ",""""))")
    End With
    
End Sub

Open in new window


Kris
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question