formula based on three dependent drop down lists

[HansV]

You have my e-mail address.

[HansV]

Hi DVM, I have received your workbook. Can you give an example of where you experience problems?

[DVM]

Dear Hans
I have sent the file with error snap shots.
Regards
DVM

[HansV]

The new file that you sent me still contains the old code that removes formulas. Please modify the Worksheet_Change event of the MasterList sheet as described in Post=132753

[HansV]

I can't reproduce the errors that you report. Here is your workbook, zipped. I can enter and edit values without error messages.

testforisacode_01.zip

[HansV]

Here is a (hopefully) slightly improved version. If column D is changed, columns E and F are cleared. If column E is changed, column F is cleared.

testforisacode_02.zip

[DVM]

How could some one be so helpful as you?
you gave me a hint that the file had code which deleted the formulas.
I now understand the situation. Without knowing this I sent you two files - one with that stupid code and the other without. That was accidental.
So I went back to the earlier file versions and see how the problem got resolved.
Thanks a lot.

[DVM]

"Here is a (hopefully) slightly improved version. If column D is changed, columns E and F are cleared. If column E is changed, column F is cleared."

This is excellent. But could you please make a small bit of change here:
When D is deletd by hitting the hard key on keyboard, I want the contents of the entire row to be erased.
When D is changed, except for D ofcourse, I want the entire row to be erased

Thnaks again and best regards.

[HansV]

Erase the entire row, or delete the entire row? Erase is problematic because you'll remove the formulas too...

[DVM]

No, I mean clear the contents . I dont want to remove the entire row. Thanks for asking.

[HansV]

But if you clear the contents of the entire row, you lose the formulas in that row. So if you enter new values in column D, E and F, the ISA Code will not be computed.

[DVM]

Ok, I agree with you. So is there a way that we clear only those cells which do not have a formula? If yes, that would be excellent becuase when we fill the other cells the formula cells will be automatically filled. The formula cells are G, J, L O and V.

[HansV]

You can replace the line

Code: Select all

    Target.Offset(0, 1).Resize(1, 2) = ""

with

Code: Select all

    If varNew = "" Then
        Target.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
    Else
        Target.Offset(0, 1).Resize(1, 2).ClearContents
    End If

See attached version (zipped).

testforisacode_02.zip

[DVM]

Dear Hans,
Sorry, I was out the whole of today and could not see this solution. It is very correct, for changing D however, the code clears E and F , but how about other cells in the row. Is it possible that all cells other than D are cleared much the same way as Deleting D.
Thanks in advance and with best regards
DVM

[HansV]

If you ALWAYS want to clear the other cells, use

Code: Select all

    Target.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

without the If ... Else ... End If

[DVM]

Thank you, Hans. I had presumed (my mistake) that I hv told you about the 'Delete" and "modify' procedures in the current code. When D is deleted the entire row does get cleared but the range that was copied to destination does not get deleted. I have a feeling that if your 'Delete" procedure is a part of the existing code which takes care of deleting the destination copy, that that would give me the desired result. I am aware I am asking too much from your courtsey, but I would be happy to experience the completion of your help. please advise.

[HansV]

See if the attached version works better. If not, I'm afraid I can't fulfil your wish.

testforisacode_02.zip

[DVM]

Thank you, Hans. It gives me an error the first time I click on cell D. So I cannot move ahead at all to see if your revised code is working.

[HansV]

What is the error message?

[DVM]

Runtime error 09, and then when I move f/w by clicking 'end program' the macro does not work.