The data I'm working with is from a .txt file which was created by exporting 
a .pdf to a text file.

I copy all of the text from the .txt file and paste it into a column in Excel.

When trying to reference any column, row, or range on the sheet with the 
pasted text, I get the following error: Run-time error '1004': 
Application-defined or object-defined error.

Sample code:
Sheets("Sheet5").Select ' <this works fine
Columns("A:A").Select ' <this gets the error

I've tried pasting the text with other paste special options. I've tried 
referencing the text on another sheet and running the code off of it. I've 
tried using the Text formula to reformat the text. But, none of this seems to 
be working. I cannot select any area on a sheet that has or references this 
text... and therefore cannot use it in my code.

Double check your spelling and symbols on the line of code that produces the 
error.  There is no reason for the error, otherwise.



"Excel Curious" <ExcelCurious@discussions.microsoft.com> wrote in message 
news:EC3617D0-5A2E-4D46-9B98-FCF9A34C2975@microsoft.com...
> The data I'm working with is from a .txt file which was created by 
> exporting
> a .pdf to a text file.
>
> I copy all of the text from the .txt file and paste it into a column in 
> Excel.
>
> When trying to reference any column, row, or range on the sheet with the
> pasted text, I get the following error: Run-time error '1004':
> Application-defined or object-defined error.
>
> Sample code:
> Sheets("Sheet5").Select ' <this works fine
> Columns("A:A").Select ' <this gets the error
>
> I've tried pasting the text with other paste special options. I've tried
> referencing the text on another sheet and running the code off of it. I've
> tried using the Text formula to reformat the text. But, none of this seems 
> to
> be working. I cannot select any area on a sheet that has or references 
> this
> text... and therefore cannot use it in my code. 

On the chance that it's not a mis-spelling or syntax error, you could simply 
try Range("A:A").Select instead of Columns("A:A").Select.

Also just as food for thought/learning...  it's generally not necessary to 
"select" objects in order to manipulate them with VBA.  e.g.:

Range("A:A").Copy

is the same as

Range("A:A").Select
Selection.Copy




"Excel Curious" wrote:

> The data I'm working with is from a .txt file which was created by exporting 
> a .pdf to a text file.
> 
> I copy all of the text from the .txt file and paste it into a column in Excel.
> 
> When trying to reference any column, row, or range on the sheet with the 
> pasted text, I get the following error: Run-time error '1004': 
> Application-defined or object-defined error.
> 
> Sample code:
> Sheets("Sheet5").Select ' <this works fine
> Columns("A:A").Select ' <this gets the error
> 
> I've tried pasting the text with other paste special options. I've tried 
> referencing the text on another sheet and running the code off of it. I've 
> tried using the Text formula to reformat the text. But, none of this seems to 
> be working. I cannot select any area on a sheet that has or references this 
> text... and therefore cannot use it in my code.

There is no misspelling and I have tried using Range in place of Columns. The 
exact same code works on other worksheets that do not have the pasted text. 
I'm using Select because the next line of the code runs a find on the 
selection.

Is it possible the text from the .txt file is somehow corrupt (although it 
looks fine)? And if so is there a way to un-corrupt it?

"B Lynn B" wrote:

> On the chance that it's not a mis-spelling or syntax error, you could simply 
> try Range("A:A").Select instead of Columns("A:A").Select.
> 
> Also just as food for thought/learning...  it's generally not necessary to 
> "select" objects in order to manipulate them with VBA.  e.g.:
> 
> Range("A:A").Copy
> 
> is the same as
> 
> Range("A:A").Select
> Selection.Copy
> 
> 
> 
> 
> "Excel Curious" wrote:
> 
> > The data I'm working with is from a .txt file which was created by exporting 
> > a .pdf to a text file.
> > 
> > I copy all of the text from the .txt file and paste it into a column in Excel.
> > 
> > When trying to reference any column, row, or range on the sheet with the 
> > pasted text, I get the following error: Run-time error '1004': 
> > Application-defined or object-defined error.
> > 
> > Sample code:
> > Sheets("Sheet5").Select ' <this works fine
> > Columns("A:A").Select ' <this gets the error
> > 
> > I've tried pasting the text with other paste special options. I've tried 
> > referencing the text on another sheet and running the code off of it. I've 
> > tried using the Text formula to reformat the text. But, none of this seems to 
> > be working. I cannot select any area on a sheet that has or references this 
> > text... and therefore cannot use it in my code.

You didn't paste the procedure name that holds this code--or where that
procedure is located.

If the code is in a General module, then I would expect it to work ok.

But if the code is in a worksheet module (some sort of event/control
procedure???), then it won't.

