Excel 2003: почему данный макрос выдает Type mismatсh?

[rdva]

Код: Выделить всё

Sub Brand()
rwIndex = 7

With Worksheets("(1) Каталог НАШИ").Cells(rwIndex, "B")
    Do While .Value > 0
    If Range("C" & rwIndex & ":" & "I" & rwIndex) = 0 Then
            MsgBox ("Brand!")
            rwIndex = rwIndex + 1
        End If
    Loop
End With
End Sub



Останавливается с ошибкой на этой строке: If Range("C" & rwIndex & ":" & "I" & rwIndex) Then

[Genyaa]

Потому что Range возвращает объект типа Range, а сравнивается с 0 - числовой целой константой... несоответствие типов сравниваемых данных, однако... Type mismatch!

Может сумму нужно сосчитать или еще что-нибудь... но Range области нескольких ячеек не может вернуть одно число...

[bi-lya]

Я так понял, что нужно в строках, начиная с 7-ой, находить какие-то значения, и если ни одного значения найдено не будет, то пишем сообщение и переходим на другую строку? Если так, то (в зависимости от данных и поставленной задачи) ИЛИ как Genyaa предлагает - просуммировать значения ячеек на этом диапазоне ИЛИ еще одним вложенным For...Next или Do...Loop просканировать этот кусок строки на наличие искомого

[Чудовище под кроватью]

Do While тут совершенно не нужен (он вообще мало где употребляется). Надо использовать For Next:

Код: Выделить всё

'при таком цикле rwIndex будет меняться от 7 до номера строки
'последней НЕпустой ячейки столбца В

For rwIndex=7 To Worksheets(твой_лист).Range("B65535").End(xlUp).Row
...
Next rwIndex

или

Код: Выделить всё

'при таком цикле rwIndex будет меняться от 7 до номера последней
'строки использованного на листе пространства.

For rwIndex=7 To Worksheets(твой_лист).UsedRange.Rows.Count
...
Next rwIndex

If Range("C" & rwIndex & ":" & "I" & rwIndex) = 0 Then

А на словах это условие как выглядит?

Если условие: сумма ячеек данной строки в столбцах С:I = 0, то в коде оно выглядит:

Код: Выделить всё

If Application.WorksheetFunction.Sum(Range(Cells(rwIndex,3),Cells(rwIndex,9))) = 0 Then

Можно, конечно, обращаться и так:

Range("C" & rwIndex & ":" & "I" & rwIndex)

, но тогда будет потеря по быстродействию из-за трех(!) операций конкатенации на каждом шаге.

Если условие:
все ячейки данной строки в столбцах C:I - пустые, то:

Код: Выделить всё

If Application.WorksheetFunction.CountA(Range(Cells(rwIndex,3),Cells(rwIndex,9))) = 0 Then

P.S.: а переменные, по-хорошему, надо объявлять

Код: Выделить всё

Dim rwIndex As Long

P.P.S.: практически все это можно сделать, не используя VBA. - Окно с сообщением тут обязательно? Если нет, то проще сделать условное форматирование с двумя условиями и вместо таблички строки, попадающие под эти два условия будут выделяться (цветом, шрифтом и т.д.). Быстро, легко, автоматически.

[rdva]

Всем спасибо за советы, задача на словах такая (в первом топике набросал макрос просто для примера):
Если в ячейка Bx непустая, а ячейки Cx:Ix пустые, то скопировать ячейку Bx в Ax.
Делать это до конца скписка (Пока не попадется пустая Bx ячейка).

Попробовал If Application.WorksheetFunctions.CountA(Range(Cells(rwIndex, 3), Cells(rwIndex, 9))) = 0 Then
Выдает ошибку "Object Doesn't support this property or method"

[Чудовище под кроватью]

rdva
ну, могу, конечно, скрипт написать, но по опыту знаю, что намного проще и логичнее не использовать VBA там, где можно обойтись стандартными средствами. А тут можно как раз обойтись: в ячейку A1 пиши формулу:
=ЕСЛИ(И(НЕ(ЕПУСТО(B1));СЧЁТЗ(C1:I1)=0);B1;"")
И размножь ее на нужное количество строк. - Хоть вручную, хоть скриптом (через AutoFill, разумеется а не для кадой ячейки отдельно ).

З.Ы.: ошибся - там не WorksheetFunctions, а WorksheetFunction

[bi-lya]

Может быть так?

Код: Выделить всё

Public Sub Nechto()
a = 1
With ActiveWorkbook.ActiveSheet
    Do While IsEmpty(.Cells(a, 2)) = False
        For b = 3 To 10
            If IsEmpty(.Cells(a, b)) = False Then
                Exit For
            End If
        If b = 10 Then Cells(a, 1).Value = .Cells(a, 2).Value
        Next b
    a = a + 1
    Loop
