topic Failure using find and replace in excel file using QlikView macro in QlikView

Failure using find and replace in excel file using QlikView macro

Wed, 09 Jan 2013 16:35:41 GMT

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.

Re: Failure using find and replace in excel file using QlikView macro

Gysbert_Wassenaar — Sat, 12 Jan 2013 15:46:43 GMT

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.

Re: Failure using find and replace in excel file using QlikView macro

Mon, 14 Jan 2013 13:03:15 GMT

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.

Re: Failure using find and replace in excel file using QlikView macro

Thu, 24 Jan 2013 00:34:08 GMT

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.

Re: Failure using find and replace in excel file using QlikView macro

Fri, 25 Jan 2013 13:01:35 GMT

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.