Expected end with ошибка

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#1

10.04.2018 18:17:08

Код
Sub МАКС()
'
If Worksheets("Макс").PivotTables.Count > 0 Then
    Worksheets("Макс").PivotTables("Свод").TableRange2.Clear
End If
Dim ra As Range, delra As Range, ТекстДляПоиска As String
    Application.ScreenUpdating = False    ' отключаем обновление экрана
    ТекстДляПоиска = "Комплекты"    ' удаляем строки с таким текстом
    ' перебираем все строки в используемом диапазоне листа
    For Each ra In ActiveSheet.UsedRange.Rows
        ' если в строке найден искомый текст
        If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then
            ' добавляем строку в диапазон для удаления
            If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
        End If
    Next
    ' если подходящие строки найдены - удаляем их
    If Not delra Is Nothing Then delra.EntireRow.Delete
    With ActiveSheet
    .Range("E3:F" & .Cells(.Rows.Count, "F").End(xlUp).Row).ClearContents
    .Range("E2:F2").AutoFill .Range("E2:F" & .Cells(.Rows.Count, "D").End(xlUp).Row)
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Макс!R1C4:R1048576C6", Version:=6).CreatePivotTable _
        TableDestination:="Макс!R2C9", TableName:="Сводная таблица", DefaultVersion:=6
    Sheets("Макс").Select
    Cells(2, 9).Select
With ActiveSheet.PivotTables("Сводная таблица").PivotFields("Вид тары")
        .Orientation = xlRowField
        .Position = 1
End With
    ActiveSheet.PivotTables("Сводная таблица").AddDataField ActiveSheet. _
    PivotTables("Сводная таблица").PivotFields("Количество"), _
    "Количество по полю Количество", xlCount
With ActiveSheet.PivotTables("Сводная таблица5").PivotFields( _
        "Количество по полю Количество")
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0,00%"
End With
    ActiveCell.Offset(1, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(Calculation!R[8]C[-7],'Макс'!C[1]:C[2],2,0),0)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A21"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A21").Select
    Selection.Copy
    Sheets("Calculation").Select
            
End Sub
 

Hugo

Пользователь

Сообщений: 23371
Регистрация: 22.12.2012

В 22-ю строку добавьте то, о чём Вам говорит отладчик.

 

RAN

Пользователь

Сообщений: 7139
Регистрация: 21.12.2012

Берем лупу, и считаем количество With и End With.

PS Но какой-то странный Ёжуж (или Ужёж)

Изменено: RAN10.04.2018 18:24:37

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

Добавил, макрос выдает ошибку Run time error ‘9’ : Subscript out of range ссылаясь на 3-ю строку

 

Ігор Гончаренко

Пользователь

Сообщений: 13859
Регистрация: 01.01.1970

#5

10.04.2018 19:32:03

Цитата
BMSs написал:
выдает ошибку Run time error ‘9’ : Subscript out of range ссылаясь на 3-ю строку

закомментируйте 3, 4 и 5 строки Вашего кода
оптимальный вариант — закомментировать все, кроме строк:
Sub МАКС()
и
End Sub

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#6

10.04.2018 21:16:00

Цитата
vikttur написал:
Макрос для удаления лишних строк.
 

Юрий М

Модератор

Сообщений: 60761
Регистрация: 14.09.2012

Контакты см. в профиле

Не писал такого Виктор )
BMSs,  Вы не умеете пользоваться цитированием. И не пользуйтесь — ответить можно и без него. А с названием у Вас опять беда: что можно из него понять? Единственное, это то,что у Вас имеется такой макрос. И что? Неужели нельзя кратко сформулировать суть проблемы?

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

Суть в появлении ошибки при запуске макроса, макрос в студии, куда еще короче?

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

Вопрос закрыт, всем спасибо!

 

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

Поделиться решением забыли.

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#11

12.04.2018 08:30:03

Код
 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Макс!A1:F2000", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=Worksheets("Макс").Range("I2"), TableName:="Сводная Таблица", DefaultVersion:=xlPivotTableVersion14
      Application.GoTo Worksheets("Макс").Range("I2")
 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#12

12.04.2018 08:30:42

Изменены строки с 23 по 27.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
Private Sub CommandButton1_Click()
'Декларация переменных
Dim fam, pred, exam, ocenk, resault, grup, semestr As String
Dim summa As Single
Dim nomer As Integer
Dim data As Date
 
nomer = Application.CountA(ActiveSheet.Columns(1)) + 1
With UserForm1
 
If [Фамилия] = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If [Группа] = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox1.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox2.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox3.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox4.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox4.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
 
fam = [Фамилия]
grup = [Группа]
pred = ComboBox1.Value
ocenk = ComboBox2.Value
resault = ComboBox3.Value
exam = ComboBox4.Value
semestr = ComboBox5.Value
 
 
If IsDate([Дата]) = False Then
MsgBox "", vbExclamation
Exit Sub
End If
 
data = [Дата]
 
With ActiveSheet
.Cells(nomer, 1).Value = fam
.Cells(nomer, 2).Value = pred
.Cells(nomer, 3).Value = exam
.Cells(nomer, 4).Value = ocenk
.Cells(nomer, 5).Value = resault
.Cells(nomer, 6).Value = grup
.Cells(nomer, 7).Value = semestr
.Cells(nomer, 8).Value = summa
.Cells(nomer, 9).Value = nomer
.Cells(nomer, 10).Value = data
End With
ClearForm
End Sub
 
Private Sub ClearForm()
Unload UserForm1
UserForm1.Show
End Sub
 
 
Private Sub UserForm_Initialize()
ComboBox1.AddItem "Информатика"
ComboBox1.AddItem "Высшая  математика"
ComboBox1.AddItem "Физика"
ComboBox1.AddItem "История"
ComboBox1.AddItem "Психология"
ComboBox1.AddItem "Физическое воспитание"
ComboBox1.AddItem "Иследовательская работа"
ComboBox2.AddItem "Отлично"
ComboBox2.AddItem "Хорошо"
ComboBox2.AddItem "Удовлетворительно"
ComboBox2.AddItem "Не сдал"
ComboBox2.AddItem "Зачет"
ComboBox2.AddItem "Незачет"
ComboBox3.AddItem "сессия  закрыта  с  обычной  стипендией"
ComboBox3.AddItem "закрыта  с  повышенной стипендией"
ComboBox3.AddItem "закрыта  с  повышенной стипендией"
ComboBox4.AddItem "Экзамен"
ComboBox4.AddItem "Зачет"
ComboBox5.AddItem "I"
ComboBox5.AddItem "II"
ComboBox5.AddItem "III"
ComboBox5.AddItem "IV"
ComboBox5.AddItem "V"
ComboBox5.AddItem "VI"
ComboBox5.AddItem "VIII"
ComboBox5.AddItem "IX"
ComboBox5.AddItem "X"
End Sub