End With
End Sub


[Чудовище под кроватью]

Может быть так?

Попробуй запустить такой код имея хотя бы 1000 строк .
Не надо изобретать велосипед - любые стандартные средства Excel на несколько порядков быстрее VBA процедур, выполняющих те же функции, как бы грамотно они не были написаны.

Код: Выделить всё

Sub SmplFil()
'пишем в ячейку А7 формулу
Cells(7, 1).FormulaR1C1 = _
        "=IF(AND(NOT(ISBLANK(RC[1])),COUNTA(RC[2]:RC[8])=0),RC[1],"""")"
'заполняем этой формулой все ячейки в столбце А в используемом диапазоне
    Range(Cells(7, 1), Cells(Cells(7, 2).End(xlDown).Row, 1)).FillDown

End Sub

- скрипт в два действия. Без циклов.

[bi-lya]

А это насчет Do...Loop для нашего общего друга:

Код: Выделить всё

Public Sub Nechto1()
a = 1
b = 3
With ActiveWorkbook.ActiveSheet
    Do While IsEmpty(.Cells(a, 2).Value) = False
        Do While IsEmpty(.Cells(a, b).Value) = True
             b = b + 1
            If b = 10 Then
                .Cells(a, 1).Value = .Cells(a, 2).Value
                Exit Do
            End If
        Loop
        b = 3
        a = a + 1
    Loop
End With
End Sub


Товарисч проверь, все работает и там и там!!! (Сделал ровно 1000 строк - обработка чуть меньше 1c (тоже замерил!!!)
А про велосипед полностью согласен!

[Чудовище под кроватью]

Товарисч проверь, все работает и там и там!!! (Сделал ровно 1000 строк - обработка чуть меньше 1c (тоже замерил!!!)
А про велосипед полностью согласен!

Ну вот запустил твой второй код при 65535 записях. После долгого висения он выдал выдал ошибку 1004, поскольку не нашел строку с номером 65537. Если это называется работает, то я - летающий енот-мышкогрыз.

P.S.: Циклы For Next работают быстрее - можешь проверить. Вообще, где-то была статья или пост про повышение быстродействия кода VB. Там доходчиво объяснялось, что, как и почему.

[bi-lya]

Код: Выделить всё

Sub SmplFil()
'пишем в ячейку А7 формулу
Cells(7, 1).FormulaR1C1 = _
        "=IF(AND(NOT(ISBLANK(RC[1])),COUNTA(RC[2]:RC[8])=0),RC[1],"""")"
'заполняем этой формулой все ячейки в столбце А в используемом диапазоне
    Range(Cells(7, 1), Cells(Cells(7, 2).End(xlDown).Row, 1)).FillDown

End Sub

- скрипт в два действия. Без циклов.

no commetn...
Уважаемый, а на тысячу строк запускали? И с изменяемым диапазоном? Я угасаю

[rdva]

Чудовище под кроватью - Дело в том, что это только часть работы, некоторые куски которой уже написаны на VBA, которые нужно объеденить воедино, ну и доделать кое-что. По сравнению с перспективой ручной обрабоки (30000 строк) любая автоматизация, даже сравинительно медленная - ну вы меня понимаете

bi-lya
Попробовал такую конструкцию

Код: Выделить всё

With Worksheets("mybook.xls")
   .Cells(a, 1).Value = .Cells(a, 2).Value
End With


Что-то не работает, остается пустой ячейка A2, ошибок не выдает

PS: Как реализовать проверку
если в ячейке текст жирныйм шрифтом, то...
?

[Чудовище под кроватью]

Уважаемый, а на тысячу строк запускали? И с изменяемым диапазоном? Я угасаю

Запускал свой код на 65535 строк. Что есть изменяемый диапазон? Сколько строк заполнено, на столько код формулу и размножит.

[Чудовище под кроватью]

Чудовище под кроватью
Как реализовать проверку
если в ячейке текст жирныйм шрифтом, то...
?

Код: Выделить всё

If Cells(1,1).font.bold Then

[bi-lya]

Так а каким образом Вы его запускали на 65537 строк? Я надеюсь, не изменив значение в If...Next. Ну ладно, хоть Вы меня и заставляете заниматься хренью нездоровой, вот Вам код с обработкой "Ошибки 65537"

Код: Выделить всё

Public Sub Nechto1()
a = 1
b = 3
With ActiveWorkbook.ActiveSheet
    Do While IsEmpty(.Cells(a, 2).Value) = False
        Do While IsEmpty(.Cells(a, b).Value) = True
             b = b + 1
            If b = 10 Then
                .Cells(a, 1).Value = .Cells(a, 2).Value
                Exit Do
            End If
        Loop
        b = 3
        a = a + 1
        if a=65537 then exit do
    Loop
