Calling any Visual Basic programmers.
We have hundreds of excel files, each containing numerical data from cells C3 : J1033 inclusive. We are trying detect any string of '20 or more' continuous zeros (going down a column) and to replace the zeros with the mean of the corresponding cells from the other 7 columns. Therefore we'd like any string of '19 or fewer' zeros to be left as zeros.
We have written part of this as a formula (see below) which works in Excel, but doesn't work as a line in a visual basic program, giving the following error message:
"Compile error: Expected: line number or label or statement or end of statement"
We think this is because it is too long for a single line in VBA. If anybody knows how to wrap formulas in VBA or can recommend a simpler way of detecting and replacing a string of 20 or more zeros, our small stats team would be very grateful.
Many thanks
Brad
Please contact: [log in to unmask]
=IF(AND(C20=0,OR(SUM(C1:C19)=0,SUM(C21:C39)=0,AND(C21=0,SUM(C2:C19)=0),
AND(SUM(C3:C19)=0,SUM(C21:C22)=0),AND(SUM(C4:C19)=0,SUM(C21:C23)=0),
AND(SUM(C5:C19)=0,SUM(C21:C24)=0),AND(SUM(C6:C19)=0,SUM(C21:C25)=0),
AND(SUM(C7:C19)=0,SUM(C21:C26)=0),AND(SUM(C8:C19)=0,SUM(C21:C27)=0),
AND(SUM(C9:C19)=0,SUM(C21:C28)=0),AND(SUM(C10:C19)=0,SUM(C21:C29)=0),
AND(SUM(C11:C19)=0,SUM(C21:C30)=0),AND(SUM(C12:C19)=0,SUM(C21:C31)=0),
AND(SUM(C13:C19)=0,SUM(C21:C32)=0),AND(SUM(C14:C19)=0,SUM(C21:C33)=0),
AND(SUM(C15:C19)=0,SUM(C21:C34)=0),AND(SUM(C16:C19)=0,SUM(C21:C35)=0),
AND(SUM(C17:C19)=0,SUM(C21:C36)=0),AND(SUM(C18:C19)=0,SUM(C21:C37)=0),
AND(C19=0,SUM(C21:C38)=0))),1,0)
|