VBA Macro is not running with existing formula

[rkb1510]

Hi Everyone,
I have created one macro which can insert date in Column B whenever Column A read "Ready" But it is working only when I Manually add "Ready" word in col A If Ready word come when condition of formula come true Col B is not populating date. I have set some formula in Col A which Populate ready whenever condition of formula become true. Can someone please help me with this issue?

 

[mumps]

What formula are you using in column A? It would also be easier to help if you could use the XL2BB add-in (icon in the menu) to attach the desired selected range (not a picture) of your data. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Joe4]

My guess is that you are using a Worksheet_Change event. That VBA code only fires when cells are manually updated.
There is another event named Worksheet_Calculate, which runs whenever a calculation is done on the sheet.
However, a big caveat is that Worksheet_Calculate CANNOT identify which cell's formula changed, only that some calculation somewhere on the sheet changed.
So that is not a good option to use for Date stamping, since it cannot tell where the update was.

You may be able to use Worksheet_Change event, looking at a different cells, if the formula in question is referencing other cells IN THE SAME ROW.
Can you show us what that formula looks like?

 

[rkb1510]

My guess is that you are using a Worksheet_Change event. That VBA code only fires when cells are manually updated.
There is another event named Worksheet_Calculate, which runs whenever a calculation is done on the sheet.
However, a big caveat is that Worksheet_Calculate CANNOT identify which cell's formula changed, only that some calculation somewhere on the sheet changed.
So that is not a good option to use for Date stamping, since it cannot tell where the update was.

You may be able to use Worksheet_Change event, looking at a different cells, if the formula in question is referencing other cells IN THE SAME ROW.
Can you show us what that formula looks like?

Thanks Joe4, It is exactly same problem I have used worksheet_change that is why it is only working when I make entry manually. I am wondering how can I achieve Calculate and change both togeather because when I try to use Calculate it is not capturing manual entries.

 

[Joe4]

Please answer the question I asked, as we may be able to do something, depending on the details you provide to the questions I asked.

You may be able to use Worksheet_Change event, looking at a different cells, if the formula in question is referencing other cells IN THE SAME ROW.
Can you show us what that formula looks like?

 

[rkb1510]

Please answer the question I asked, as we may be able to do something, depending on the details you provide to the questions I asked.

You may be able to use Worksheet_Change event, looking at a different cells, if the formula in question is referencing other cells IN THE SAME ROW.
Can you show us what that formula looks like?

=IF(AND(X2="Good",Z2="Good", L2="Complete"),"Ready","Not Ready") this is the formula

 

[rkb1510]

=IF(AND(X2="Good",Z2="Good", L2="Complete"),"Ready","Not Ready") this is the formula

and code is as below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim existingDate As String
Dim existingDate2 As String

'Check if change is made in col M
If Not Intersect(Target, Me.Range("M:M")) Is Nothing Then

'Loop through all cells
For Each cell In Target

'Verify if ready date col has any dates or not
existingDate = cell.Offset(0, 3).Value
existingDate2 = cell.Offset(0, 1).Value

'If staus col has "Ready" append date in col AC and also insert latest date in Col N
If cell.Value = "Ready" Then
If existingDate <> "" Then
cell.Offset(0, 3).Value = existingDate & ", " & Now
cell.Offset(0, 1).Value = Now
Else
cell.Offset(0, 3).Value = Now
cell.Offset(0, 1).Value = Now
End If

ElseIf cell.Value = "Not Ready" Then
cell.Offset(0, 1).ClearContents

'If Col M has any value other than "Ready" or "Not Ready" then clear cell values
Else
cell.Offset(0, 3).ClearContents
cell.Offset(0, 1).ClearContents
End If
Next cell
End If

End Sub

 

[Joe4]

=IF(AND(X2="Good",Z2="Good", L2="Complete"),"Ready","Not Ready") this is the formula

OK, so is that formula you posted specifically for row 2 (the formula in cell M2)?
If so, then the formula in cell M3 would look like this, right?

=IF(AND(X3="Good",Z3="Good", L3="Complete"),"Ready","Not Ready")

If so, how are column L, X, and Z being updated?
If they are being updated manually, then we just need to update our Worksheet_Change event procedure code to watch columns L, X, or Z for changes, instead of watching column M.

 

[rkb1510]

OK, so is that formula you posted specifically for row 2 (the formula in cell M2)?
If so, then the formula in cell M3 would look like this, right?

=IF(AND(X3="Good",Z3="Good", L3="Complete"),"Ready","Not Ready")

If so, how are column L, X, and Z being updated?
If they are being updated manually, then we just need to update our Worksheet_Change event procedure code to watch columns L, X, or Z for changes, instead of watching column M.

formula for row 3 is correct as you mentioned, Col L, X and Z are update by vlookup or other formulas, it is kind of dependent columns on multiple other columns and sheets in same workbook

 

[Joe4]

Is there ANYTHING being manually entered in to the same row that is triggering these formulas to be updated (that ultimately lead to column M being updated).
If so, which columns are those?

You would just update the Worksheet_Change to watch for changes in those columns.
However, that would only work if the columns being manually updated are in the same row as the formula in column M being updated.

For example, let's say that columns A and B are being manually updated.
Let's say that manually updating column A doesn't affect anything, but manually updating column B ultimately leads to column M in the same row being updated.
If so, then we can program our Worksheet_Change event procedure to watch column B for updates, because those are the updates that ultimately lead to column M being updated.