End With
End Sub


Все, флейм завязал

[bi-lya]

rdvaИзвините, отвлекся. Это (как я понял из вашей постановки задачи) работает когда столбец B заполнен с В1 до любой строки. Если нужно с какой-то другой строки - измените первоначальное значение переменной а. Ну а ваш пример

Код: Выделить всё


   .Cells(a, 1).Value = .Cells(a, 2).Value


работает когда находимся в активной книге и юзаем рабочий лист (см. мои примеры)
Ну а с жирным шрифтом что-то подобное:

Код: Выделить всё

   If .Cells(a, 2).Font.Bold = True Then .Cells(a, 1).Value = .Cells(a, 2).Value


[Чудовище под кроватью]

Так а каким образом Вы его запускали на 65537 строк?

Скопировал твой код себе, на листе заполнил столбцы C:I на все строки и запустил код. В коде ничего не менял.

Вы меня и заставляете заниматься хренью нездоровой

Это исключительно собственная инициатива - я никого не заставлял.

вот Вам код с обработкой "Ошибки 65537"

Код: Выделить всё

Public Sub Nechto1()
a = 1
b = 3
With ActiveWorkbook.ActiveSheet
    Do While IsEmpty(.Cells(a, 2).Value) = False
        Do While IsEmpty(.Cells(a, b).Value) = True
             b = b + 1
            If b = 10 Then Exit Do
        Loop
        If b < 10 Then .Cells(a, 1).Value = .Cells(a, 2).Value
        b = 3
        a = a + 1
        if a=65537 then exit do
    Loop
End With
End Sub



Ну, если уж тебе так нравятся циклы, то вот твой код в нормальном виде:

Код: Выделить всё

Sub smpl()
    Dim Cell As Range
    Application.ScreenUpdating = False
    For Each Cell In Range(Cells(7, 1), Cells(Cells(7, 2).End(xlDown).Row, 1))
        With Cell
            If Not (IsEmpty(.Offset(0, 1))) And WorksheetFunction.CountA _
               (Range(.Offset(0, 2), .Offset(0, )) = 0 Then Cell = .Offset(0, 1)
        End With
    Next Cell
    Application.ScreenUpdating = True
End Sub

Но даже в таком оптимизированном виде код проигрывает простому автозаполнению. А все потому что "родные" функции Excel выполняет на порядки быстрее.

Все, флейм завязал

Аналогично.

[bi-lya]

"А Вы свой код на работоспособность проверяли? Он не решает поставленную задачу"
Извините, забрал слова обратно

[Чудовище под кроватью]

Извинения приняты.

[KL]

А Вы свой код на работоспособность проверяли? Он не решает поставленную задачу
1. Работает наоборот
2. Не выполняется условие автора вопроса "... (Пока не попадется пустая Bx ячейка)..."
И вообще - на кой тянуть все ячейки в столбце, если заполненных 10 штук? О какой тут можно говорить оптимизации? Здесь никакой ScreenUpdating не спасет...

Вы извините что я влезаю, но если заполненных ячеек 10 штук (ну или менее 16.384 штук) то по-моему:

1) цикл "bi-lya" для 30000 строк - чудовищное извращение
2) усл. форматирование "Чудовища под кроватью" для 30000 строк - умеренное, но тоже извращение из-за его летучести (будет постоянно по делу и без дела пересчитывать дикое кол-во формул)
3) самое быстрое решение - через SpecialCells, но есть ограничение в 8.192 несмежных области (т.е. без риска 16.383 ячейки)
4) самое оптимальное решение - Range.Find + цикл

И еще, и тоже по-моему, флейма здесь нет и в помине, т.к. дискуссия аргументированная и сосредоточена на решении, а не на личностях.

[bi-lya]

KL
Приветствую здоровую критику!!!
Что ж, на 30000 строках я, (согласно Ваших доводов) проиграл...
Но! Что есть функция Find(если ее рассмотреть на уровне ее написания)? Глупо сравнивать, конечно, написание кодов на таких разных уровнях и тем не менее - какие операции она проводит с ячейкой, с диапазоном ячеек? Если можете, приведите ее код, не думаю что в Ехселе проводится индексация и последующая работа только с задействованными ячейками. Так же (заострю Ваше внимание) в моем коде при определенных условиях цикл прекращается. Может, все-таки не такой уж я извращенец?! По-моему, лишнего мой код не делает
На всякий случай все-же - rdva, я код поправил, если что

[Чудовище под кроватью]