Short version

Add spaces between operators and their operands. Problem solved.


Long version

Any identifier that is immediately followed by a &, like name& and affiliation&, is interpreted as a Long variable, so the lack of whitespace in front of what’s meant to be concatenation operators is causing a parse error, because VBA doesn’t know what literal expression could possibly follow the Combined = name& assignment — the instruction is complete as it is; the only token that should be where " (" is, is an end-of-statement token:

Expected: end of statement

Says exactly that. Everything before " (" is a perfectly valid instruction, except it’s not terminated.

So it’s not the " (", it’s the type hints. Insert spaces to separate the operators from the operands, and you’ll fix the problem. More explicitness couldn’t hurt, either:

Option Explicit

Public Function Combined(ByVal name As String, ByVal affiliation As String, email As String) As String
    Combined = name & " (" & affiliation & ") " & "<" & email & ">"
End Function

When a type isn’t specified, a declaration is implicitly Variant, which incurs some unnecessary run-time overhead.

When ByVal isn’t specified, parameters are passed ByRef by default, which means the function could be assigning to the parameters.

You could also implement the function like so:

Combined = Join(Array(name, "(" & affiliation & ")", "<" & email & ">"), " ")

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#1

10.04.2018 18:17:08

Код
Sub МАКС()
'
If Worksheets("Макс").PivotTables.Count > 0 Then
    Worksheets("Макс").PivotTables("Свод").TableRange2.Clear
End If
Dim ra As Range, delra As Range, ТекстДляПоиска As String
    Application.ScreenUpdating = False    ' отключаем обновление экрана
    ТекстДляПоиска = "Комплекты"    ' удаляем строки с таким текстом
    ' перебираем все строки в используемом диапазоне листа
    For Each ra In ActiveSheet.UsedRange.Rows
        ' если в строке найден искомый текст
        If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then
            ' добавляем строку в диапазон для удаления
            If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
        End If
    Next
    ' если подходящие строки найдены - удаляем их
    If Not delra Is Nothing Then delra.EntireRow.Delete
    With ActiveSheet
    .Range("E3:F" & .Cells(.Rows.Count, "F").End(xlUp).Row).ClearContents
    .Range("E2:F2").AutoFill .Range("E2:F" & .Cells(.Rows.Count, "D").End(xlUp).Row)
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Макс!R1C4:R1048576C6", Version:=6).CreatePivotTable _
        TableDestination:="Макс!R2C9", TableName:="Сводная таблица", DefaultVersion:=6
    Sheets("Макс").Select
    Cells(2, 9).Select
With ActiveSheet.PivotTables("Сводная таблица").PivotFields("Вид тары")
        .Orientation = xlRowField
        .Position = 1
End With
    ActiveSheet.PivotTables("Сводная таблица").AddDataField ActiveSheet. _
    PivotTables("Сводная таблица").PivotFields("Количество"), _
    "Количество по полю Количество", xlCount
With ActiveSheet.PivotTables("Сводная таблица5").PivotFields( _
        "Количество по полю Количество")
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0,00%"
End With
    ActiveCell.Offset(1, -1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(Calculation!R[8]C[-7],'Макс'!C[1]:C[2],2,0),0)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A21"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A21").Select
    Selection.Copy
    Sheets("Calculation").Select
            
End Sub
 

Hugo

Пользователь

Сообщений: 23100
Регистрация: 22.12.2012

В 22-ю строку добавьте то, о чём Вам говорит отладчик.

 

RAN

Пользователь

Сообщений: 7081
Регистрация: 21.12.2012

Берем лупу, и считаем количество With и End With.

PS Но какой-то странный Ёжуж (или Ужёж)

Изменено: RAN10.04.2018 18:24:37

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

Добавил, макрос выдает ошибку Run time error ‘9’ : Subscript out of range ссылаясь на 3-ю строку

 

Ігор Гончаренко

Пользователь

Сообщений: 13125
Регистрация: 01.01.1970

#5

10.04.2018 19:32:03

Цитата
BMSs написал:
выдает ошибку Run time error ‘9’ : Subscript out of range ссылаясь на 3-ю строку

закомментируйте 3, 4 и 5 строки Вашего кода
оптимальный вариант — закомментировать все, кроме строк:
Sub МАКС()
и
End Sub

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#6

10.04.2018 21:16:00

Цитата
vikttur написал:
Макрос для удаления лишних строк.
 

Юрий М

Модератор

Сообщений: 60335
Регистрация: 14.09.2012

Контакты см. в профиле

Не писал такого Виктор )
BMSs,  Вы не умеете пользоваться цитированием. И не пользуйтесь — ответить можно и без него. А с названием у Вас опять беда: что можно из него понять? Единственное, это то,что у Вас имеется такой макрос. И что? Неужели нельзя кратко сформулировать суть проблемы?

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

Суть в появлении ошибки при запуске макроса, макрос в студии, куда еще короче?

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

Вопрос закрыт, всем спасибо!

 

vikttur

Пользователь

Сообщений: 47199
Регистрация: 15.09.2012

Поделиться решением забыли.

 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#11

12.04.2018 08:30:03

Код
 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Макс!A1:F2000", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=Worksheets("Макс").Range("I2"), TableName:="Сводная Таблица", DefaultVersion:=xlPivotTableVersion14
      Application.GoTo Worksheets("Макс").Range("I2")
 

BMSs

Пользователь

Сообщений: 19
Регистрация: 20.01.2017

#12

12.04.2018 08:30:42

