Match and replace



Wondered if anyone has any ideas/experience on this and can give me some pointers?

I'm new to learning excel vba and am trying to create a few scripts to automate common tasks

Raw data (columns A and B)

1 Not in Plan

1 Pending

1 Pass

1 Fail

2 Hold

1 Block

1 Pending

1 Pending

3 Not in Plan

1 Pending

1 Pending

1 Pending

2 Pending

1 Pending

3 Not in Plan

Task 1

Script to review cells B1:B16, Any cell in this range which has text, script is to replace current text with text "Pending", pre-existing blank cells to stay blank (ignore)

I got this partly working, but it does not ignore blank rows

Sub Simple_Select()

Range("B1:B16").Value = "Pending"

End Sub

Found the below elsewhere which does seem to ignore blanks:

Sub ReplaceText2()

Range("B1:B16").Replace What:="*", Replacement:="Pending", LookAt:=xlPart

End Sub

is this good to use?

Task 2

Script to review cells A1:A16, if a cell in this range has a value of 2, the script is to replace cell to immediate right with text "Not in Plan", else if value is not 2 then leave cell to right as it is

I have no idea how to script this one at all (presumably some if statement needed) but nothing I tried to date gets me anywhere

Thanks for tips in advance!




Selected Answer

Yes, your use of Find/Replace is correct. Your formula looks for the wild card "*" (meaning "anything") and replaces it with whatever word you specify. You could use exactly the same code to replace "Block" with "No plan" or "2" with "Not in plan". But be carfeful of the difference between 2 and "2". The first is a number, the other a letter. Excel can't find 2 if you specify "2".

Of course, Replace can't change an adjacent cell. For that you need code as explained below.

Option Explicit
' make it a habit to always add this option
' at the top of every code module

Sub ReplaceText3()

    ' know what you are looking at!! Declare all your variables
    ' If you don't know the data type use "As Variant"
    '   or "As Object" if you know that the type is an object
    Dim Cell As Range               ' loop object

    ' Loop through all cells in the specified range
    For Each Cell In Range("A1:A16")
        If Cell.Value = 2 Then
            Cell.Offset(0, 1).Value = "Not in plan"
        End If
    Next Cell
End Sub

The code takes each cell of the range in turn, one after the other. So the loop will run 16 times in the above example. Each time the variable Cell refers to a different cell starting with A1. Cell.Offset(0, 1).Value = "Not in plan" specifies the cell offset from Cell by 0 rows and 1 column. You could change this specs to specify another cell in the same row as Cell or another row at a fixed distance from it.

Observe If Cell.Value = 2 specifies the number 2. If you formatted A1:A16 as Text the number won't be found but If Cell.Value = "2" will work. This kind of formula will be very useful to you in your project. It can be adapted to many situations.

The code below contains a host of other useful ideas. For one, it's an Event Procedure. It responds to a change made to any cell of a worksheet. A standard code module, like Module1, won't know what happened on a worksheet. Therefore the code must be pasted to the code module that has the name of one of your wortksheets. These modules are set up by Excel when you create a worksheet and they have all the necessary connections already. Paste the code below to the code module of the worksheet from which you copied your sample. (Observe the Option specified at the top).

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' This procedure runs when any cell in the worksheet is modified
    ' "Target" is the cell that was modified (supplied by Excel)

    ' but you want to consider action only if the change
    ' occurred in this range:-
    Const TriggerRange As String = "B1:B16"

    Dim Col As Long                 ' a colour number

    ' check: Is "Target" within the "TriggerRange"?
    '        (Jump right to the end if the change was elsewhere)
    If Not Application.Intersect(Target, Range(TriggerRange)) Is Nothing Then
        ' examine the value of "Target"
        ' assign a different colour number to the variable 'Col' depending upon the Value
        Select Case Target.Value
            Case "Hold"
                Col = 12611584
            Case "Pass"
                Col = vbGreen
            Case "Fail"
                Col = vbRed
            Case "Pending"
                Col = vbBlue
            Case "Not in plan"
                Col = 15773696
            Case "Block"
                Col = vbYellow
            Case Else
                ' if none of the words matches specify no colour
                Col = 0
        End Select
        ' If a colour was specified above Then change the cell colour
        If Col <> 0 Then Target.Interior.Color = Col
    End If
End Sub

I've added a lot of explanations for you but you may also want to take note of the fact that, at its core, the Select statement is just a more efficient concatenation of many If/Else statements.

