Elettracompany.com

Компьютерный справочник
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba проверка ячейки на пустоту

Excel / VBA: Check if Cell Reference returns 0

I wrote a VBA script that is supposed to hide rows that reference empty cells in another worksheet. So to speak, Sheet 1 contains cell A1, which is empty. Sheet 2’s cell A1 contains «Sheet1!A1», but prints a 0. My script is supposed to go through all the rows and hide the ones that contain empty references, but does not seem to be doing that. It would be great if you could help me figure out my mistake. Here is my code so far:

I’d be very grateful for any kind of help.

+ 1 Good question! Had me thinking for a while 🙂 – Siddharth Rout 05 авг. 12 2012-08-05 23:09:07

2 ответа

Using Cells(1, i) in a loop means that you are working along the columns in row 1 rather than down the rows in column A. I would suspect you want Cells(i, 1) instead.

As the calls to Cells and Rows are unqualifed, they will refer to the active sheet. From your description, it’s not 100% clear whether both should refer to the same sheet. It’s obvious, for example, that Sheet2!A1 cannot be empty as it contains a formula referring to Sheet1!A1 .

You may, therefore, want to check for empty cells on Sheet1 but actually hide the rows on Sheet2 . This would change the calls to Worksheets(«Sheet1»).Cells(i, 1) and Worksheets(«Sheet2»).Rows(i)

If things are more complicated and you need to both check for emptiness and hide rows on Sheet2 then you will either need to hide all rows with a zero value in column A (which may be a problem if any of the rows on Sheet1 actually contain zero as a value) or alter the formulas on Sheet2 to explicitly deal with empty cells on Sheet1 .