Изменены строки с 23 по 27.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
Private Sub CommandButton1_Click()
'Декларация переменных
Dim fam, pred, exam, ocenk, resault, grup, semestr As String
Dim summa As Single
Dim nomer As Integer
Dim data As Date
 
nomer = Application.CountA(ActiveSheet.Columns(1)) + 1
With UserForm1
 
If [Фамилия] = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If [Группа] = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox1.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox2.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox3.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox4.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
If ComboBox4.Text = "" Then
MsgBox "", vbExclamation
Exit Sub
End If
 
 
fam = [Фамилия]
grup = [Группа]
pred = ComboBox1.Value
ocenk = ComboBox2.Value
resault = ComboBox3.Value
exam = ComboBox4.Value
semestr = ComboBox5.Value
 
 
If IsDate([Дата]) = False Then
MsgBox "", vbExclamation
Exit Sub
End If
 
data = [Дата]
 
With ActiveSheet
.Cells(nomer, 1).Value = fam
.Cells(nomer, 2).Value = pred
.Cells(nomer, 3).Value = exam
.Cells(nomer, 4).Value = ocenk
.Cells(nomer, 5).Value = resault
.Cells(nomer, 6).Value = grup
.Cells(nomer, 7).Value = semestr
.Cells(nomer, 8).Value = summa
.Cells(nomer, 9).Value = nomer
.Cells(nomer, 10).Value = data
End With
ClearForm
End Sub
 
Private Sub ClearForm()
Unload UserForm1
UserForm1.Show
End Sub
 
 
Private Sub UserForm_Initialize()
ComboBox1.AddItem "Информатика"
ComboBox1.AddItem "Высшая  математика"
ComboBox1.AddItem "Физика"
ComboBox1.AddItem "История"
ComboBox1.AddItem "Психология"
ComboBox1.AddItem "Физическое воспитание"
ComboBox1.AddItem "Иследовательская работа"
ComboBox2.AddItem "Отлично"
ComboBox2.AddItem "Хорошо"
ComboBox2.AddItem "Удовлетворительно"
ComboBox2.AddItem "Не сдал"
ComboBox2.AddItem "Зачет"
ComboBox2.AddItem "Незачет"
ComboBox3.AddItem "сессия  закрыта  с  обычной  стипендией"
ComboBox3.AddItem "закрыта  с  повышенной стипендией"
ComboBox3.AddItem "закрыта  с  повышенной стипендией"
ComboBox4.AddItem "Экзамен"
ComboBox4.AddItem "Зачет"
ComboBox5.AddItem "I"
ComboBox5.AddItem "II"
ComboBox5.AddItem "III"
ComboBox5.AddItem "IV"
ComboBox5.AddItem "V"
ComboBox5.AddItem "VI"
ComboBox5.AddItem "VIII"
ComboBox5.AddItem "IX"
ComboBox5.AddItem "X"
End Sub

Not only should there be at least one End With, there shouldn’t be so many With statements.

Sub RowRangeMove()
    Sheets.Add().Name = "CopySheet"
    With Sheets("BigDataSet - Copy")
        .Range("A65000", .Range("A13000").End(xlUp)).Copy Destination:=Range("A1")
        .Range("B65000", .Range("B13000").End(xlUp)).Copy Destination:=Range("B1")
        .Range("C65000", .Range("C13000").End(xlUp)).Copy Destination:=Range("C1")
        .Range("D65000", .Range("D13000").End(xlUp)).Copy Destination:=Range("D1")
        .Range("E65000", .Range("E13000").End(xlUp)).Copy Destination:=Range("E1")
        .Range("F65000", .Range("F13000").End(xlUp)).Copy Destination:=Range("F1")
        .Range("G65000", .Range("G13000").End(xlUp)).Copy Destination:=Range("G1")
        .Range("H65000", .Range("H13000").End(xlUp)).Copy Destination:=Range("H1")
        .Range("I65000", .Range("I13000").End(xlUp)).Copy Destination:=Range("I1")
        .Range("J65000", .Range("J13000").End(xlUp)).Copy Destination:=Range("J1")
    End With

End Sub

would be the correct syntax.

The With statement is simply a way to shorten up your lines of code. The With statement is a way of saying «I’m going to perform a bunch of actions on a specific object» and shorten up the individual lines of code.

Example:

Without the With statement, the code above would look like this:

Sub RowRangeMove()
    Sheets.Add().Name = "CopySheet"
        Sheets("BigDataSet - Copy").Range("A65000", .Range("A13000").End(xlUp)).Copy Destination:=Range("A1")
        Sheets("BigDataSet - Copy").Range("B65000", .Range("B13000").End(xlUp)).Copy Destination:=Range("B1")
        Sheets("BigDataSet - Copy").Range("C65000", .Range("C13000").End(xlUp)).Copy Destination:=Range("C1")
        Sheets("BigDataSet - Copy").Range("D65000", .Range("D13000").End(xlUp)).Copy Destination:=Range("D1")
        Sheets("BigDataSet - Copy").Range("E65000", .Range("E13000").End(xlUp)).Copy Destination:=Range("E1")
        Sheets("BigDataSet - Copy").Range("F65000", .Range("F13000").End(xlUp)).Copy Destination:=Range("F1")
        Sheets("BigDataSet - Copy").Range("G65000", .Range("G13000").End(xlUp)).Copy Destination:=Range("G1")
        Sheets("BigDataSet - Copy").Range("H65000", .Range("H13000").End(xlUp)).Copy Destination:=Range("H1")
        Sheets("BigDataSet - Copy").Range("I65000", .Range("I13000").End(xlUp)).Copy Destination:=Range("I1")
        Sheets("BigDataSet - Copy").Range("J65000", .Range("J13000").End(xlUp)).Copy Destination:=Range("J1")
End Sub

In shorter terms, the With statement allows you to start out individual lines of code with a dot, and inside that with statement, the compiler will assume you mean the thing declared in your with statement.

So

With Answerer
  ' Inside this with block, any line beginning with "." , 
  ' the compiler will assume you mean "Answerer.".   
  ' Therefore ".FirstName" is the same as "Answerer.FirstName"
  .FirstName = "David"
  .LastName = "Stratton"
End With

