Failure using find and replace in excel file using QlikView macro

[#1]

This is my first post so I hope I can give enough info to get help.

I am creating an excel file from my pivot table and I need to add the average of the data in a column in the last row.  Some of the rows have a zero value and this distorts the average.  I need to replace the zeroes with an empty field. When I capture the find/replace as an excel macro I get:

   Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:= _

        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

my code:

   sheet.Columns("D:D").Replace What="0", Replacement="",LookAt=1

error message : Microsoft Excel cannot find any data to replace .....

I have tried many variations of my code -- always with the same results.

Any Suggestions?

THANKS.

[Gysbert_Wassenaar]

Try: sheet.Columns("D:D").Replace "0","",1

If it doesn't work, try searching the internet for vbscript examples. This is not a Qlikview question, but a vbscript question.

[#3]

Thanks for your response. But I am still getting the same error message. However, reading al l the way to the end of the long error message, I see “ may be on a protected sheet”. I might have jumped to the wrong conclusion about the nature of the error. Unfortunately I have no idea what a “protected sheet” is or how to “unprotect” it.

[#4]

Hi,

I am also getting a similar error, please let me know if you found a way to replace some text via a macro in an exported excel file.

Thanks,

Puneet.

[#5]

Sorry, I was not successful in correcting the issue. I think it really was the “Protection” problem. I did a work around by creating a macro in the master document that I was adding the data to. The user must execute the macro to get the desired result.