Страницы: 1
RSS
end if without block if, куда поставить end if, чтобы заработало
 
Добрый день!
Подскажите, пожалуйста, мне Excel ругается на последний end if, говорит что мой end if without block if, но у меня же кол-во if и end if одинаковое.
Если удаляю его, то начинает ругаться на Next k without For.
Как же быть? Куда только не ставил его.
Код
Option Explicit

Sub checking()


Dim a()
Dim b()
Dim i As Long
Dim k As Long
Dim j As Long
Dim iLastRow As Long

a = Sheets("ÎèÎ").[A1].CurrentRegion.Value
b = Sheets("ÎÑÂ").[A1].CurrentRegion.Value
ReDim c(1 To UBound(a) + UBound(b), 1 To 12)


j = 1

For i = 1 To UBound(a)
    If a(i, 7) Like "*ÎÎÎ*" Or _
              a(i, 7) Like "*ÀÎ*" Or _
              a(i, 7) Like "*""*""*" Or _
              UBound(Split(a(i, 7), " "), 1) + 1 >= 5 Then
        For k = 1 To UBound(b)

         If a(i, 7) = b(k, 1) Then
                    c(j, 1) = a(i, 1)
                    c(j, 2) = a(i, 2)
                    c(j, 3) = a(i, 3)
                    c(j, 4) = a(i, 4)
                    c(j, 5) = a(i, 5)
                    c(j, 6) = a(i, 6)
                    c(j, 7) = a(i, 7)
                    c(j,  = a(i, 
                    c(j, 9) = b(k, 1)
                    c(j, 10) = b(k, 2)
                    c(j, 11) = b(k, 3)
                    c(j, 12) = b(k, 4)
                   j = j + 1
                   
        Else

            With CreateObject("VBScript.RegExp")
            .Pattern = "[à-ÿÀ-߸¨]+\s[à-ÿÀ-߸¨]{1}[.]{1}[à-ÿÀ-߸¨]{1}[.]{1}$" 
                 If .Test(a(i, 7)) Then
        
                        If a(i, 7) = b(k, 1) _
                         Or Right(a(i, 7), 4) = Right(b(k, 1), 4) _
                         Or Left(a(i, 7), InStr(a(i, 7), " ") - 2) = Left(a(k, 1), InStr(a(i, 7), " ") - 2) Then
  

                    c(j, 1) = a(i, 1)
                    c(j, 2) = a(i, 2)
                    c(j, 3) = a(i, 3)
                    c(j, 4) = a(i, 4)
                    c(j, 5) = a(i, 5)
                    c(j, 6) = a(i, 6)
                    c(j, 7) = a(i, 7)
                    c(j,  = a(i, 
                    c(j, 9) = b(k, 1)
                    c(j, 10) = b(k, 2)
                    c(j, 11) = b(k, 3)
                    c(j, 12) = b(k, 4)
                   j = j + 1
   
End If
End If
End If

Next k
             Next i

Sheets("Èòîã").[A1].Resize(UBound(c), 12) = c


End Sub
 
а количество With и End With,  а последовательно завершения блоков????
По вопросам из тем форума, личку не читаю.
 
Код
Option Explicit
Sub checking()
 
 
Dim a()
Dim b()
Dim i As Long
Dim k As Long
Dim j As Long
Dim iLastRow As Long
 
a = Sheets("IeI").[A1].CurrentRegion.Value
b = Sheets("INA").[A1].CurrentRegion.Value
ReDim c(1 To UBound(a) + UBound(b), 1 To 12)
 
 
j = 1
 
For i = 1 To UBound(a)
    If a(i, 7) Like "*III*" Or _
              a(i, 7) Like "*AI*" Or _
              a(i, 7) Like "*""*""*" Or _
              UBound(Split(a(i, 7), " "), 1) + 1 >= 5 Then
        For k = 1 To UBound(b)
 
         If a(i, 7) = b(k, 1) Then
                    c(j, 1) = a(i, 1)
                    c(j, 2) = a(i, 2)
                    c(j, 3) = a(i, 3)
                    c(j, 4) = a(i, 4)
                    c(j, 5) = a(i, 5)
                    c(j, 6) = a(i, 6)
                    c(j, 7) = a(i, 7)
                    c(j, 9) = b(k, 1)
                    c(j, 10) = b(k, 2)
                    c(j, 11) = b(k, 3)
                    c(j, 12) = b(k, 4)
                   j = j + 1
                    
        Else
 
            With CreateObject("VBScript.RegExp")
            .Pattern = "[a-yA-???]+\s[a-yA-???]{1}[.]{1}[a-yA-???]{1}[.]{1}$"
                 If .Test(a(i, 7)) Then
         
                        If a(i, 7) = b(k, 1) _
                         Or Right(a(i, 7), 4) = Right(b(k, 1), 4) _
                         Or Left(a(i, 7), InStr(a(i, 7), " ") - 2) = Left(a(k, 1), InStr(a(i, 7), " ") - 2) Then
   
 
                                 c(j, 1) = a(i, 1)
                                 c(j, 2) = a(i, 2)
                                 c(j, 3) = a(i, 3)
                                 c(j, 4) = a(i, 4)
                                 c(j, 5) = a(i, 5)
                                 c(j, 6) = a(i, 6)
                                 c(j, 7) = a(i, 7)
                                 c(j, 9) = b(k, 1)
                                 c(j, 10) = b(k, 2)
                                 c(j, 11) = b(k, 3)
                                 c(j, 12) = b(k, 4)
                                j = j + 1
    
                        End If
                End If
            End With
         End If
 
Next k
    End If
Next i
 
Sheets("Eoia").[A1].Resize(UBound(c), 12) = c
 
 
End Sub
 
БМВ, end with потерял совсем  :D спасибо!
МатросНаЗебре, ваш вариант заработал, спасибо!
Страницы: 1
Наверх