is equivalient to

Answerer.FirstName = "David"
Amswerer.LastName = "Stratton"
  1. 02-13-2013, 02:16 PM

    #1

    Robert110 is offline


    Registered User


    Compile Error: Expected End With

    Hi,

    I have been recording 3 separate Marco’s in excel and now would like them to become one.

    The separate Marco’s are:-
    1) To format a header, and copy an “If” statement in to cell K4
    2) To fill the copied information in K4 down to a dynamic range
    3) To conditional format the rows that have «Yes» in the column K.

    The script is below, but I get the error «Compile Error: Expected End With»
    I have tried many things but I can’t seem to join these Marcos together.

    I hope someone can help me with this, as this s is driving me crazy

    Thanks in advance
    Rob

    Last edited by Robert110; 02-14-2013 at 06:37 AM.


  2. 02-13-2013, 02:20 PM

    #2

    Re: Compile Error: Expected End With

    Rob

    It’s hard to tell where the problem is without seeing the code in tags.

    Could you edit and add them?

    If posting code please use code tags, see here.


  3. 02-13-2013, 02:23 PM

    #3

    Robert110 is offline


    Registered User


    Re: Compile Error: Expected End With


  4. 02-13-2013, 02:25 PM

    #4

    Re: Compile Error: Expected End With

    Rob

    You can (should?) add code tags so that it makes the code easier to read/copy.

    The tags will preserve any indentation and will make the code stand out from the rest of the post.

    To add tags, select the code and press the # button in the toolbar above the reply box.

    PS Try sticking End With just before End Sub.


  5. 02-13-2013, 02:38 PM

    #5

    Robert110 is offline


    Registered User


    Re: Compile Error: Expected End With

    Ok Norie, Thanks for telling me about the coda posting ,

    Sorry I didn’t know about the cods tag’s I will do that in future.

    I did try «End With» but it still errored.

    I am working on a different computer at the min and don’t have the code here with me. so I had to copy and paste the code from Excel to and e-mail and then post it on this thread
    But if it helps I can get the code from excel tomorrow and post it in a code format.

    Rob


  6. 02-13-2013, 02:42 PM

    #6

    Re: Compile Error: Expected End With

    Rob

    I might have missed something — it is hard to read the code without the tags.

    PS You can edit your original post to add the code tags.


  7. 02-13-2013, 02:50 PM

    #7

    Robert110 is offline


    Registered User


    Re: Compile Error: Expected End With

    Norie,

    Thanks for the tip, I will edit my first post. Once I get on the other computer.
    Thanks very much for helping me

    Rob


  8. 02-14-2013, 06:39 AM

    #8

    Robert110 is offline


    Registered User


    Re: Compile Error: Expected End With

    Hi Norie,

    I have added the code in the correct format to my first message. I hope this makes it a bit clearer now.

    Thanks again for your help

    Rob


  9. 02-14-2013, 06:48 AM

    #9

    Re: Compile Error: Expected End With

    Rob

    That does help, the answer is still the same though — all you need to do is add End With just before End Sub.


  10. 02-14-2013, 07:14 AM

    #10

    Robert110 is offline


    Registered User


    Re: Compile Error: Expected End With

    Thank you so much Norie, that worked perfectly.

    To help me understand, why did I need an «End With» there, why wouldn’t an End Sub just do?

    Thanks again, this was driving me crazy:-)


Return to VBA Code Examples

This tutorial will explain what a VBA Compile Error means and how it occurs.

Before running your code, the VBA Editor compiles the code. This basically means that VBA examines your code to make sure that all the requirements are there to run it correctly – it will check that all the variables are declared (if you use Option Explicit which you should!), check that all the procedures are declared, check the loops and if statements etc. By compiling the code, VBA helps to minimize any runtime errors occurring.

(See our Error Handling Guide for more information about VBA Errors)

Undeclared Variables

If you do not declare variables, but your Option Explicit is switched on at the top of your module, and then you run the macro, a compile error will occur.

VBACompileError VarNotDeclared

If you click OK,  the relevant procedure will go into debug mode.

VBACompileError Debug

Alternatively, before you run your code, you can force a compilation of the code.

In the Menu, select Debug > Compile Project.

VBACompileError Menu

The compiler will find any compile errors and highlight the first one it finds accordingly.

Undeclared Procedures

If you code refers to a procedure that does not exist, you will also get a compile error.

For example:

Sub CallProcedure()
'some code here then 
  Call NextProcedure
End Sub

However, if the procedure – NextProcedure does not exist, then a compile error will occur.

VBACompileError NoProcedure

Incorrect Coding – Expected End of Statement

If you create a loop using For..Each..Next or With..End With and forget to and the Next or the End With… you will also get a compile error.

Sub CompileError()
 Dim wb As Workbook
 Dim ws As Worksheet
 For Each ws In wb
   MsgBox ws.Name
End Sub

VBACompileError NoNext

The same will happen with an If statement if the End If is omitted!

VBACompileError NoEndIf

Missing References

If you are using an Object Library that is not part of Excel, but you are using the objects from the library in your variable declaration, you will also receive a compile error.

VBACompileError MissingRef

This can be solved by either Late Binding – declaring the variables are Objects; or by adding the relevant Object Library to the Project.

In the Menu, select Tools > References and add the relevant object library to your project.

VBACompileError RefBox

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
vba save as

Learn More!

FreeBASIC

Compiler Error Messages

During the program compilation three types of errors can arise:

Compiler Warnings:

