This simple (?) code crashes

[blumesan]

I am very much a beginner at coding in VBA. However I believe the follow code is fairly straightforward but it always crashes in
Sub Cleanup() at the line "InTextRange.Select"
with "Run time error 1004"

Please Login or Register  to view this content.

Any help much appreciated.

[Norie]

Not sure why you've separated the code out like you have, 2 subs, global declarations etc.

Anyway, try this.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi

Works OK for me.

Is the INPUT-sRGB sheet hidden by any chance?

[Norie]

Richard

How did you run the code?

Which sub to you execute first?

[LJMetzger]

Nice job Norie.

blumesan,

Your code deletes cells and moves other cells to take the place of the deleted cells.
If that is NOT what you want, you may want:

Please Login or Register  to view this content.

Lewis

BTW, the poster's code worked for me in Excel 2003. Ran 'Define_Variables(), then ran Cleanup().

[Richard Buttrey]

Hi Norie,

I ran the Define_Variables first, then the Cleanup.

It only tripped up with the error mentioned when I hid the sheet in question.

[Norie]

I got this to work.

Please Login or Register  to view this content.

[blumesan]

You guys are terrific. So much help in only a couple of hours.

First, Norie: The code is written this way because there are (or will be) many other subroutines which use the global variables. Your code using With statement in the "Define_Variables()" is cleaner. Also it works without crashing BUT trying both IntextRange.Delete or IntextRange.ClearContents has no effect on the spreadsheet.

Other questions: the Sub Define_Variables is always run first. No sheets are hidden.

If I may impose on your kindness, let me explain a bit more about the project:

It begins by importing a text file into the spreadsheet; then goes on to make several calculations. When this finishes and results are saved, I wish to repeat the process with another text file, BUT: A connection has already been established and this connection must be terminated before I can import another text file into the same space. I have managed to accomplish this with the following macro, but it won't work when I try to use the macro code in a VBA module.

Please Login or Register  to view this content.

So again, thanks and any additional help greatly appreciated.

[Norie]

How are you importing the data?

What calculations are you performing on the data?

How are you saving the results?

[blumesan]

How are you importing the data?

What calculations are you performing on the data?

How are you saving the results?

Importing is done via the Data > Import from Text with code snippet:

Please Login or Register  to view this content.

Many calculation which, at this point, are not relevant.

Saving is done by selecting data in worksheet and Save As PDF file.

I should have mentioned earlier, I am using Microsoft Office Excel 2007

[blumesan]

To all who responded, many thanks.
It appears that your interest has now petered out, so I take the liberty of bumping this thread.

Please understand that I am not here just to find a solution to this particular problem; I also wish to learn something. Twenty years ago I was a pretty good C++ coder, although I never did it for a living. Time and age (82 yrs) have much dimmed my knowledge; but given what I do remember, VBA by comparison is a very opaque language. Why, for example, does Norie's first revision of my code succeed in preventing an error? And why does it not have the desired effect?

[blumesan]

To all who responded, many thanks.
It appears that your interest has now petered out, so I take the liberty of bumping this thread.

Please understand that I am not here just to find a solution to this particular problem; I also wish to learn something. Twenty years ago I was a pretty good C++ coder, although I never did it for a living. Time and age (82 yrs) have much dimmed my knowledge; but given what I do remember, VBA by comparison is a very opaque language. Why, for example, does Norie's first revision of my code succeed in preventing an error? And why does it not have the desired effect?

[Norie]

I don't think it's our interest that's petered out, more like the forum is petering out, for us all.

As for why the code isn't working, it's kind of hard to tell without some sample files to work with.

One thought I just had was that instead of using QueryTables you could use OpenTextFile.