Looking for a macro that clears the content of Cell A1 when cell B1 is filled

[Swiemel]

Hi everyone,

I'm looking for a macro that clears the content of Cell A1 IF B1 contains text (this text is automatically generated through an IF formula).
Best case scenario would be to do this until it meets the bottom of my list (Which can vary from 30.000 to 100.000 lines).

Would really appreciate the help!

Thanks in advance,

Swiemel

 

[MARK858]

What is in cell A1? or/and what triggers the change in the formula in B1?

 

[strooman]

If possible, create a dummy column with the data from Column A. Then in A1:
=IF(LEN($B1)>0,"",$C1)
Copy down

 

[Swiemel]

I use two columns:

With the following formulas:

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(Legenda!D$2:D$11,R4)))))>0,"yes","") <-- formula column A
=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(Legenda!D$2:D$11,R4)))))>0,"Wrong","") <-- formula column B


The first column (let's say "A") basically checks wheither the string of text in cell: R4 contains certain words listed in my legenda. IF so it will say "yes", IF not it will stay blanc.

The second column (let's say "B") checks the same thing, however with different words. When those words are found in column B, the "yes" in column "A" should'nt there or needs to be cleared.

hope i'm explaining this in a clear way!

 

[Swiemel]

If possible, create a dummy column with the data from Column A. Then in A1:
=IF(LEN($B1)>0,"",$C1)
Copy down

That could actually work, however i'm trying to keep the amount of columns to a minimum which is why i'm looking for a solution if the form of a Macro.

 

[MARK858]

So just to be clear.
if the B cell isn't "" then the A cell should be ""
if the B cell is "" then the A cell should be the result of the A cell formula.

If the above is true can't you simply do

=IF(B1="",IF(SUMPRODUCT(--(NOT(ISERR(SEARCH(Legenda!D$2:D$11,R4)))))>0,"yes",""),"")

or =IF(LEN(B1)=0 ...then rest of formula will do equally well.

which is why i'm looking for a solution if the form of a Macro.

The reason I am asking is a formula result can't trigger a macro by itself and because I can't currently see any trigger then I am looking at a formula solution which will give empty strings.

 

[Swiemel]

Thanks Mark and Strooman, will try that solution after my weekend!
Will report back wheither it worked or not!

 

[Swiemel]

Thanks guys, it worked like a charm!

much appreciated!