The warnings don’t stop the compilation, just alert the user some non-recommended and error-prone operation is attempted in the code.
Sometimes one of these operations is coded deliberately to achieve a result, in this case the warnings can be disabled by setting the -w 1 option at the command line.

  • 1 Passing scalar as pointer
  • 2 Passing pointer to scalar
  • 3 Passing different pointer types
  • 4 Suspicious pointer assignment
  • 5 Implicit conversion
  • 6 Cannot export symbol without -export option
  • 7 Identifier’s name too big, truncated
  • 8 Literal number too big, truncated
  • 9 Literal string too big, truncated
  • 10 UDT with pointer or var-len string fields
  • 11 Implicit variable allocation
  • 12 Missing closing quote in literal string
  • 13 Function result was not explicitly set
  • 14 Branch crossing local variable definition
  • 15 No explicit BYREF or BYVAL
  • 16 Possible escape sequence found in
  • 17 The type length is too large, consider passing BYREF
  • 18 The length of the parameters list is too large, consider passing UDT’s BYREF
  • 19 The ANY initializer has no effect on UDT’s with default constructors
  • 20 Object files or libraries with mixed multithreading (-mt) options
  • 21 Object files or libraries with mixed language (-lang) options
  • 22 Deleting ANY pointers is undefined
  • 23 Array too large for stack, consider making it var-len or SHARED
  • 24 Variable too large for stack, consider making it SHARED
  • 25 Overflow in constant conversion
  • 26 Variable following NEXT is meaningless
  • 27 Cast to non-pointer
  • 28 Return method mismatch
  • 29 Passing Pointer
  • 30 Command line option overrides directive
  • 31 Directive ignored after first pass
  • 32 ‘IF’ statement found directly after multi-line ‘ELSE’
  • 33 Shift value greater than or equal to number of bits in data type
  • 34 ‘=’ parsed as equality operator in function argument, not assignment to BYREF function result
  • 35 Mixing signed/unsigned operands
  • 36 Mismatching parameter initializer
  • 37
  • 38 Mixing operand data types may have undefined results
  • 39 Redefinition of intrinsic

Compiler Error messages:

