Loop through all Sheets + Text in Cell to Numbers

Question

Im running an Excel sheet that calculates the amout of parts for a switching cabinet. The Data for that calculation is exported from a 3rd Party program called "E-Plan". In the first column (A) the Product numbers are displayed. In the second column the amount for each cabinet is displayed but formated as text. Ive written a short macro that should Loop through each sheet of the workbook except for those with the Name "Übersicht", "Rechner" or "Bestellmenge" and changes the Format of column "B" to "Zahl" or Numbers. However, the macro only Loops through the sheet im in and even through the Sheets ist not supposed to Loop through.

Sub Test()
Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
       If ws.Name = "Übersicht" Or _
       ws.Name = "Rechner" Or _
       ws.Name = "Bestellmenge" Then


       Else

       Columns("B:B").Select
       Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
               TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
               :=Array(1, 1), TrailingMinusNumbers:=True
       End If
    Next ws

End Sub

score 1 · Accepted Answer · answered Jun 15 '18 at 06:55

Try this...

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets        
   If ws.Name <> "Überischt" And ws.Name <> "Rechner" And ws.Name <> "Bestellmenge" Then
        ws.Columns("B").TextToColumns Destination:=ws.Range("B1"), DataType:=xlDelimited, _
               TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
               :=Array(1, 1), TrailingMinusNumbers:=True
   End If
Next ws
End Sub

@Feinberg You're welcome! But you accepted another answer. ;) Avoid using `Select` in the code. — Subodh Tiwari sktneer, Jun 15 '18 at 07:05

Chris Melville · Answer 2 · 2018-06-15T06:52:02.133

0

Columns("B:B").Select

... is operating on the ActiveSheet. You want:

ws.Columns("B:B").Select

Additionally, you have nothing between your “Then” and “Else” statements. So nothing will happen if the worksheet name is one of the 3 you named. Your operation will only execute if the sheet it NOT one of those named! Is this what you intended? If not, delete the “Else” line.

edited Jun 15 '18 at 06:52

answered Jun 15 '18 at 06:48

Chris Melville

1,476
1
14
30

This spits out a runtime error "1004" The select-method of the range object could not be executed. Sorry for the bad Translation btw. – Feinberg Jun 15 '18 at 06:51
Probably you need `Destination:=ws.Range("B1")` too (specify a sheet for **every** `Range`, `Column` etc. And instead of selecting directly use `TextToColumns` on the column like `ws.Columns("B:B").TextToColumns …` see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Pᴇʜ Jun 15 '18 at 06:51
I want the Macro to "skip" the 3 Sheets i named because those should not be formated. So it skips These Sheets and moves on. Atleast thats what i intended to to. – Feinberg Jun 15 '18 at 06:56
Then the more conventional logic would be to check that the sheet name is NOT one of the 3, and enclose your code within the main If statement. Your use of Else is unconventional and therefore misleading. – Chris Melville Jun 15 '18 at 07:01
1

At first i didnt quite get right to check if ist NOT the Name of the 3. However sktneer got it right and im also thankful for your help (Both of yours). Have a good day ! – Feinberg Jun 15 '18 at 07:05

Loop through all Sheets + Text in Cell to Numbers

2 Answers2