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

vijayamani1987

New Member
Joined
Mar 16, 2014
Messages
32
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
 
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.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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:

Code:
=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:
Code:
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
 
Upvote 0
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.
 
Upvote 0
@ vijayamani1987
Quite some time ago I tucked away this link to a question on another forum. May give some insight towards a solution.
 
Upvote 0
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.

ABCDEF
1Activateevent Aevent Bevent Cevent D
2execute-cancelexecute
3cancelexecuteexecutecancel
4executeexecuteexecuteexecuteexecute
5cancel-executeexecute
6executeexecutecancelexecute
7cancel-cancelcancel
8executeexecute-execute
9cancelcancelexecuteexecute
10execute-cancelexecute
11-executeexecutecancel
12executeexecuteexecuteexecuteexecute
13cancelcancelcancelcancel

<tbody>
</tbody>

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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top