The error messages stop the compilation after 10 errors (see the -maxerr command-line option to change that default value) or a fatal error occurred, and require a correction by the user before the compilation can be continued. The compiler signals the lines where the errors have been found, so the correction can be done quickly. In a few cases the place pointed at by the error messages is not where the errors can be found, it’s the place where the compiler has given up in waiting for something that should be somewhere.

  • 1 Argument count mismatch
  • 2 Expected End-of-File
  • 3 Expected End-of-Line
  • 4 Duplicated definition
  • 5 Expected ‘AS’
  • 6 Expected ‘(‘
  • 7 Expected ‘)’
  • 8 Undefined symbol
  • 9 Expected expression
  • 10 Expected ‘=’
  • 11 Expected constant
  • 12 Expected ‘TO’
  • 13 Expected ‘NEXT’
  • 14 Expected identifier
  • 15 Expected ‘-‘
  • 16 Expected ‘,’
  • 17 Syntax error
  • 18 Element not defined
  • 19 Expected ‘END TYPE’ or ‘END UNION’
  • 20 Type mismatch
  • 21 Internal!
  • 22 Parameter type mismatch
  • 23 File not found
  • 24 Invalid data types
  • 25 Invalid character
  • 26 File access error
  • 27 Recursion level too deep
  • 28 Expected pointer
  • 29 Expected ‘LOOP’
  • 30 Expected ‘WEND’
  • 31 Expected ‘THEN’
  • 32 Expected ‘END IF’
  • 33 Illegal ‘END’
  • 34 Expected ‘CASE’
  • 35 Expected ‘END SELECT’
  • 36 Wrong number of dimensions
  • 37 Array boundaries do not match the original EXTERN declaration
  • 38 ‘SUB’ or ‘FUNCTION’ without ‘END SUB’ or ‘END FUNCTION’
  • 39 Expected ‘END SUB’ or ‘END FUNCTION’
  • 40 Illegal parameter specification
  • 41 Variable not declared
  • 42 Variable required
  • 43 Illegal outside a compound statement
  • 44 Expected ‘END ASM’
  • 45 Function not declared
  • 46 Expected ‘;’
  • 47 Undefined label
  • 48 Too many array dimensions
  • 49 Array too big
  • 50 User Defined Type too big
  • 51 Expected scalar counter
  • 52 Illegal outside a CONSTRUCTOR, DESTRUCTOR, FUNCTION, OPERATOR, PROPERTY or SUB block
  • 53 Expected var-len array
  • 54 Fixed-len strings cannot be returned from functions
  • 55 Array already dimensioned
  • 56 Illegal without the -ex option
  • 57 Type mismatch
  • 58 Illegal specification
  • 59 Expected ‘END WITH’
  • 60 Illegal inside functions
  • 61 Statement in between SELECT and first CASE
  • 62 Expected array
  • 63 Expected ‘{‘
  • 64 Expected ‘}’
  • 65 Expected ‘]’
  • 66 Too many expressions
  • 67 Expected explicit result type
  • 68 Range too large
  • 69 Forward references not allowed
  • 70 Incomplete type
  • 71 Array not dimensioned
  • 72 Array access, index expected
  • 73 Expected ‘END ENUM’
  • 74 Var-len arrays cannot be initialized
  • 75 ‘…’ ellipsis upper bound given for dynamic array (this is not supported)
  • 76 ‘…’ ellipsis upper bound given for array field (this is not supported)
  • 77 Invalid bitfield
  • 78 Too many parameters
  • 79 Macro text too long
  • 80 Invalid command-line option
  • 81 Selected non-x86 CPU when compiling for DOS
  • 82 Selected -gen gas ASM backend for non-x86 CPU
  • 83 -asm att used for -gen gas, but -gen gas only supports -asm intel
  • 84 -pic used when making executable (only works when making a shared library)
  • 85 -pic used, but not supported by target system (only works for non-x86 Unixes)
  • 86 Var-len strings cannot be initialized
  • 87 Recursive TYPE or UNION not allowed
  • 88 Recursive DEFINE not allowed
  • 89 Identifier cannot include periods
  • 90 Executable not found
  • 91 Array out-of-bounds
  • 92 Missing command-line option for
  • 93 Expected ‘ANY’
  • 94 Expected ‘END SCOPE’
  • 95 Illegal inside a compound statement or scoped block
  • 96 UDT function results cannot be passed by reference
  • 97 Ambiguous call to overloaded function
  • 98 No matching overloaded function
  • 99 Division by zero
  • 100 Cannot pop stack, underflow
  • 101 UDT’s containing var-len string fields cannot be initialized
  • 102 Branching to scope block containing local variables
  • 103 Branching to other functions or to module-level
  • 104 Branch crossing local array, var-len string or object definition
  • 105 LOOP without DO
  • 106 NEXT without FOR
  • 107 WEND without WHILE
  • 108 END WITH without WITH
  • 109 END IF without IF
  • 110 END SELECT without SELECT
  • 111 END SUB or FUNCTION without SUB or FUNCTION
  • 112 END SCOPE without SCOPE
  • 113 END NAMESPACE without NAMESPACE
  • 114 END EXTERN without EXTERN
  • 115 ELSEIF without IF
  • 116 ELSE without IF
  • 117 CASE without SELECT
  • 118 Cannot modify a constant
  • 119 Expected period (‘.’)
  • 120 Expected ‘END NAMESPACE’
  • 121 Illegal inside a NAMESPACE block
  • 122 Symbols defined inside namespaces cannot be removed
  • 123 Expected ‘END EXTERN’
  • 124 Expected ‘END SUB’
  • 125 Expected ‘END FUNCTION’
  • 126 Expected ‘END CONSTRUCTOR’
  • 127 Expected ‘END DESTRUCTOR’
  • 128 Expected ‘END OPERATOR’
  • 129 Expected ‘END PROPERTY’
  • 130 Declaration outside the original namespace
  • 131 No end of multi-line comment, expected «‘/»
  • 132 Too many errors, exiting
  • 133 Expected ‘ENDMACRO’
  • 134 EXTERN or COMMON variables cannot be initialized
  • 135 EXTERN or COMMON dynamic arrays cannot have initial bounds
  • 136 At least one parameter must be a user-defined type
  • 137 Parameter or result must be a user-defined type
  • 138 Both parameters can’t be of the same type
  • 139 Parameter and result can’t be of the same type
  • 140 Invalid result type for this operator
  • 141 Invalid parameter type, it must be the same as the parent TYPE/CLASS
  • 142 Vararg parameters are not allowed in overloaded functions
  • 143 Illegal outside an OPERATOR block
  • 144 Parameter cannot be optional
  • 145 Only valid in -lang
  • 146 Default types or suffixes are only valid in -lang
  • 147 Suffixes are only valid in -lang
  • 148 Implicit variables are only valid in -lang
  • 149 Auto variables are only valid in -lang
  • 150 Invalid array index
  • 151 Operator must be a member function
  • 152 Operator cannot be a member function
  • 153 Method declared in anonymous UDT
  • 154 Constant declared in anonymous UDT
  • 155 Static variable declared in anonymous UDT
  • 156 Expected operator
  • 157 Declaration outside the original namespace or class
  • 158 A destructor should not have any parameters
  • 159 Expected class or UDT identifier
  • 160 Var-len strings cannot be part of UNION’s or nested TYPE’s
  • 161 Dynamic arrays cannot be part of UNION’s or nested TYPE’s
  • 162 Fields with constructors cannot be part of UNION’s or nested TYPE’s
  • 163 Fields with destructors cannot be part of UNION’s or nested TYPE’s
  • 164 Illegal outside a CONSTRUCTOR block
  • 165 Illegal outside a DESTRUCTOR block
  • 166 UDT’s with methods must have unique names
  • 167 Parent is not a class or UDT
  • 168 CONSTRUCTOR() chain call not at top of constructor
  • 169 BASE() initializer not at top of constructor
  • 170 REDIM on UDT with non-CDECL constructor
  • 171 REDIM on UDT with non-CDECL destructor
  • 172 REDIM on UDT with non-parameterless default constructor
  • 173 ERASE on UDT with non-CDECL constructor
  • 174 ERASE on UDT with non-CDECL destructor
  • 175 ERASE on UDT with non-parameterless default constructor
  • 176 This symbol cannot be undefined
  • 177 RETURN mixed with ‘FUNCTION =’ or EXIT FUNCTION (using both styles together is unsupported when returning objects with constructors)
  • 178 ‘FUNCTION =’ or EXIT FUNCTION mixed with RETURN (using both styles together is unsupported when returning objects with constructors)
  • 179 Missing RETURN to copy-construct function result
  • 180 Invalid assignment/conversion
  • 181 Invalid array subscript
  • 182 TYPE or CLASS has no default constructor
  • 183 Function result TYPE has no default constructor
  • 184 Missing BASE() initializer (base UDT without default constructor requires manual initialization)
  • 185 Missing default constructor implementation (base UDT without default constructor requires manual initialization)
  • 186 Missing UDT.constructor(byref as UDT) implementation (base UDT without default constructor requires manual initialization)
  • 187 Missing UDT.constructor(byref as const UDT) implementation (base UDT without default constructor requires manual initialization)
  • 188 Invalid priority attribute
  • 189 PROPERTY GET should have no parameter, or just one if indexed
  • 190 PROPERTY SET should have one parameter, or just two if indexed
  • 191 Expected ‘PROPERTY’
  • 192 Illegal outside a PROPERTY block
  • 193 PROPERTY has no GET method/accessor
  • 194 PROPERTY has no SET method/accessor
  • 195 PROPERTY has no indexed GET method/accessor
  • 196 PROPERTY has no indexed SET method/accessor
  • 197 Missing overloaded operator:
  • 198 The NEW[] operator does not allow explicit calls to constructors
  • 199 The NEW[] operator only supports the { ANY } initialization
  • 200 The NEW operator cannot be used with fixed-length strings
  • 201 Illegal member access
  • 202 Expected ‘:’
  • 203 The default constructor has no public access
  • 204 Constructor has no public access
  • 205 Destructor has no public access
  • 206 Accessing base UDT’s private default constructor
  • 207 Accessing base UDT’s private destructor
  • 208 Illegal non-static member access
  • 209 Constructor declared ABSTRACT
  • 210 Constructor declared VIRTUAL
  • 211 Destructor declared ABSTRACT
  • 212 Member cannot be static
  • 213 Member isn’t static
  • 214 Only static members can be accessed from static functions
  • 215 The PRIVATE and PUBLIC attributes are not allowed with REDIM, COMMON or EXTERN
  • 216 STATIC used here, but not the in the DECLARE statement
  • 217 CONST used here, but not the in the DECLARE statement
  • 218 VIRTUAL used here, but not the in the DECLARE statement
  • 219 ABSTRACT used here, but not the in the DECLARE statement
  • 220 Method declared VIRTUAL, but UDT does not extend OBJECT
  • 221 Method declared ABSTRACT, but UDT does not extend OBJECT
  • 222 Not overriding any virtual method
  • 223 Implemented body for an ABSTRACT method
  • 224 Override has different return type than overridden method
  • 225 Override has different calling convention than overridden method
  • 226 Implicit destructor override would have different calling convention
  • 227 Implicit LET operator override would have different calling convention
  • 228 Override is not a CONST member like the overridden method
  • 229 Override is a CONST member, but the overridden method is not
  • 230 Override has different parameters than overridden method
  • 231 This operator cannot be STATIC
  • 232 This operator is implicitly STATIC and cannot be VIRTUAL or ABSTRACT
  • 233 This operator is implicitly STATIC and cannot be CONST
  • 234 Parameter must be an integer
  • 235 Parameter must be a pointer
  • 236 Expected initializer
  • 237 Fields cannot be named as keywords in TYPE’s that contain member functions or in CLASS’es
  • 238 Illegal outside a FOR compound statement
  • 239 Illegal outside a DO compound statement
  • 240 Illegal outside a WHILE compound statement
  • 241 Illegal outside a SELECT compound statement
  • 242 Expected ‘FOR’
  • 243 Expected ‘DO’
  • 244 Expected ‘WHILE’
  • 245 Expected ‘SELECT’
  • 246 No outer FOR compound statement found
  • 247 No outer DO compound statement found
  • 248 No outer WHILE compound statement found
  • 249 No outer SELECT compound statement found
  • 250 Expected ‘CONSTRUCTOR’, ‘DESTRUCTOR’, ‘DO’, ‘FOR’, ‘FUNCTION’, ‘OPERATOR’, ‘PROPERTY’, ‘SELECT’, ‘SUB’ or ‘WHILE’
  • 251 Expected ‘DO’, ‘FOR’ or ‘WHILE’
  • 252 Illegal outside a SUB block
  • 253 Illegal outside a FUNCTION block
  • 254 Ambiguous symbol access, explicit scope resolution required
  • 255 An ENUM, TYPE or UNION cannot be empty
  • 256 ENUM’s declared inside EXTERN .. END EXTERN blocks don’t open new scopes
  • 257 STATIC used on non-member procedure
  • 258 CONST used on non-member procedure
  • 259 ABSTRACT used on non-member procedure
  • 260 VIRTUAL used on non-member procedure
  • 261 Invalid initializer
  • 262 Objects with default [con|de]structors or methods are only allowed in the module level
  • 263 Static member variable in nested UDT (only allowed in toplevel UDTs)
  • 264 Symbol not a CLASS, ENUM, TYPE or UNION type
  • 265 Too many elements
  • 266 Only data members supported
  • 267 UNIONs are not allowed
  • 268 Arrays are not allowed
  • 269 COMMON variables cannot be object instances of CLASS/TYPE’s with cons/destructors
  • 270 Cloning operators (LET, Copy constructors) can’t take a byval arg of the parent’s type
  • 271 Local symbols can’t be referenced
  • 272 Expected ‘PTR’ or ‘POINTER’
  • 273 Too many levels of pointer indirection
  • 274 Dynamic arrays can’t be const
  • 275 Const UDT cannot invoke non-const method
  • 276 Elements must be empty for strings and arrays
  • 277 GOSUB disabled, use ‘OPTION GOSUB’ to enable
  • 278 Invalid -lang
  • 279 Can’t use ANY as initializer in array with ellipsis bound
  • 280 Must have initializer with array with ellipsis bound
  • 281 Can’t use … as lower bound
  • 282 FOR/NEXT variable name mismatch
  • 283 Selected option requires an SSE FPU mode
  • 284 Expected relational operator ( =, >, <, <>, <=, >= )
  • 285 Unsupported statement in -gen gcc mode
  • 286 Too many labels
  • 287 Unsupported function
  • 288 Expected sub
  • 289 Expected ‘#ENDIF’
  • 290 Resource file given for target system that does not support them
  • 291 -o <file> option without corresponding input file
  • 292 Not extending a TYPE/UNION (a TYPE/UNION can only extend other TYPEs/UNIONs)
  • 293 Illegal outside a CLASS, TYPE or UNION method
  • 294 CLASS, TYPE or UNION not derived
  • 295 CLASS, TYPE or UNION has no constructor
  • 296 Symbol type has no Run-Time Type Info (RTTI)
  • 297 Types have no hierarchical relation
  • 298 Expected a CLASS, TYPE or UNION symbol type
  • 299 Casting derived UDT pointer from incompatible pointer type
  • 300 Casting derived UDT pointer from unrelated UDT pointer type
  • 301 Casting derived UDT pointer to incompatible pointer type
  • 302 Casting derived UDT pointer to unrelated UDT pointer type
  • 303 ALIAS name string is empty
  • 304 LIB name string is empty
  • 305 UDT has unimplemented abstract methods
  • 306 Non-virtual call to ABSTRACT method
  • 307 #ASSERT condition failed
  • 308 Expected ‘>’
  • 309 Invalid size
  • 310 ALIAS name here is different from ALIAS given in DECLARE prototype
  • 311 vararg parameters are only allowed in CDECL procedures
  • 312 the first parameter in a procedure may not be vararg
  • 313 CONST used on constructor (not needed)
  • 314 CONST used on destructor (not needed)
  • 315 Byref function result not set
  • 316 Function result assignment outside of the function
  • 317 Type mismatch in byref function result assignment
  • 318 -asm att|intel option given, but not supported for this target (only x86 or x86_64)
  • 319 Reference not initialized
  • 320 Incompatible reference initializer
  • 321 Array of references — not supported yet
  • 322 Invalid CASE range, start value is greater than the end value
  • 323 Fixed-length string combined with BYREF (not supported)

