Solved

VBA FIND REPLACE IF

Posted on 2014-11-12
4
110 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 31

Accepted Solution

by:
Rob Henson earned 125 total points
Comment Utility
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 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now