macro error when formatting columns
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.
"dhermus" wrote in message
...
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
|