Selecting Range with variables, Run time error 1004

Question

VBA keeps giving run time error'1004': Application defined or object defined error for the code snippet below;

Sub deneme()

    Dim a As Long
    Dim b As Integer

    For a = 12 To 13

        For b = 2 To 3

            Sheets(2).Select

            ActiveSheet.Cells(a, 1).Select

            Sheets.Add After:=Sheets(Sheets.Count)

            ActiveSheet.Name = Worksheets(2).Cells(a, 1).Value & " " & Left(Worksheets(2).Cells(a, 2).Value, 18)

            ActiveSheet.Range("A1:D1").Select

            Selection.Merge

            ActiveCell.Select

            ActiveCell.FormulaR1C1 = Worksheets(2).Cells(a, 1).Value

            With Selection
                .HorizontalAlignment = xlCenter
            End With

            Sheets(Sheets.Count).Select

            ActiveSheet.Range(Cells(b, 1), Cells(b, 2)).Select

            Selection.Merge

            ActiveCell.Select

            ActiveCell.FormulaR1C1 = Worksheets(2).Cells(8, b + 1)

            With Selection
                .HorizontalAlignment = xlCenter
            End With

            ActiveSheet.Range(Cells(b, 3), Cells(b, 4)).Select

            Selection.Merge

            ActiveCell.Select

            ActiveCell.FormulaR1C1 = Worksheets(2).Cells(a, b + 1).Value

            With Selection
                .HorizontalAlignment = xlCenter
            End With

        Next b
    Next a
End Sub

Two different variables a and b is working normally when seperated, however when i wrote them down together vba gives 1004 error.

I use the code for taking different values and arranging them in a new worksheet. This is only a small part of it which was working normally before.

QHarr QHarr · Accepted Answer · 2018-02-18T12:20:09

This line is the problem:

 ActiveSheet.Name = Worksheets(2).Cells(a, 1).Value & " " & Left(Worksheets(2).Cells(a, 2).Value, 18)

You have it inside:

For b = 2 To 3

 Next b

As you don't account for the second loop of b you are trying to create a sheet with the same name and hence the error. You need to find a way to account for the fact you are doing two loops with b instead of the outer loop for a - which you do account for.

I think you probably want to move everything that doesn't involve b, including the sheet add and name, to before the b loop.

Something like, though hard without seeing your data:

Option Explicit

Public Sub deneme()

    Dim a As Long
    Dim b As Integer

    For a = 12 To 13

        Sheets(2).Select

        ActiveSheet.Cells(a, 1).Select

        Sheets.Add After:=Sheets(Sheets.Count)

        ActiveSheet.Name = Worksheets(2).Cells(a, 1).Value & " " & Left(Worksheets(2).Cells(a, 2).Value, 18)

        ActiveSheet.Range("A1:D1").Select

        Selection.Merge

        ActiveCell.Select

        ActiveCell.FormulaR1C1 = Worksheets(2).Cells(a, 1).Value

        With Selection
            .HorizontalAlignment = xlCenter
        End With

        For b = 2 To 3

            Sheets(Sheets.Count).Select

            ActiveSheet.Range(Cells(b, 1), Cells(b, 2)).Select

            Selection.Merge

            ActiveCell.Select

            ActiveCell.FormulaR1C1 = Worksheets(2).Cells(8, b + 1)

            With Selection
                .HorizontalAlignment = xlCenter
            End With

            ActiveSheet.Range(Cells(b, 3), Cells(b, 4)).Select

            Selection.Merge

            ActiveCell.Select

            ActiveCell.FormulaR1C1 = Worksheets(2).Cells(a, b + 1).Value

            With Selection
                .HorizontalAlignment = xlCenter
            End With

        Next b
    Next a
End Sub

Primarily: You should include a test for the existence of a sheet name before trying to name a sheet. What happens if different rows in column A lead to you trying to create a sheet with the same name?

After that, you should start looking at using worksheet variables, avoiding .Select, using With statements and Option Explicit, looking at ways .FormulaR1C1can be applied to an entire range rather than in a loop to the current ActiveCell and pulling out repeated lines of code into functions. Could also used typed function Left$ rather than Left.

The code I posted also depends on what you actually wanted to happen if the same sheet name came up.. did you want to create a new name or did you want to use the existing sheet. All stuff error handling on sheet and a test for sheet exists could deal with.

Selecting Range with variables, Run time error 1004

1 Answers