| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro error when formatting columns
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro error when formatting columns
On Oct 28, 11:24*am, "JLGWhiz" wrote:
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro error when formatting columns
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. "dhermus" wrote in message ... On Oct 28, 11:24 am, "JLGWhiz" wrote: 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- Hide quoted text - - Show quoted text - 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro error when formatting columns
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 "dhermus" wrote in message ... On Oct 28, 11:24 am, "JLGWhiz" wrote: 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- Hide quoted text - - Show quoted text - 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro error when formatting columns
On Oct 28, 4:02*pm, "JLGWhiz" wrote:
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 "dhermus" wrote in message ... On Oct 28, 11:24 am, "JLGWhiz" wrote: 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- Hide quoted text - - Show quoted text - 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 - 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro error when formatting columns
On Oct 30, 9:42*am, dhermus wrote:
On Oct 28, 4:02*pm, "JLGWhiz" wrote: 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 "dhermus" wrote in message .... On Oct 28, 11:24 am, "JLGWhiz" wrote: 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- Hide quoted text - - Show quoted text - 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 - 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 - to clarify, these columns are grouped, not hidden. The were grouped using the data, group & ungroup menu option. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error message when adding 2 columns with Macro | Excel Programming | |||
| Right() error changing columns in macro | Excel Programming | |||
| error: Excel encountered an error and had to remove some formatting to avoid corrupting the workbook; please recheck your formatting carefully | Excel Programming | |||
| Macro for formatting columns with dates, weekdays, and widths? | Excel Programming | |||
| First four columns lose formatting when DAO macro is run | Excel Programming | |||
Linear Mode
