Replaceing destination cell contents with new data in Excell

Hi
I am using the following code to replace old data with new.

Set objRange1 =
Application.Workbooks("Analysis.xls").Worksheets("Data").Range("B2").EntireColumn
Set ObjRange2 =
Application.Workbooks("Analysis.xls").Worksheets("Data").Range("C2").EntireColumn

objRange1.TextToColumns ObjRange2, , , , , , True

The problem is, unless I do a manual delete on "ObjRange2" then I get the
prompt,

"Do you want to replace the contents of the destination cells?"

I always want to replace and to do programatically. I am using Excel 2000
and Windows 2000.

Thanks, Duane

Try

OnError GoTo codeBreak
SetWarnings No

'before your code and at the end of your code put

codeBreak:
SetWarnings Yes

This can be dangerous so be certain to add the second part. The Goto
--
Thanks,

Patrick


"Duane L Kibby" wrote:

> Hi
> I am using the following code to replace old data with new.
>
> Set objRange1 =
> Application.Workbooks("Analysis.xls").Worksheets("Data").Range("B2").EntireColumn
> Set ObjRange2 =
> Application.Workbooks("Analysis.xls").Worksheets("Data").Range("C2").EntireColumn
>
> objRange1.TextToColumns ObjRange2, , , , , , True
>
> The problem is, unless I do a manual delete on "ObjRange2" then I get the
> prompt,
>
> "Do you want to replace the contents of the destination cells?"
>
> I always want to replace and to do programatically. I am using Excel 2000
> and Windows 2000.
>
> Thanks, Duane
>

Use
application.displayalerts = false
and
application.displayalerts = true

To change whether the prompt is shown. As this is an application level
setting you should be sure to use an error handler to reset the value in case
of a crash.

HTH

"Duane L Kibby" wrote:

> Hi
> I am using the following code to replace old data with new.
>
> Set objRange1 =
> Application.Workbooks("Analysis.xls").Worksheets("Data").Range("B2").EntireColumn
> Set ObjRange2 =
> Application.Workbooks("Analysis.xls").Worksheets("Data").Range("C2").EntireColumn
>
> objRange1.TextToColumns ObjRange2, , , , , , True
>
> The problem is, unless I do a manual delete on "ObjRange2" then I get the
> prompt,
>
> "Do you want to replace the contents of the destination cells?"
>
> I always want to replace and to do programatically. I am using Excel 2000
> and Windows 2000.
>
> Thanks, Duane
>

Disregard previous...whron app.

Application.DisplayAlerts = False

Application.DisplayAlerts = True

!!Be sure to turn the Alerts back on at the end of you proceedure!!

--
Thanks,

Patrick


"Duane L Kibby" wrote:

> Hi
> I am using the following code to replace old data with new.
>
> Set objRange1 =
> Application.Workbooks("Analysis.xls").Worksheets("Data").Range("B2").EntireColumn
> Set ObjRange2 =
> Application.Workbooks("Analysis.xls").Worksheets("Data").Range("C2").EntireColumn
>
> objRange1.TextToColumns ObjRange2, , , , , , True
>
> The problem is, unless I do a manual delete on "ObjRange2" then I get the
> prompt,
>
> "Do you want to replace the contents of the destination cells?"
>
> I always want to replace and to do programatically. I am using Excel 2000
> and Windows 2000.
>
> Thanks, Duane
>