excel - Detecting sheet change, clearing the sheet giving an overflow -

excel - Detecting sheet change, clearing the sheet giving an overflow -

i have worksheet_change event, @ sheet module level. problem want able clear sheet @ times. however, when clear sheet overflow:

private sub worksheet_change(byval target range) 'this line causing problem because clearing whole sheet causes count massive if target.count = 1 if target = range("a4") if instr(lcase(target.value), "loaded") <> 0 range("a5").formulaarray = "=my_function(r[-1]c)" end if end if end if end sub

i trying accomplish following:

i press button , sheet cleared (clears existing array formula data), paste in formula sheet , phone call formula. formula returns info excel cache , changes cell containing formula (a4) string saying "loaded". when observe cell alter value "loaded" equivalent on ctrl + shift + enter on array formula function below, display data.

i believe using xl2007+?

the target.cells.count long value , hence when select entire worksheet .count little hold result.

replace line

if target.count = 1

with

if target.cells.countlarge = 1

you might want see this since using worksheet_change

edit:

two other things

1)

you can replace line

if target = range("a4")

with

if not intersect(target, range("a4")) nil

2)

this line

if instr(lcase(target.value), "loaded") <> 0

can written as

if instr(1, target.value, "loaded", vbtextcompare)

excel vba excel-vba