Вы извините что я влезаю, но если заполненных ячеек 10 штук (ну или менее 16.384 штук) то по-моему:
2) усл. форматирование "Чудовища под кроватью" для 30000 строк - умеренное, но тоже извращение из-за его летучести (будет постоянно по делу и без дела пересчитывать дикое кол-во формул)

Там нет условного форматирования. Макрос заполнит столбец А в используемом диапазоне формулами. - Это раз.
Если напрягает пересчет - можно сделать аналогичную пользовательскую функцию, но не прописывать в ней

Код: Выделить всё

Application.Volatile

- все, проблема решена - функция делает то же самое, но пересчитываться будет только если ей дать указание на пересчет. Другой способ - стереть формулы во всем диапазоне, оставив только значения - Это два. Но я все же не думаю, что доли секунды, которые уйдут на пересчет всех формул, кого-то напрягут.

3) самое быстрое решение - через SpecialCells, но есть ограничение в 8.192 несмежных области (т.е. без риска 16.383 ячейки)
4) самое оптимальное решение - Range.Find + цикл

Полный код процедуры в студию.

[Чудовище под кроватью]

KL
Так же (заострю Ваше внимание) в моем коде при определенных условиях цикл прекращается.

Как ни старайся, а переплюнуть по быстродействию экселевские функции VBA не может. Может только достичь примерно такой же скорости, если использовать WorksheetFunction. Поэтому, всегда надо по возможности использовать эти функции в коде (даже если тебе кажется, что твой код оптимальнее).

[KL]

Что есть функция Find

Find - не функция, а метод.

Если можете, приведите ее код,

К сож. нет времени писать код, но по-моему я здесь что-то подобное публиковал - попробуй поиск по Find и KL.

не думаю что в Ехселе проводится индексация и последующая работа только с задействованными ячейками.

Именно: http://www.decisionmodels.com/calcsecrets.htm

Так же (заострю Ваше внимание) в моем коде при определенных условиях цикл прекращается.

да, но это лишь частный случай и при условии что в данных нет пробелов.

Может, все-таки не такой уж я извращенец?!

этого само собой

По-моему, лишнего мой код не делает

при условии что в данных нет пробелов и они начинаются с первой строки и нет других условий.

[KL]

Там нет условного форматирования. Макрос заполнит столбец А в используемом диапазоне формулами.

пардон привиделось

Если напрягает пересчет

уже не напрягает

3) самое быстрое решение - через SpecialCells, но есть ограничение в 8.192 несмежных области (т.е. без риска 16.383 ячейки)
4) самое оптимальное решение - Range.Find + цикл

Полный код процедуры в студию.

снимается

[bi-lya]

Уважаемые, мне дико стыдно, но пока не могу сообразить, что задать в качестве What для метода Find
Хочу провести эксперимент на древнем компьютере - прогнать по нескольким параметрам свой код и код с Find. А как вы думали - все по серьезному!!!

[Чудовище под кроватью]

Уважаемые, мне дико стыдно, но пока не могу сообразить, что задать в качестве What для метода Find

А что ты хочешь искать? То и запиши в What
Какой свой код?

[bi-lya]

В What я хочу зарядить ИЛИ является ли Range пустым диапазоном, ИЛИ есть ли в нем какие-либо данные (числовые, строковые - короче, любые).
А "свой код" - код, насчет которого мы с вами вчера долго дискутировали (см. с 7 ответа) с Next...For и второй с Do...Loop

[Чудовище под кроватью]

В What я хочу зарядить ИЛИ является ли Range пустым диапазоном, ИЛИ есть ли в нем какие-либо данные (числовые, строковые - короче, любые).

Find ищет определенные ячейки (с определенным значением, комментарием или формулой). Он ничего не определяет - он возвращает ячейку(и), удовлетворяющую условию.
Теоретически, можно попробовать запихать туда Empty:

Код: Выделить всё

If Not Range("A1:D20").Find(Empty, lookin:=xlValues) is Nothing Then

Аналогично можно вместо Find использовать SpecialCells (дает диапазон всех ячеек определенного типа) с параметром xlTypeBlanks.
Но зачем использовать методы? Определить ближайшую пустую ячейку можно просто обратившись к свойству .End объекта Range.

[KL]

Аналогично можно вместо Find использовать SpecialCells (дает диапазон всех ячеек определенного типа) с параметром xlTypeBlanks.
Но зачем использовать методы? Определить ближайшую пустую ячейку можно просто обратившись к свойству .End объекта Range.

Find на самом деле предлагался не для этого, а для проскакивания пустых ячеек в ранге 300000 строк с непоследовательными данными. SpecialCells, как я уже сказал ранее, имеет лимит в 8.192 несмежных области. В любом случае признаю, что предложения основывались на неверно понятой задаче