Solved

VBA FIND REPLACE IF

Posted on 2014-11-12
4
117 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 30

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

717 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