If the code is in a General module, then the unqualified range (Columns() would
refer to the active sheet.

If the code is in the worksheet, then the unqualified range belongs to the
worksheet that owns the code--and I bet that's not Sheet5.

Try:

Worksheets("Sheet5").select
Worksheets("Sheet5").columns("A:A").select

or (less typing):
with worksheets("Sheet5")
   .select
   .columns("A:A").select
End with

But even better would be to drop the selection stuff and just work on it
directly.

with worksheets("Sheet5")
   .columns("A:A").numberformat = "General"  'for instance
   ...
end with

Excel Curious wrote:
> 
> The data I'm working with is from a .txt file which was created by exporting
> a .pdf to a text file.
> 
> I copy all of the text from the .txt file and paste it into a column in Excel.
> 
> When trying to reference any column, row, or range on the sheet with the
> pasted text, I get the following error: Run-time error '1004':
> Application-defined or object-defined error.
> 
> Sample code:
> Sheets("Sheet5").Select ' <this works fine
> Columns("A:A").Select ' <this gets the error
> 
> I've tried pasting the text with other paste special options. I've tried
> referencing the text on another sheet and running the code off of it. I've
> tried using the Text formula to reformat the text. But, none of this seems to
> be working. I cannot select any area on a sheet that has or references this
> text... and therefore cannot use it in my code.

-- 

Dave Peterson

I think Dave might be closer to the problem with his analogy.  That 
particular error occurs because VBA cannot compile the code, not because of 
corrupted data within a cell.  It does not even test the cell content at 
that point in the execution.  It is only looking for an object that the code 
has described.  As Dave pointed out, if you are not running the code from 
the public module of the same workbook, then the compiler does not know 
which column A you mean, so you need to add the workbook name to that line 
of code so that the compiler knows where to look for column A.



"Excel Curious" <ExcelCurious@discussions.microsoft.com> wrote in message 
news:7D4F1EC4-DD45-4E54-8A4A-EF8D109F56B2@microsoft.com...
> There is no misspelling and I have tried using Range in place of Columns. 
> The
> exact same code works on other worksheets that do not have the pasted 
> text.
> I'm using Select because the next line of the code runs a find on the
> selection.
>
> Is it possible the text from the .txt file is somehow corrupt (although it
> looks fine)? And if so is there a way to un-corrupt it?
>
> "B Lynn B" wrote:
>
>> On the chance that it's not a mis-spelling or syntax error, you could 
>> simply
>> try Range("A:A").Select instead of Columns("A:A").Select.
>>
>> Also just as food for thought/learning...  it's generally not necessary 
>> to
>> "select" objects in order to manipulate them with VBA.  e.g.:
>>
>> Range("A:A").Copy
>>
>> is the same as
>>
>> Range("A:A").Select
>> Selection.Copy
>>
>>
>>
>>
>> "Excel Curious" wrote:
>>
>> > The data I'm working with is from a .txt file which was created by 
>> > exporting
>> > a .pdf to a text file.
>> >
>> > I copy all of the text from the .txt file and paste it into a column in 
>> > Excel.
>> >
>> > When trying to reference any column, row, or range on the sheet with 
>> > the
>> > pasted text, I get the following error: Run-time error '1004':
>> > Application-defined or object-defined error.
>> >
>> > Sample code:
>> > Sheets("Sheet5").Select ' <this works fine
>> > Columns("A:A").Select ' <this gets the error
>> >
>> > I've tried pasting the text with other paste special options. I've 
>> > tried
>> > referencing the text on another sheet and running the code off of it. 
>> > I've
>> > tried using the Text formula to reformat the text. But, none of this 
>> > seems to
>> > be working. I cannot select any area on a sheet that has or references 
>> > this
>> > text... and therefore cannot use it in my code. 

That was it. I changed the code to include the worksheet in the column 
selection and it worked. I've written a lot of code and haven't encountered 
this issue before. Thank you for your help.

FYI my code was in a button on the worksheet module for sheet4.

"Dave Peterson" wrote:

> You didn't paste the procedure name that holds this code--or where that
> procedure is located.
> 
> If the code is in a General module, then I would expect it to work ok.
> 
> But if the code is in a worksheet module (some sort of event/control
> procedure???), then it won't.
> 
> If the code is in a General module, then the unqualified range (Columns() would
> refer to the active sheet.
> 
> If the code is in the worksheet, then the unqualified range belongs to the
> worksheet that owns the code--and I bet that's not Sheet5.
> 
> Try:
> 
> Worksheets("Sheet5").select
> Worksheets("Sheet5").columns("A:A").select
> 
> or (less typing):
> with worksheets("Sheet5")
>    .select
>    .columns("A:A").select
> End with
> 
> But even better would be to drop the selection stuff and just work on it
> directly.
> 
> with worksheets("Sheet5")
>    .columns("A:A").numberformat = "General"  'for instance
>    ...
> end with
> 
> Excel Curious wrote:
> > 
> > The data I'm working with is from a .txt file which was created by exporting
> > a .pdf to a text file.
> > 
> > I copy all of the text from the .txt file and paste it into a column in Excel.
> > 
> > When trying to reference any column, row, or range on the sheet with the
> > pasted text, I get the following error: Run-time error '1004':
> > Application-defined or object-defined error.
> > 
> > Sample code:
> > Sheets("Sheet5").Select ' <this works fine
> > Columns("A:A").Select ' <this gets the error
> > 
> > I've tried pasting the text with other paste special options. I've tried
> > referencing the text on another sheet and running the code off of it. I've
> > tried using the Text formula to reformat the text. But, none of this seems to
> > be working. I cannot select any area on a sheet that has or references this
> > text... and therefore cannot use it in my code.
> 
> -- 
> 
> Dave Peterson
> .
>