To make best use of this procedure you will have to learn the use the VB Editor's Immediate Pane. It should be visible at the bottom of your Editor's screen. If it isn't you can add it by pressing Ctl+G or by selecting it from the View menu where it is [falsely] listed as Immediate Window. This pane is extremely useful for programming. You can let your code write to it with Debug.Print "Hello" or Debug.Print Cell.Value. You can also enter Debug.Print vbYellow directly into the Immediate Pane and it will give you the number 65535 upon [Enter]. There is a shortcut for "Debug.Print" which is a simple questionmark.  ? vbyellow will also return 65535 but this won't work when used in a VBA procedure.

From the last example you learn that constants like vbRed, vbYellow, vbBlue, vbBlack etc. are, in fact, numbers - enumerations to be precise (named numbers). You can enter (in the Immediate Pane) ActiveCell.Interior.Color = vbYellow and the cell you selected will turn yellow. You can enter ? ActiveCell.Interior.Color and the pane will show you 65535 upon [Enter]. In this way you can find out the colour you set for any cell and use that number in the above code to apply the same colour to other cells.

Play with this procedure. Modify it so that the font colour will change to white for some of the dark colours and remain black for light colours, changing in synch with the background as ifferent text is entered. The extra syntax you need for this is Target.Font.Color = vbBlack / vbWhite.

I should add that If Col <> 0 Then Target.Interior.Color = Col omits the End If I showed you in my first snippet. You could change this code to 

[CODE]If Col <> 0 Then

    Target.Interior.Color = Col

End If/CODE]

With this structure you can add more line of code that should only be executed when the condition is met.



Hi   Many thanks for this help and your time in looking at it, loads of tips I need to digest to understand (some way above my understanding at present 😊

Struggling to understand how to use the Event Procedure you described? If I look in top left pane of vba editor I can see
VBAProject (Book.xlsm)
Under that is listed “Microsoft Excel Objects”
Under that is Sheet1 (Sheet1)
If I double click that it opens a code window
If I paste the code in there is that what you mean?
I think when I first pasted it there was a warning that this would reset my project, I ignored that and chose proceed anyway

For a simpler start for me, is there a way to specify at the individual macro (module:?) level how to colour cell background and text colour while also changing the text string?   e.g in the replace code:  

Sub ReplaceText1() Range("B1:B16").Replace What:="*", Replacement:="Pending", LookAt:=xlPart End Sub  

Add something like the below:  
Range("B1:B16").Interior.Color = RGB(112, 48, 160) Range("B1:B16").Font.Color = vbWhite

Basically for each replacement of any text for “Pending” could it also apply the background and text colours as per above?

Same thought applies to the other offset code snippet you gave me (which works a treat thanks!) Can this code also apply text and background colour at same time it changes the text?
Nojoke (rep: 2) Oct 17, '20 at 8:55 am
Too many questions. My fault :-) But please do consider asking questions as "Questions". and leaving the "Discussions" for discussions.
Yes. You found the worksheet code modules correctly. 
When your code crashes VBE goes into break mode (Look for the dimmed buttons under the "Run" menu.). If you continue using VBA you may be warned about "resetting the VBA Project" which you might also have done by pressing the green square button.
There is no simpler method but to find the correct module. Avoid re-inventing Excel. They thought about all these things 30 years ago. Discover what they did in response to theri thoughts.
The textbook example they created was of a radio which has buttons for volume and frequency (station selection). They called Volume and Frequency "methods" and the respective settings "properties". You can't adjust the volume by twiddling the frequency button. Nor can you adjust the trebles if there is no button (method) to do it with.
Variatus (rep: 4148) Oct 17, '20 at 8:24 pm
The Cell object (radio) has methods for "Interior" and "Font". If you didn't turn off Intellisense you see them listed in the VBE as you type. Cell.Interior.Color and Cell.Font.Color are two different buttons. Guess what you can do with Cell.Font.Size or what happens which you say Cell.Font.Bold = True.
To uncomplicate this, Cell is the radio. Interior is a button. But Font has the functions of both radio and button. It's like an external tuner or synthesizer.
In answer to your last question, is it possible to change both the volume and the frequency of the radio at the same time? I think it won't be possible with a single command, even if you call Alexis for help. But one after the other ...
Variatus (rep: 4148) Oct 17, '20 at 8:24 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login