Thread: Help!!

Hello All,

I am new to this forum and would be grateful for some input.

I understand that there is an issue with sumif/averageif/countif that should they be used on an external link and the corresponding file is closed, one gets an #NA. This is because these functions only work with active files and not with closed external links.

Given this, I have basically designed an Excel vba macro which in any workbook loops over each worksheet and:

1) searches for any occurences of sumif/averageif/countif and should it find one,
2) it checks whether the found sumif/averageif/countif function takes any external links, if so,
3) the macro copes and pastes this cell in as values

I hope this makes sense.

Within my macro, I search for sumif/averageif/countif using the following line of code:

ActiveSheet.Cells.Find(What:="SUMIF", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

The problem is that if a worksheet has used sumif/averageif/countif alot then this searching takes alot of time as I have placed this line of code within a while loop which means it loops until there is no occurence left of sumif/averageif/countif.

My question is that, does someone has a quicker way of doing this?

Thanks for your help!
IpMan

I don't know of another way.

Please take a few minutes to read the forum rules about thread titles.

Thanks.