Execute a macro based a text in a column which is constantly changing by formulae

[vijayamani1987]

hi people,

Thanks in advance, and excuse me for my average English.

I am very new to VBA. I have a excel sheet, where cells in column B will change on predefined time frame, ex.5 minutes. I want to execute a macro when any cell in that column comes to have specific text, say YES. Please help me out with this.

Note: The values will change only by formula

 

[vijayamani1987]

Hi Peter_SSs ,

Yeah, the column B has similar formula just copies down from B2, its a bigger one and so it's difficult to post the exact formula. The values in other columns will keep change for every 10 or 15 minutes. Based on changes in the corresponding cells from other columns, the B:B will show either "completed" or "Not completed" or "-". And i need to call a macro say "Record", whenever any cells in B:B gives "Completed" or "Not Completed" values. Hope I have given my problem to some extend.

 

[MARK858]

its a bigger one and so it's difficult to post the exact formula

Why is it difficult to post the first formula, it is a simple copy and paste?

 

[Grand]

Have you tried it?

Yes, I did. I have refered to one of the cells in cloumn B in the sheet of interest in another sheet:

=Sheet2!A1

So the formula above is placed in one of the cells n column B. Then this code is fired when we write something in cell A1 of sheet 2:

Private Sub Worksheet_Calculate()


Dim MyRng As Range

  With Range("B:B")
    Set MyRng = .Find(What:="Yes", _
       After:=.Cells(.Cells.Count), _
                  LookIn:=xlValues, _
                   LookAt:=xlWhole, _
             SearchOrder:=xlByRows, _
           SearchDirection:=xlNext, _
                 MatchCase:=False)
        If Not MyRng Is Nothing Then
           MsgBox "My range contains YES!"
           'here you can call you macro
        End If
    End With
End Sub

 

[vijayamani1987]

Hi MARK858,

I am at offsore oil rig.The exact file I'm working is back at home. I will create a sample file and upload it later.

 

[vijayamani1987]

Hi Grand,

can you please upload that file so that i could easily understand

 

[Grand]

Hi Grand,

can you please upload that file so that i could easily understand

Here

[URL]https://files.fm/u/qvv4g2kk[/URL]

 

[NoSparks]

@ vijayamani1987
Quite some time ago I tucked away this link to a question on another forum. May give some insight towards a solution.

 

[vijayamani1987]

Hi Grand,

Thanks, I checked it and it's working. But how to make it to search to search the nest value. Below is the example.

[TABLE="width: 442"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Activate[/TD]
[TD]event A[/TD]
[TD]event B[/TD]
[TD]event C[/TD]
[TD]event D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]execute[/TD]
[TD]-[/TD]
[TD]cancel[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]cancel[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]cancel[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]cancel[/TD]
[TD]-[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]cancel[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]cancel[/TD]
[TD]-[/TD]
[TD]cancel[/TD]
[TD]cancel[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]-[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]cancel[/TD]
[TD]cancel[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]execute[/TD]
[TD]-[/TD]
[TD]cancel[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]cancel[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[TD]execute[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]cancel[/TD]
[TD]cancel[/TD]
[TD]cancel[/TD]
[TD][/TD]
[TD]cancel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The values of columns C to F will be updated automatically for every 30 mins. Depending on those values column B:B gets the values either "execute" or "cancel". When the values changes I need the macro to search column B:B (cell by cell), and has to call either execute macro or cancel macro. Thanks in advance.

 

[Grand]

Hi
Do you want that macro be executed for any instances that is fund in column B? If there are let's say 100 "execute", then the acro shall be executed 100 times?

I am thinking, you can modify the code I provided earlier but this time search for "execute" instead of "yes". That would do what you need.

 

[vijayamani1987]

Hi Grand,

Thank you, I tried your code, worked perfectly. But I had problem in making it to the next result. I mean when it got the first "execute" or "cancel" string, it calls the "execute macro" or "cancel macro" (which will copy the entire row and paste it into a new sheet). But I don't know how to make it to look for the next result and keep going on.

In the mean time I come across a similar macro in some other page. https://www.techonthenet.com/excel/macros/search_for_string.php

It's almost completing my purpose except that every time when my values got update, it has to look for only new "execute" or "cancel" string. But it's keep copying the already copied cells also. I think I can do it with another column and with some regular formulas, but asking you is there any changes we can make it in the macro that would skip the already executed rows from being copied again.