Code doesn't run when cell contain formula

Good day Excel Gurus.

I'm trying to figure out, why is that, when any cell in column A contain formula, the code does not run as it should?
For example C3 failed to return the value from Database (or not found), because A3 contains formula.
But it is working normally when there's no formula in it.
Please help. Thanks!

Worksheet_Change trigger only manual change only, not change from formula in A3. Try vlookup A3 instead.

I'm not sure I understand exactly how you are interacting with the spreadsheet, but I am wondering if, rather than a change event, you are looking for something triggered by a calculate event, or maybe some other event.

It might a bit much, but I would suggest you explain exactly what "it works normally when there's no formula in the cell" and exactly what you mean by "when a cell contains a formula, it doesn't work as it should." Once we understand exactly how you are interacting with the sheet and what "works" and "does not work" mean, then maybe we can help by suggesting a better event structure or how to change the existing change event code.

I did look at the sample sheet. What I see is a change event procedure that is specifically looking for changes/edits to column A (the "If cell.Column=1 Then...End If" block if). When you talk about putting a formula into column A, that suggests that, as the project develops, you no longer intend to change/edit column A. As currently programmed, you have pushed the changes/edits into column E (column 5). Assuming you still want to use a change event, then you would need to change the code to check for changes in column 4 rather than column 1. But, the sample sheet appears to be far from complete, so I am wondering if the next phase of the project will be to add a formula to column E, indicating that you are not going to change/edit column E in the final version of the spreadsheet.

Ultimately, the end goal is to identify which cells are going to be your main input cells that you will be changing/editing when the spreadsheet is completed so you can program the change event procedure to monitor those cells and respond appropriately. If that will be column E, the changes to the event procedure should be straightforward. Rather than make those changes now, I would wait until the spreadsheet is closer to its final form where we are more certain which cells will be the main input/edit/change cells, then visit the change event procedure to make it responsive to those input cells.

Good day MrShorty
Thanks for your feedback. The spreadsheet is almost in its final form. The only thing that is delaying me from completing it is because of is this.

I have uploaded another worksheet for you to look into.
To clarify, the main input cell is going to be Main!E2
Once input value is keyed in Main!E2, any cell in column A, should be able to accept formula and run the code.
I hope this explanation make sense to you. Thanks ya.

I don't use event code in my spreadsheets, so I'm not that skilled at using them. My first question, where this change event will be looking for changes to a cell on one sheet and changes to that cell will trigger code to change other sheets in the workbook, will this be better as a workbook_change event procedure (in the ThisWorkbook module) or will this be better as a sheet_change event in the Main module? I would have to rely on those with more experience than me to answer that question, or rely on you to decide which you would prefer.

In either the case, the main flow of the procedure is to watch for changes in the worksheet/workbook and when Main!E2 is changed/edited, then go to L1 and do the Find, Copy, Paste operation from Database into L1. The main change I see to this part of the current procedure is changing the Offset() methods/properties so that they are no longer relative to the change event's target cell, but always referencing column C of L1.

… where this change event will be looking for changes to a cell on one sheet and changes to that cell will trigger code to change other sheets in the workbook, will this be better as a workbook_change event procedure (in the ThisWorkbook module) or will this be better as a sheet_change event in the Main module?

Not really. If, let's say, you have 10 sheets all laid out the same, doing the same thing, and reacting to the same events, you could use a Workbook Sheet Change Event handler rather than repeat the code 10 times. You'd the use the sh variable to determine which sheet has been changed. Where you have a change to one sheet, whether or not it affects other sheets, you would just have the code behind that sheet. If the change affects other sheets, it may be appropriate to disable event handling to avoid cascading changes … although that may be what you want to achieve. Hope that makes sense. What you are trying to avoid is one change initiating unnecessary changes and potentially looping.

If you remove the code from the L1 Sheet module, and place this code in Main sheet tab

Please Login or Register  to view this content.

It seems to me that all you need to do is change the block If where the cell value is copied to something uses a copy/paste method as you had done in your original code. As I noted above, the offsets will now need to be relative to column A rather than the changed cell. So replace this

Please Login or Register  to view this content.

with something like

Please Login or Register  to view this content.

Try this,

Please Login or Register  to view this content.

There should be two worksheet_change events, one for "L1", other for "Main"
For "Main":

PHP Code: 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, vFND As Range, L1 As Worksheet, Data As Worksheet
If Target.Address <> "$E$2" Then Exit Sub
Set L1 = Worksheets("L1"): Set Data = Worksheets("Database")
For Each cell In L1.Columns("A").SpecialCells(xlCellTypeFormulas)
    Set vFND = Data.Range("A:A").Find(cell, LookIn:=xlValues, LookAt:=xlWhole)
    If Not vFND Is Nothing Then
        vFND.Offset(, 1).Copy cell.Offset(, 2)
    Else
        cell.Offset(, 2) = "not found"
    End If
Next
End Sub 


For "L1"

PHP Code: 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, vFND As Range, L1 As Worksheet, Data As Worksheet
If Target.Column <> 1 Or Target.Count > 1 Then Exit Sub
On Error Resume Next
Set Data = Worksheets("Database")
Set vFND = Data.Range("A:A").Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
    If Not vFND Is Nothing Then
        vFND.Offset(, 1).Copy Target.Offset(, 2)
    Else
        cell.Offset(, 2) = "not found"
    End If
End Sub