blackeangel
25-03-2021, 12:08
Доброго дня всем. Скажите, пожалуйста, как можно этот код оптимизировать?
Do While Cells(i, Ncolumn2).Value <> Empty 'поставил на "Обозначение" т.к. обрывался на пустой ячейке
objRegExp.Pattern = "^5085"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "С85"
Else
objRegExp.Pattern = "^5081"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "С81"
Else
If Cells(i, ncolumn).Value Like "*3200*" Then
Cells(i, ncolumn + 1).Value = "ЦВО"
Else
objRegExp.Pattern = "^3200-3000"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Or Cells(i, ncolumn).Value Like "*3000*" Or Cells(i, ncolumn).Value Like "*ЭМЦ*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3000*" And Cells(i, ncolumn4).Value Like "Бирка *" Then
Cells(i, ncolumn + 1).Value = "ЭМЦ"
Else
objRegExp.Pattern = "^3600"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Or Cells(i, Ncolumn2).Value Like "КРП.*.3600*" And Cells(i, ncolumn4).Value Like "Бирка *" Then
Cells(i, ncolumn + 1).Value = "ПММ"
Else
If Cells(i, ncolumn).Value Like "*3000*" And Cells(i, ncolumn4).Value Like "Плата*" Or Cells(i, ncolumn).Value Like "3400*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3400*" And Cells(i, ncolumn4).Value Like "Бирка *" Or Cells(i, ncolumn).Value Like "*ЭМЦ*" And Cells(i, ncolumn4).Value Like "Плата*" Then
Cells(i, ncolumn + 1).Value = "МЦ"
Else
If Cells(i, ncolumn).Value Like "*3300*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3300*" And Cells(i, ncolumn4).Value Like "Бирка *" Or Cells(i, ncolumn).Value Like "*3340*" Then
Cells(i, ncolumn + 1).Value = "ПКМ"
Else
If Cells(i, ncolumn).Value Like "*3100*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3100*" And Cells(i, ncolumn4).Value Like "Бирка *" Or Cells(i, ncolumn).Value Like "*CМЦ*" Or Cells(i, ncolumn).Value Like "*СМЦ*" Then
Cells(i, ncolumn + 1).Value = "СМЦ"
Else
objRegExp.Pattern = "^3800|^3801"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "ОВК"
Else
If Cells(i, ncolumn).Value Like "2400*" Then
Cells(i, ncolumn + 1).Value = "БИХ"
Else
If Cells(i, ncolumn).Value Like "2300*" Then
Cells(i, ncolumn + 1).Value = "ХТС"
Else
If Cells(i, ncolumn).Value Like "1240*" Then
Cells(i, ncolumn + 1).Value = "1240"
Else
If Cells(i, Ncolumn2).Value Like "РСТ.*" Then
Cells(i, ncolumn + 1).Value = "Уланов"
Else
If Cells(i, ncolumn).Value Like "3050*" Then
Cells(i, ncolumn + 1).Value = "ЦГО"
Else
objRegExp.Pattern = "210[0-4]"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "ОМЭ"
Else
If Cells(i, ncolumn).Value = "" Or Cells(i, ncolumn).Value = "-" Or Cells(i, ncolumn).Value = "--" Or Cells(i, ncolumn).Value = "---" Or Cells(i, ncolumn).Value = "----" Then
Cells(i, ncolumn + 1).Value = "МЦМ"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
i = i + 1
Loop
Do While Cells(i, Ncolumn2).Value <> Empty 'поставил на "Обозначение" т.к. обрывался на пустой ячейке
objRegExp.Pattern = "^5085"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "С85"
Else
objRegExp.Pattern = "^5081"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "С81"
Else
If Cells(i, ncolumn).Value Like "*3200*" Then
Cells(i, ncolumn + 1).Value = "ЦВО"
Else
objRegExp.Pattern = "^3200-3000"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Or Cells(i, ncolumn).Value Like "*3000*" Or Cells(i, ncolumn).Value Like "*ЭМЦ*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3000*" And Cells(i, ncolumn4).Value Like "Бирка *" Then
Cells(i, ncolumn + 1).Value = "ЭМЦ"
Else
objRegExp.Pattern = "^3600"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Or Cells(i, Ncolumn2).Value Like "КРП.*.3600*" And Cells(i, ncolumn4).Value Like "Бирка *" Then
Cells(i, ncolumn + 1).Value = "ПММ"
Else
If Cells(i, ncolumn).Value Like "*3000*" And Cells(i, ncolumn4).Value Like "Плата*" Or Cells(i, ncolumn).Value Like "3400*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3400*" And Cells(i, ncolumn4).Value Like "Бирка *" Or Cells(i, ncolumn).Value Like "*ЭМЦ*" And Cells(i, ncolumn4).Value Like "Плата*" Then
Cells(i, ncolumn + 1).Value = "МЦ"
Else
If Cells(i, ncolumn).Value Like "*3300*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3300*" And Cells(i, ncolumn4).Value Like "Бирка *" Or Cells(i, ncolumn).Value Like "*3340*" Then
Cells(i, ncolumn + 1).Value = "ПКМ"
Else
If Cells(i, ncolumn).Value Like "*3100*" Or Cells(i, Ncolumn2).Value Like "КРП.*.3100*" And Cells(i, ncolumn4).Value Like "Бирка *" Or Cells(i, ncolumn).Value Like "*CМЦ*" Or Cells(i, ncolumn).Value Like "*СМЦ*" Then
Cells(i, ncolumn + 1).Value = "СМЦ"
Else
objRegExp.Pattern = "^3800|^3801"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "ОВК"
Else
If Cells(i, ncolumn).Value Like "2400*" Then
Cells(i, ncolumn + 1).Value = "БИХ"
Else
If Cells(i, ncolumn).Value Like "2300*" Then
Cells(i, ncolumn + 1).Value = "ХТС"
Else
If Cells(i, ncolumn).Value Like "1240*" Then
Cells(i, ncolumn + 1).Value = "1240"
Else
If Cells(i, Ncolumn2).Value Like "РСТ.*" Then
Cells(i, ncolumn + 1).Value = "Уланов"
Else
If Cells(i, ncolumn).Value Like "3050*" Then
Cells(i, ncolumn + 1).Value = "ЦГО"
Else
objRegExp.Pattern = "210[0-4]"
If objRegExp.Test(Cells(i, ncolumn).Value) = True Then
Cells(i, ncolumn + 1).Value = "ОМЭ"
Else
If Cells(i, ncolumn).Value = "" Or Cells(i, ncolumn).Value = "-" Or Cells(i, ncolumn).Value = "--" Or Cells(i, ncolumn).Value = "---" Or Cells(i, ncolumn).Value = "----" Then
Cells(i, ncolumn + 1).Value = "МЦМ"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
i = i + 1
Loop