For example, the formula in Sheet2!A1 could be: =IF(ISBLANK(Sheet1!A1),#N/A,Sheet1!A1)

The check in your macro would then be:

You could just use the IsError check but that may hide genuine errors with the source data (e.g. division by zero)

Создан 05 авг. 12 2012-08-05 23:02:49 barrowc

Alex here is a slightly complicated way of doing this. But it works 🙂

Note: This will only work for Formulas which refer to sheet(s) in the same workbook or sheets of open workbooks.

Logic:

  1. This code identifies the cells which have the formulas and then loops through them
  2. Then while looping through all cells which have the formulas, it checks for the .Precedents of that cell. Here is the trickiest part. There is no easy way to get the .Dependents or .Precedents that lie on another Worksheet or Workbook. You may call it the limitation of this property. So I am using an alternative to get those 🙂
  3. Once I get the address of that cell, I am checking if they are empty or not and then based on the condition, I am hiding the cell.

CODE

SNAPSHOT

Sheet 1 before running the macro

This is how Sheet 2 Looks

And this is how the Sheet1 looks after the macro

Чтение и запись значения ячейки в VBA

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

Обращение к конкретной ячейке

Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

  • С помощью Range
  • С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Читать еще:  Comобъект excel application 1с

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:

  • Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
  • Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
  • Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.

Excel vba проверка ячейки на пустоту

Да бросьте, нафиг оно надо.

для тестирования брал файл выложенный mrdime, и заново созданный.

на обоих одинаковые результаты выдаются при строке If Range(col & CStr(i)).Value <> «» Then
если использовать If Not IsEmpty(Range(col & CStr(i))) Then
то выложенном файле результат неверный, а в заново созданном все в порядке. С выложенным помогает только очистка ячеек.
(для сравнения с выложенным файлом, просмотр организован только для диапазона A8:A16)

Выходит, что сравнение с «» работает более универсально, чем IsEmpty.

P.S. А в случае, если информация содержит спецсимволы, то мне кажется проще все
это безобразие затянуть целиком в vba и обрабатывать в нем, обращаясь к Excel только для вывода результатов.

Function emptyCol(ByVal col As String) As Boolean
Dim i As Integer
For i = 8 To 16
‘ If Not IsEmpty(Range(col & CStr(i))) Then
If Range(col & CStr(i)).Value <> «» Then
emptyCol = False
Exit Function
End If
Next i
emptyCol = True
End Function

Sub test()
Dim myCol As String
myCol = «A»
If emptyCol(myCol) Then
MsgBox «column » & CStr(myCol) & » empty»
Else
MsgBox «column » & CStr(myCol) & » not empty»
End If
End Sub

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору SAS888
* Давайте, все-таки, определим, какой метод быстрее.
А — Все уже определено. Меня не волнует, во сколько именно раз мой код быстрее Вашего. Важно то, что
1. Я это определил на глаз
2. Не выдавал это за безапелляционную истину.
3. Подтвердил свои утверждения отчетами.
4. Разница на порядок
5. Третьи библиотеки не используются
6. Я в сравнении не использовал заведомо более медленный вариант кода своего визави.
7. Алгоритм и его реализация более понятны конечному пользователю.
8. . и, следоваетльно, легче адаптируются под возможные изменившиеся условия
(Последние два пункта — чисто субъективное имо — это все-таки мой код.)

Читать еще:  Как сортировать столбцы в excel

Бэ — я с Вами буду взаимодействовать только после получения извинений за Вашу. э-э-э. которую Вы позволили себе при проведении замеров.

Цэ — Если — см п. Бэ, то можно и побеседовать на тему моих «неизвестных науке расчетов». Только странно, как они могут Вас интересовать, если Вы уже дали им такую саркастическую оценку. И, обращаю Ваше внимание, что Вы, несмотря на неоднократные просьбы, так и не привели ни одной цифры из Ваших замеров. Одни слова.

Добавлено:
DmitryPrint
Я файл скачал, но кактастрофически не хватает времени взглянуть. Чуть позже, ОК?

Вы не в то порядке употребили местоимения «мой» и «Ваш».

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Всем привет. Вводная часть — есть Delphi-проект, в котором посредством Afalina XLReport создается прайс-лист с несколькими именованными листами (sheet). Каким образом можно в данном отчете после его создания добавить в начало книги еще один лист с оглавлением, т.е. чтобы в оглавлении были перечислены все листы со ссылками, т.е. чтобы по клику можно было переходить на нужный лист? В идеале кроме названия листов туда же желательно добавить и структурированное содержимое каждого листа, содержащего цены на те или иные объекты. Заранее спасибо откликнувшимся, готовым помочь в создании — буду благодарен посредством WMZ.

У меня предложение — давайте забудем об использовании IsEmpty в качестве средства проверки содержимого ячейки на наличие-отсутствие чего-то. Это неправильно.

У меня это IsEmpty появилось в ответе w01f14 «Помогите сделать скрипт который проверит ячейку таблицы на пустоту». Почему я именно его взял? Не знаю. Я сейчас в Access-e плотно сижу, может на автомате написал. Впрочем, я оговорился в следующем же посте, но и меня это не насторожило, увы 🙁 А когда речь зашла об апострофах и прочей нечисти, я уже на автомате посчитал использование IsEmpty за панацею от этих бед. После этого Zloy_Gelud задал вопрос с уже конкретно используемым IsEmpty и понеслось.

Предлагаю обратиться к первоисточникам. Прежде всего, IsEmpty — это VBA-шная функция, не Экселя, т.е. общая для всего Офиса:
Function IsEmpty(Expression) As Boolean, Member of VBA.Information

IsEmpty(expression) — Returns a Boolean value indicating whether a variable has been initialized.

The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.

Remarks:
IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

Если кратко, то речь идет о проверке переменной — инициализированна она или нет.
Подсовывая ей выражение типа Cells(1,1).Value, мы передаем в нее переменную Value (свойство объекта Range) на предмет проверки ее на инициализацию. Не на проверку ее содержимого.

Я уже сокрушался по поводу убогости и скудности английского языка — вот и здесь (я всю тему просмотрел) все отчаянно ведутся на легко переводимое Empty — пустой. Как и я сам 🙂

Я пока не знаю, что с ним делать. Можно пытаться использовать Cells(1,1).SpecialCells(xlCellTypeConstants).Count
Но она странно работает с одиночной ячейкой. Надо ей в пару включать в проверяемый диапазон хотя бы еще одну ячейку, в которой заведомо содержится некое «нормальное» значение.

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору В первую очередь адресую это asbo и DmitryPrint, которые заинтересовались моим вопросом насчет создания макроса по поиску и удалению из листа всех пустых строк и колонок.
Наваял вот такой макрос. Пока использовал проверку лишь на .Value=»»
Пока получилось что-то такое, хотя конечно хотелось бы чтобы удалялись строки и колонки, заполенные пробелами и прочим содержимым, которое пропускает вышеуказанная проверка.
Хотелось бы услышать ваше мнение по этому поводу/ предложения по оптимизации кода.

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору На вскидку, — прежде всего я бы заменил
If Cells(I, CheckCol).Value <> «»
на
If Len(Cells(I, CheckCol).Value) > 0
Впрочем, с апострофом и это не проканает 🙂 (т.е. ячейки, содержащие один-единственный апостроф тоже будет удалены, но, как я понял из файла , это вполне удовлятворяет)

Читать еще:  Онлайн excel бесплатно без регистрации

Потом разделил бы код на четыре части:
1. Процедура определения рабочего диапазона
2. Процедура поиска пустых строк
3. Процедура поиска пустых колонок
4. Процедура удаления найденного по п.п. 2 и 3

Удобнее было бы и работать, и обсуждать 🙂 И, главное(!) — модифицировать условия включения элементов в удаляемые. А это, скорее всего, понадобится 🙂

Что нам это даст?
Ячейки с пробелами, например, таким образом все равно проходят проверку.
Может ты хотел предложить проверку Len(Trim(Cells(I, CheckCol).Value)) > 0? Тогда диапазоны с пробелами улетают как надо.
НО не уверен насколько подобная проверка корректна, если в ячейках нестроковые данные. Ведь эта функция предназначена для работы с данными типа «string».

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Бытует мнение, что так грамотнее. Вариант с Len должен работать быстрее, по-идее. Я в циклах именно его использую, а если всего один раз надо сравнить — пофиг, конечно. В первом случае сравниваются строки, вступает в действие стейтмент Options Compare (даже неявно). Просто пробел — частный случай строки с одним символом, как и пустая строка.

* Ячейки с пробелами, например, таким образом все равно проходят проверку.
Не понял. Результат идентичен:
? » » <> «»
True
? Len(» «) > 0
True

Ты же проверяешь на истинность некое утверждение. Я для этого и говорил о разделении кода, чтобы обсуждать его участки, а не ожидаемый конечный результат.

* эта функция предназначена для работы с данными типа «string».
Цитата:

Даже если говорить о первой части определения, то, я думаю, там используется неявное приведение типов к Str

* номера записывать в двухмерный массив
Быстрее запутаешься 🙂 Один массив для номеров строк, второй — для номеров колонок. Когда заработает — можно и в двумерный загнать, если особая на то нужда будет. Часто читабельность кода перевешивает плюсы быстродействия и исполнения принятых нотаций написания.

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Помогите пожалуйста дополнить, исправить или вообще заменить код!
Нужно чтобы в листе «Отработано» после вставки выделялась следующая свободная строка, указывая место для следующей вставки.

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

Sub отработано2()

‘ отработано2 Макрос
‘ Макрос записан 28.01.2011 (Менеджер)


Selection.Cut
Sheets(«Отработано»).Select
ActiveSheet.Paste
Sheets(» NEW Счета»).Select
Selection.Delete Shift:=xlUp
ActiveCell.Rows(«1:1»).EntireRow.Select
End Sub

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору asbo
В общем подшаманил код по твоим рекоммендациям (сделал два простых массива).
Вот что получилось:

Sub DelEmptyClmRws_v2()
‘Универсальный макрос для удаления пустых строк из листа
Dim R As Integer
Dim C As Integer
Dim I As Integer
Dim Y As Integer
Dim Z As Integer
Dim CheckCal As Integer
Dim CheckRow As Integer
Dim ContrSum As Integer
‘объявляем массивы с номерами пустых строк и столбцов
Dim EmptCol(1 To 100) As Integer
Dim EmptRow(1 To 100) As Integer

‘отменяем объединения ячеек, которые иногда создаются пользователями либо «кривыми» программами
ActiveSheet.UsedRange.MergeCells = False
‘1. определям «рабочую зону» нашего листа
‘строчки
R = ActiveSheet.UsedRange.Rows.Count
‘колонки
C = ActiveSheet.UsedRange.Columns.Count

‘2. ищем пустые колонки
‘задаем первую колонку, которая будет проверяться
CheckCol = 1
Y = 0
Do
ContrSum = 0
For I = 1 To R
If Len(Cells(I, CheckCol).Value) > 0 Then
‘вводим контрольную сумму, если попадется хотя бы одна непустая ячейка
ContrSum = ContrSum + 1
End If
Next I
‘если все ячейки пустые, записываем номер колонки
If ContrSum = 0 Then
Y = Y + 1
EmptCol(Y) = CheckCol
End If
CheckCol = CheckCol + 1
Loop While CheckCol 0 Then
‘вводим контрольную сумму, если попадется хотя бы одна непустая ячейка
ContrSum = ContrSum + 1
End If
Next I
‘если все ячейки пустые, записываем номер строчки
If ContrSum = 0 Then
Z = Z + 1
EmptRow(Z) = CheckRow
End If
CheckRow = CheckRow + 1
Loop While CheckRow Всего записей: 2827 | Зарегистр. 04-01-2005 | Отправлено: 17:54 19-03-2011 | Исправлено: mrdime, 18:46 19-03-2011

Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору mrdime, я бы процедуру удаления строк поместил перед процедурой поиска колонок — сократится количество ячеек к обработке. Если это приемлемо по логике работы и эргономике, конечно. Сорри, что я последовательно правлю — нет врмени.

Добавлено:
Да, чуть не забыл! Удалять сзаду надо начинать!

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector
×
×