Third party programs errors

These errors occur after the source has been compiled into assembler, they come from the auxiliary programs FB requires to compile a source into an executable: the linker, the assembler and (for Windows programs) the resource compiler.

If an IDE or a make utility are been used, additional errors can arise. These errors are outside the scope of this help.

При выполнении кода я получил следующую ошибку «Ожидайте завершения с». Это может быть здорово, если помощь будет оказана.

Я создал код ниже, который сравнивает две таблицы. Первоначально сравнивается диапазон значений от sheet1 («Status») до sheet2 («Interface»). Всякий раз, когда диапазон совпадает с диапазонами, представленными на другом листе, он ничего не делает. Когда диапазон не совпадает на другом листе, вся запись копируется с листа2 на лист1. У меня есть около 1500 строк данных на обоих листах с 15 столбцами.

Function UpdateNEW2() As Long
    Const Delimiter As String = "|"
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    Dim newRows As Range
    Dim vSteps, key
    With Sheets("Steps")
        vSteps = .Range("A2:C2", .Cells(.Rows.Count, 1).End(xlUp)).Value
    End With

        Dim r As Long, c As Long, n As Long
        For r = 1 To UBound(vSteps)
            key = vSteps(r, 2) & Delimiter & vSteps(r, 2)
            If Not dic.Exists(key) Then dic.Add key, 0
        Next

        Dim vInterface, results
        With Sheets("Interface")
            vInterface = .Range("A2:O2", .Cells(.Rows.Count, "C").End(xlUp)).Value
        End With

        ReDim results(1 To UBound(vInterface), 1 To 15)
        For r = 1 To UBound(vInterface)
            key = vInterface(r, 5) & Delimiter & vInterface(r, 5)
            If Not dic.Exists(key) Then
                n = n + 1
                For c = 3 To 15
                    results(n, c - 2) = vInterface(r, c)
                Next
            End If
        Next
        With Sheets("Steps")
        With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                .Resize(n, 15).Value = results
                  End With

              UpdateNEW2 = n

    End Function

