Posts by StephenR

    OK, try this. I'm assuming this is a Worksheet Change event.

    I'll have to look at this tomorrow.


    As an aside, in your loop should it be "r" being deleted rather than Target?


    Going back to basics if Target consists of more than one cell you should loop through to check the intersect with columns 21/24. If Target should only be one cell you should add a line to otherwise exit the sub.

    Please add code tags in future as per forum rules.


    After the first block runs you delete Target so it no longer exists and hence the error as you cannot access the column property of a non-existent range. Move the deletion to the end.


    A bit of general advice - learn to indent your code, makes it much more readable.

    I think the only change you need to make is move the firstaddress line outside the Do loop; otherwise it will change every time you find the search term. I've taken the opportunity to declare your variables and indent your code, both of which are good practice and help to spot errors. Your original code can't even have compiled as your End If and End With statements were the wrong way round.

    Try this. I don't think there's a quick way to check for 3 consecutive zeros so this involves a loop. I have simplified your code a little just to do this bit of it, but I hope you can follow. Sample workbook also attached.


    An alternative to adding a last row to the array would be to create a new array of 1 x 3.

    No need to apologise. Can you try this for writing the values to the sheet?


    This seems to work for reasons which are not apparent to me. Got from https://stackoverflow.com/ques…ter-setting-the-listindex