macro error when formatting columns

D

dhermus

I am using the following script to format columns in two worksheets
upon opening a workbook. There will not always be data in the selected
columns. My problem is the macro errors when there is no data
present. How can I bypass the errors and complete the macro for the
other columns that do have data?


Sheets("All Other Funds Centers").Select
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"),
DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"),
DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True


Range("A57").Select
Sheets("Spec Init - 10059 & 10080").Select
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"),
DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"),
DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("A57").Select
End Sub
 
J

JLGWhiz

This is an example of one method:

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
<> 0 Then

Sheets("All Other Funds Centers").Columns("E:E").TextToColumns _
Destination:=Range("E1"),DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("All Other Funds Centers").Columns("G:G").TextToColumns _
Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End If

I took the liberty of eliminating the Select and Selecion from your code
since they are not needed to make the code work. The If statement, using
CountA function, will check to see if there is data in the two columns
before you try to do anything with them. If there is no data it moves on to
the next block of code. You can do the same thing with the rest of your
code. Since this particular example checks both columns E and G, it could
still throw an error if one column has data but the other does not. To get
around that, you would need to make an If...Then statement for each column.
 
D

dhermus

This is an example of one method:

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
   And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
   <> 0 Then

Sheets("All Other Funds Centers").Columns("E:E").TextToColumns _
   Destination:=Range("E1"),DataType:=xlDelimited, _
   TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
   Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
   Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("All Other Funds Centers").Columns("G:G").TextToColumns _
   Destination:=Range("G1"), DataType:=xlDelimited, _
   TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
   Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
   Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End If

I took the liberty of eliminating the Select and Selecion from your code
since they are not needed to make the code work.  The If statement, using
CountA function, will check to see if there is data in the two columns
before you try to do anything with them.  If there is no data it moves on to
the next block of code.  You can do the same thing with the rest of your
code.  Since this particular example checks both columns E and G, it could
still throw an error if one column has data but the other does not.  Toget
around that, you would need to make an If...Then statement for each column.








- Show quoted text -
Click to expand...

Thank you, this looks good and appears to be the correct solution, but
I am still getting a "object does not support this property or method"
error, highlighting the first lines of code.

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
<> 0 Then
 
J

JLGWhiz

Sorry about that. Need WorksheetFunction in there.

If Sheets("All Other Funds
Centers").WorksheetFunction.CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds
Centers").WorksheetFunction.CountA(Columns("G:G")) _
<> 0 Then

And remember that you can still get an error if one column has data and the
other does not. So you probably should make two a separate If statement for
each column, instead of using the If... And ... Then.

This is an example of one method:

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
<> 0 Then

Sheets("All Other Funds Centers").Columns("E:E").TextToColumns _
Destination:=Range("E1"),DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("All Other Funds Centers").Columns("G:G").TextToColumns _
Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End If

I took the liberty of eliminating the Select and Selecion from your code
since they are not needed to make the code work. The If statement, using
CountA function, will check to see if there is data in the two columns
before you try to do anything with them. If there is no data it moves on
to
the next block of code. You can do the same thing with the rest of your
code. Since this particular example checks both columns E and G, it could
still throw an error if one column has data but the other does not. To get
around that, you would need to make an If...Then statement for each
column.








- Show quoted text -
Click to expand...

Thank you, this looks good and appears to be the correct solution, but
I am still getting a "object does not support this property or method"
error, highlighting the first lines of code.

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
<> 0 Then
 
J

JLGWhiz

This is the correct syntax:

If WorksheetFunction.CountA(Sheets("All Other Funds Centers") _
..Columns("E:E")) <> 0 And WorksheetFunction. _
CountA(Sheets("All Other Funds Centers").Columns("G:G")) <> 0 Then



This is an example of one method:

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
<> 0 Then

Sheets("All Other Funds Centers").Columns("E:E").TextToColumns _
Destination:=Range("E1"),DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Sheets("All Other Funds Centers").Columns("G:G").TextToColumns _
Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

End If

I took the liberty of eliminating the Select and Selecion from your code
since they are not needed to make the code work. The If statement, using
CountA function, will check to see if there is data in the two columns
before you try to do anything with them. If there is no data it moves on
to
the next block of code. You can do the same thing with the rest of your
code. Since this particular example checks both columns E and G, it could
still throw an error if one column has data but the other does not. To get
around that, you would need to make an If...Then statement for each
column.








- Show quoted text -
Click to expand...

Thank you, this looks good and appears to be the correct solution, but
I am still getting a "object does not support this property or method"
error, highlighting the first lines of code.

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
<> 0 Then
 
D

dhermus

This is the correct syntax:

If WorksheetFunction.CountA(Sheets("All Other Funds Centers") _
.Columns("E:E")) <> 0 And WorksheetFunction. _
CountA(Sheets("All Other Funds Centers").Columns("G:G")) <> 0 Then










Thank you, this looks good and appears to be the correct solution, but
I am still getting a "object does not support this property or method"
error, highlighting the first lines of code.

If Sheets("All Other Funds Centers").CountA(Columns("E:E")) <> 0 _
And Sheets("All Other Funds Centers").CountA(Columns("G:G")) _
<> 0 Then- Hide quoted text -

- Show quoted text -
Click to expand...

It took me awhile to get back to this, it is just what I needed,
Thanks.

One other question,
I have column in multiple worksheets of a workbook that are hidden.
I tried recording a macro to unhide the columns, but it error out each
time I try to run it. The columns are alway the same in the
worksheets, O-AR. Am I missing something.
 
D

dhermus

It took me awhile to get back to this, it is just what I needed,
Thanks.

One other question,
I have column in multiple worksheets of a workbook that are  hidden.
I tried recording a macro to unhide the columns, but it error out each
time I try to run it.  The columns are alway the same in the
worksheets, O-AR.  Am I missing something.- Hide quoted text -

- Show quoted text -
Click to expand...

to clarify, these columns are grouped, not hidden. The were grouped
using the data, group & ungroup menu option.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text To Columns On A Different Sheet 12
Date Format 4
Macro delimiting text if column has value 3
defining TextToColumns with variable 2
Text to Columns formatting 4
Recorded Macro 1
Excel VBA help: Text file formatting 19
Text to Columns (general) reverting at end of macro 2

Top