Run Macro automatically

[albatross32]

I am a VBA novice but want to use;

Private Sub Worksheet_Change(ByVal Target As Range)

In Range S3:S232 all cells are empty.

Formulas within the Worksheet will lead to "1" being entered into one cell at a time in the Range S3:S232.

When that happens I want a two second time delay and then a "1" to be entered in column V of the same row. The existing formulas will not allow another S cell to change until the V cell has changed.

I need the function to remain open so that all the time the Worksheet is activated everytime one of the S cells change the V cell of the same row changes after a two second time delay.

 

[stnkynts]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Not Intersect(Target, Range("S3:S232")) Is Nothing Then
    If Target.Value = "1" Then
        Application.Wait (Now + TimeValue("0:00:02"))
        Target.Offset(0, 3).Value = "1"
    End If
End If
 
End Sub

 

[albatross32]

Thanks for this but it does not appear to work. There is no error code it just does not function.

 

[stnkynts]

Did you make sure to put it in the applicable sheet. I tested it and it works fine for me.

 

[stnkynts]

Ahh i think i may see the problem. After reading your OP again i noticed that you said you have a formula in column S which puts a value of 1 in column S depending on some set of events. In this situation the macro will not recognize a change in column S if it changes to a value of one. We would need to set the intersect range to the range that makes the change in column S. Does this make sense?

 

[albatross32]

The S column is controlled by a formula in column Q as follows;

=IF($A$9>0,(AND(M3=1,P3=1)),0)

The formula in column S is;

=IF($A$9>0,(IF(Q3=FALSE,"",1)),"")

Any further thoughts?

Thanks for your help thus far in any case!

 

[stnkynts]

Are there more formulas in column M and column P that refer elsewhere or is column M & P the columns that you manually enter data. If they are then this will work:

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Not Intersect(Target, Range("M3:M232")) Is Nothing Then
    If Range("S" & Target.Row) = "1" Then
        Application.Wait (Now + TimeValue("0:00:02"))
        Range("V" & Target.Row).Value = "1"
    End If
End If
If Not Intersect(Target, Range("P3:P232")) Is Nothing Then
    If Range("S" & Target.Row) = "1" Then
        Application.Wait (Now + TimeValue("0:00:02"))
        Range("V" & Target.Row).Value = "1"
    End If
End If
 
End Sub

 

[albatross32]

Thanks for your further response. I will not be able to run this till later. However, can you clarify the comments in your post. Columns M and P both contain formulas that are involved in creating the circumstances for insertion of data into column S.

Once perfected, this workbook will be used to automatically monitor data received from an online source. There will not be any manual input of date into the workbook.

Will this affect your code?

 

[stnkynts]

The code that I provided will not work given the information you just provided. What it boils down to is that in order for a worksheet change event to be recognized it needs to see a manual change. I need to know which cells you are manually changing. In other words in which cells are you inputing data.

 

[albatross32]

I still have a problem then. As mentioned in my earlier post, this workbook will evaluate date from an online source. Once connected to that source, I will not be manually changing any cells.

I am at a loss to understand why when you have such an amazingly powerful tool as Excel that it does not seem able to perform what, to this layman, would appear to be a relatively straightforward task.

It is only the need for a time delay that prevents me from simply using a formula and avoiding VBA altogether.