2 ответа

Лучший ответ

Option Explicit '<- Always use Option Explicit 

Function UpdateNEW2() As Long

    Const Delimiter As String = "|"

    Dim dic As Object
    Dim newRows As Range
    Dim vSteps, key, vInterface, results '<- You could declare variables
    Dim r As Long, c As Long, n As Long

    Set dic = CreateObject("Scripting.Dictionary")

    With ThisWorkbook '<- To avoid issues if two workbooks are open create a "With Statement" for this workbook

        With .Sheets("Steps") '<- Use "." before Sheets to show that you will the work in the workbook of the "With Statement"

            vSteps = .Range("A2:C2", .Cells(.Rows.Count, 1).End(xlUp)).Value

        End With

        For r = 1 To UBound(vSteps)

            key = vSteps(r, 2) & Delimiter & vSteps(r, 2)

            If Not dic.Exists(key) Then dic.Add key, 0

        Next

        With .Sheets("Interface") '<- Use "." before Sheets to show that you will the work in the workbook of the "With Statement"

            vInterface = .Range("A2:O2", .Cells(.Rows.Count, "C").End(xlUp)).Value

        End With

        ReDim results(1 To UBound(vInterface), 1 To 15)

        For r = 1 To UBound(vInterface)

            key = vInterface(r, 5) & Delimiter & vInterface(r, 5)

            If Not dic.Exists(key) Then

                n = n + 1

                For c = 3 To 15
                    results(n, c - 2) = vInterface(r, c)
                Next

            End If

        Next

        With .Sheets("Steps") '<- Use "." before Sheets to show that you will the work in the workbook of the "With Statement"

            With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)

                    .Resize(n, 15).Value = results

            End With

              UpdateNEW2 = n

        End With

    End With

End Function


1

Error 1004
17 Апр 2019 в 08:57

    With Sheets("Steps")
      With .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
            .Resize(n, 15).Value = results
      End With

      UpdateNEW2 = n
    End with <-- you've forgotten this one.

Как видите, правильный отступ облегчает чтение и исправление в случае необходимости.


0

Dominique
17 Апр 2019 в 08:48

  • #3

You have part of what looks like a recorded macro followed by an event procedure.

You just need

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("G34")) Is Nothing Then
            With Target
                If IsNumeric(.Value) And Not IsEmpty(.Value) Then
                    Range("I34").Value = Range("I34").Value + Range("G34").Value
                End If
            End With
        End If
End Sub

not the part above it.
<!— / message —>

  • #4

Hi,

As shg said you must remove the lines above Private Sub…, but you need also put this code on a worksheet page-code, not in a Standard Module.

This is an event procedure, ie, it runs when something happens — in your case when G34 changes.

For an excellent explanation take a look at
http://www.cpearson.com/excel/Events.aspx

M.

  • #7

Or, if you wish, you can use an event procedure like

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("G34")) Is Nothing Then
        Application.EnableEvents = False
        With Target
            If IsNumeric(.Value) Then
                Range("I34").Value = Range("I34").Value + .Value
                .Value = 0
            End If
        End With
        Application.EnableEvents = True
    End If
End Sub

M.

Joe4

Joe4

MrExcel MVP, Junior Admin


  • #2

Welcome to the Board!

The error code is actually telling you exactly what your issue is. Every time you use a «With» statement, you need a corresponding «End With» statement to mark the end of the With statement. You have a «With», but no «End With». It looks like it should be right after your «.Apply» line.

  • #3

Im afraid that could never have run unless you deleted End With without noticing. Any its here its needed:

Code:

 With ActiveWorkbook.Worksheets("March Other Sources").Sort
 .SetRange Range("A4:AF5000")
 .Header = xlYes
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
End With

  • #4

Welcome to the Board!

The error code is actually telling you exactly what your issue is. Every time you use a «With» statement, you need a corresponding «End With» statement to mark the end of the With statement. You have a «With», but no «End With». It looks like it should be right after your «.Apply» line.

Thanks for the quick response! Knew I’d get the answer from here :LOL:

Joe4

Joe4

MrExcel MVP, Junior Admin


  • #5

You are welcome!

Be sure to pay special attention to the error messages that are returned. Many times, they are cryptic, and it is tough to figure out what it is trying to tell you, but other times it is a little clearer and pretty indicative of what the problem actually is.

Понравилась статья? Поделить с друзьями:
  • Exloader ошибка антивирус
  • Exit status 1 ошибка компиляции для платы arduino
  • Exhaust workshop ошибка туарег
  • Exhaust system warning на daf ошибка
  • Exel ошибка при направлении команды приложению