CODEWITHSUNDEEP
vbaexcel
thara
thara

Reputation: 43

Excel VBA loop through worksheets fails

Edit: I took all you advice and edited my code. Now it works!!! Thank you.

Here is the new code:

Sub WorksheetLoop()

    Dim AllWorksheets As Integer

    Dim Worksheet As Integer

    AllWorksheets = ActiveWorkbook.Worksheets.Count
    For Worksheet = 2 To AllWorksheets

        Sheets(1).Select

        Cells(10, Worksheet).Value = Sheets(Worksheet).TextBoxes(2).Text
        Cells(13, Worksheet).Value = Sheets(Worksheet).TextBoxes(3).Text
        Cells(18, Worksheet).Value = Sheets(Worksheet).TextBoxes(1).Text
        Cells(24, Worksheet).Value = Sheets(Worksheet).TextBoxes(5).Text
        Cells(34, Worksheet).Value = Sheets(Worksheet).TextBoxes(6).Text
        Cells(34, Worksheet).Value = Sheets(Worksheet).TextBoxes(4).Text

    Next Worksheet
End Sub

Original Problem

So there is an excel document, which contains an amount of worksheets. On the first sheet an overview should be created by the script. It should start in the 2nd worksheet and should write the content of the textboxes (please don't ask why there are textboxes...) to Cell B10, B13, anso so on. Then the script should go to worksheet 3 and the content of the textboxes should go to C10, C13,... You get the idea... I know that this is only possible to Z....

But why do I keep getting error messages?

My VBA knowlage is very small, so sorry for obvious errors.

Edit: I took the advice about the spaces around & But I still get "object doesn't support this property or method"

Sub WorksheetLoop()

    Dim AllWorksheets As Integer

    Dim Worksheet As Integer

    AllWorksheets = ActiveWorkbook.Worksheets.Count

    For Worksheet = 2 To AllWorksheets
        For CellAscii = 66 To (AllWorksheet + 66)
            Cell = Chr(CellAscii)
            Sheets(1).Select
            Range(Cell & "10").Value = Sheets(Worksheet).TextBox2.Text
            Range(Cell & "13").Value = Sheets(Worksheet).TextBox3.Text
            Range(Cell & "18").Value = Sheets(Worksheet).TextBox1.Text
            Range(Cell & "24").Value = Sheets(Worksheet).TextBox5.Text
            Range(Cell & "30").Value = Sheets(Worksheet).TextBox6.Text
            Range(Cell & "34").Value = Sheets(Worksheet).TextBox4.Text
        Next CellAscii
    Next Worksheet
End Sub

Upvotes: 1

Views: 2544

Answers (3)

SimonSaaS
SimonSaaS

Reputation: 56

Just try the following when trying to access textboxes:

Sheets("SheetName").TextBoxes("TextBox Name").Text

Verify that your "SheetName" and "TextBox Name" are correct.

Hope this was usefull for you.

Upvotes: 4

Siddharth Rout
Siddharth Rout

Reputation: 149287

The main error in your code is that there is no space before and after &

Change Range(Cell&"10").Value to Range(Cell & "10").Value. Similarly for the rest and your code will run just fine :)

Upvotes: 2

Stefan Steiger
Stefan Steiger

Reputation: 82166

Range doesn't take a reference of schema Ay, it takes one with RyCx.
Anyway use SheetX.Cell to access a particular cell in a particular row and column.

You loop through cells like this:

Sub MyLoop()
    For RowCounter = 1 To 20

        For ColumnCounter = 1 To 20
            Set curCell = Worksheets("Sheet1").Cells(RowCounter , ColumnCounter)
            If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
        Next ColumnCounter 
    Next RowCounter 
End Sub

Upvotes: 2

Related Questions