» Excel VBA (часть 2)

Фамилия = INDEX("C:C";MATCH($B$1;$B:$B;))

Что я опять не так написал? Ругается на ";" Как сделать =ИНДЕКС(C:C;ПОИСПОЗ($B$1;$B:$B;)) (вместо $B$1 можно написать переменную "табельный")

Как это уже.... достало. Вторые выходные угробил!

Добавлено:
SERGE_BLIZNUK

Цитата:

дай угадаю - а Вас оригинальный офис на АНГЛИЙСКОМ языке?!

Да я в курсе, как работают эти функции, но в листах и таблицах. а теперь мне их надо записать в код VBA. я не знаю как это сделать!
Тупо делал так: значения переменных ставил в ячейки; в другие ячейки - формулы. И после вычисления vba использовал результаты этих вычислений. Но формул стало очень много и после каждого "ввод" excel их все пересчитывает. И убивает на это много времени. Надо расчет формул вести в VBA а не на листах.


Добавлено:
или проще:

Как в коде VBA записать

A=ИНДЕКС('Отчёт по БД АСУТ'!D:D;ПОИСКПОЗ($B$1;'Отчёт по БД АСУТ'!$B:$B;))
(Табельный=$B$1)

Всем привет.
Ребята помогите решить проблему с TextBox,TextBox пропадает
Выкладываю файл
Заранее благодарю!

http://slil.ru/24754255

SERGE_BLIZNUK
Спасибо!
Я тут еще решил ее доработать чтобы она выводила суммы для комментариев по каждому столбцу, получилось [more=следующее]
Option Explicit

Dim Days(9, 30) As Integer
Dim Peoples(9) As String


Function DimExists(Name As String) As Integer
Dim i As Integer
DimExists = -1
For i = 0 To 9
If Peoples(i) = Name Then DimExists = i
Next i
End Function

Sub ScanAndSumComments()
Dim pComments As New Scripting.Dictionary
Dim i, j, iRow, RowFirst, rowLast, RowItogFirst, PeopleCount As Long
Dim iCol, ColSourceFirst, ColSourceLast, oo As Long
Dim vEntry As String
Dim ColItogComment, ColItogQ As String



' ---------------- Исходная таблица ----------------
ColSourceFirst = 2 ' вместо - "B" исходный столбец, в котором числа (и комментарии)
ColSourceLast = 6 ' а это столбец "D"
RowFirst = 6 ' строчка, где начинаются комментарии (первая строка исходной таблицы)

rowLast = 9 ' строчка, где заканчиваются комментарии (последняя строка исходной таблицы)
' в принципе последнюю строчку легко определять автоматически -
' но в постановке задачи такого не было

' ---------------- итоговая таблица ----------------
ColItogComment = "A" ' итоговый столбец, куда результат - список комментариев
ColItogQ = "B" ' итоговый столбец, куда результат - сумма
RowItogFirst = 11 ' строчка, куда начинать писать итоги
PeopleCount = 0
For iCol = ColSourceFirst To ColSourceLast
For iRow = RowFirst To rowLast
If Not IsEmpty(Cells(iRow, iCol)) Then
If Cells(iRow, iCol).Comment Is Nothing Then
' ну нет тут примечания
vEntry = "_no-comments_"
Else
vEntry = Trim(Cells(iRow, iCol).Comment.Text)
End If
If DimExists(vEntry) < 0 Then
Peoples(PeopleCount) = vEntry
Days(PeopleCount, iCol) = CInt(Cells(iRow, iCol).Value)
PeopleCount = PeopleCount + 1
Else
oo = DimExists(vEntry)
Days(oo, iCol) = Days(oo, iCol) + CInt(Cells(iRow, iCol).Value)
End If
End If
Next iRow

Next iCol

For i = 0 To PeopleCount - 1
Cells(15 + i, ColItogComment).Value = Peoples(i)
For j = ColSourceFirst To ColSourceLast
Cells(15 + i, j).Value = Days(i, j)
Next j
Next i

End Sub
[/more]
Но возник пока неотловленный глюк - если комментариев больше чем столбцов, то считает неправильно

vasiliy74
SERGE_BLIZNUK
Kafka17

Всем спасибо. Проблему решили. Причем достаточно банально:
Несмотря на то, что машина - четверый пень с двумя гигами мозгов, памяти ему действительно не хватало. Хоть и железный, но предел свой имеет. Видать его и достигли... ну, или почти достигли. Он просто не мог разом пересчитать все формулы (повторяюсь - около 10 книг по 5 листов мин. на каждой, строк 600 на листе и еще куча столбцов. Везде формулы, привязанные к данным на одном листе - в каждой книге свой).
Решение: нефиг держать открытыми столько книг. Открыл, поработал, сохранил, закрыл.
Работает как миленький.
Взяли себе на заметку - в будущем будет изменена технология, чтобы избавится от всей этой бойды с формулами...

вернулся я к своей функции почемуто неполучается исполнить код по Debug отследил работает только до цикла??

[more=Code]

Public Function test_realized_fifo(operation As Range, rate As Range) As Long
Dim size As Integer 'переменная для определения размера
Dim c1 As Range 'временная переменная для перебора значений
Dim index As Byte 'временная переменная для перебора строк
Dim sum As Range 'временная переменная для итоговой суммы

Dim in_r0 As Range 'Кол-во
Dim in_r1 As Range 'Курс
Dim in_r2 As Range 'Сумирование покупок по текущею

'копирование кол-ва
Set in_r0 = operation
'копирование курсов
Set in_r1 = rate
'Создание строки сумм
index = 1
sum = 0
For Each c1 In in_r0
If c1.Value > 0 Then
sum = sum + c1.Value
in_r2(index, 1).Value = sum + c1.Value
Else
in_r2(index, 1).Value = 0
End If
index = index + 1
Next c1
'Set index = Nothing
'Set c1 = Nothing
'Set sum = Nothing
test_realized_fifo = 2
End Function


[/more]

Люди, снова вопрос появился... Как много я не знаю... /смеется/
Есть столбец, к примеру, EV. Номер столбца 152.
Ищу его макросом - последний пустой столбец. Нахожу его - получаю номер. А как мне получить его имя - само "EV".

Можно ли тое сделать или нет?

И SERGE_BLIZNUK - можно на "ты"... /смеется/ Да и всем можно...


Добавлено:
CEMEH

ИНДЕКС('Отчёт по БД АСУТ'!D:D;ПОИСКПОЗ($B$1;'Отчёт по БД АСУТ'!$B:$B))

Цитата:

отследил работает только до цикла

понял там по непонятной мне причине in_r0 Range почему то имеет два столбца??

нет всё нормально не туда посмотрел, не что не так понимаю
поправил код:


Код: For Each c1 In in_r0.Cells
If c1.Value > 0 Then
sum = sum + c1.Value
in_r2(index, 1).Value = sum + c1.Value
Else
in_r2(index, 1).Value = 0
End If
index = index + 1
Next c1

CEMEH

Цитата:

Как в коде VBA записать
A=ИНДЕКС('Отчёт по БД АСУТ'!D:D;ПОИСКПОЗ($B$1;'Отчёт по БД АСУТ'!$B:$B;))

а так пойдёт?

Код:
Sub test_VBA()
Dim w1 As Worksheet
Dim i As Long
Set w1 = ThisWorkbook.Worksheets("Отчёт по БД АСУТ")
On Error GoTo Err_Label
i = Application.WorksheetFunction.Match(Cells(1, "B"), w1.Range("B:B"), 0)
MsgBox Application.WorksheetFunction.Index(w1.Range("D:D"), i)
Exit Sub
Err_Label:
'NO MATCH!!
End Sub

Как сделать?
По нажатию кнопки в Экселе
создается новый лист и там определенная выборка из основного листа

nesta sandro

Цитата:

По нажатию кнопки создается новый лист

Private Sub CommandButton1_Click()
Dim NewSheet As Worksheet
Set NewSheet = ActiveWorkbook.Worksheets.Add 'Добавляем лист в нашу книгу
NewSheet.Name = "Новый Лист для Выборки"
End Sub
(это без проверки на ошибки/существование уже такого листа и т.п...


Цитата:

определенная выборка из основного листа

а что значит - "определённая"??
вторая и третья строка?? или весь пятый столбец?
или все чётные строки?...
телепаты всё в отпуске - конкретнее задачу формулируйте...

Использую Excel 2007, хотя могу откатиться до 2003. Написал макрос, вынес кнопку с ним на панель инструментов, но он работает только в пределах того файла, в котором создан. Мне нужно, чтобы эта кнопка срабатывала в любом открытом xls-файле, как это сделать?

griin

Цитата:

срабатывала в любом открытом xls-файле

прежде всего, почти уверен, что кнопка у Вас работает в КАЖДОМ открытом XLS
файле.
для проверки этого факта первой строчкой в макрос добавьте код:
msgbox "Кнопочка сработала!"

думаю, что вся обработка идёт для той книги, где находится код макроса.
чтобы это было не так, нужно написать принудительно
ActiveWorkBook.ActiveWorksheet (.Cells .Range и т.д....)

SERGE_BLIZNUK, нет "кнопочка сработала" не работает.

Описываю проблему подробнее. Макрос лежит в файле zzz.xls. Я открываю этот файл и макрос zzz.xls!macros вытаскиваю на панель инструментов в виде кнопки. Затем документ закрываю.

После этого открываю другой файл, жму кнопку созданную на предыдущем шаге и получаю сообщение об ошибке "Не удалось найти zzz.xls. Проверьте правильность ввода имени и расположения файла. При попытке отрытия файла из списка недавно используемых файлов убедитесь, что файл не был переименован, перемещен или удален". То есть, если я правильнопонимаю, проблема в том, что к кнопке привязывается имя файла и макроса в нем, но не путь к файлу с макросом, потому и не удается получить доступ к макросу из другого файла.

Вроде бы в старых версиях офиса можно было сохранять макросы в Normal.dot и тогда они спокойно работали во всех файлах, можно также сохранить макрос и 2007 офисе?

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

привет всем, где я могу посмотреть результаты отладочной печати? Debug.Print

Добавлено:
SERGE_BLIZNUK
Цитата:

msgbox "Кнопочка сработала!"

наверно ему нужно макрос как Addin прописать?

vasiliy74

В окне Immediate (Ctrl + G) в режиме VBA.

griin

Цитата:

Вроде бы в старых версиях офиса можно было сохранять макросы в Normal.dot и тогда они спокойно работали во всех файлах, можно также сохранить макрос и 2007 офисе?

с этого и надо было начинать!
Тогда нужно пользовать личную книгу макросов (personal.xls)
Этот файл запускается всегда в первую очередь и прячется в списке открытых файлов.

сохраните макрос обработки кнопки в файле Personal.xls и положите его в папку [more=XLStart]
Настройка запуска Microsoft Excel
Папки XLStart находятся по адресу «C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Excel» или «C:\Program Files\Microsoft Office\Office10».

Автоматическое открытие книги при запуске Microsoft Excel

В проводнике Windows переместите значок книги, которую требуется открывать, в папку «XLStart».
Если требуется, чтобы книга оставалась в текущем местоположении, воспользуйтесь командой Создать ярлык из меню Файл для создания ярлыка книги, а затем переместите ярлык в папку XLStart.
[/more]

vasiliy74
Цитата:

Debug.Print

по моему внизу, в статусной строке...


Цитата:

наверно ему нужно макрос как Addin прописать?

наверное... только я ещё не умею add-In делать ;-))) научите...






Добавлено:

Oyger
Цитата:

окне Immediate (Ctrl + G) в режиме VBA

Супер. Не знал. Спасибо!!

Oyger
да прикольно! Я его как то открывал и не понял зачем оно!

SERGE_BLIZNUK

Цитата:

с этого и надо было начинать!
Тогда нужно пользовать личную книгу макросов (personal.xls)
Этот файл запускается всегда в первую очередь и прячется в списке открытых файлов.

сохраните макрос обработки кнопки в файле Personal.xls и положите его в папку XLStart

Хммм... Сделал... Разместил файл personal.xls в каждой из приведенных папок по очереди, но в списке макросов мой макрос по прежнему не появился, и, как следствие, в другом файле не срабатывает...

Цитата:

Хммм... Сделал... Разместил файл personal.xls в каждой из приведенных папок по очереди

разумеется, excel закрывали и запускали заново?..

griin
Попробуйте поступить так:
меню "Сервис" - "Макросы" - "Начать запись"
там где "Сохранить в..." выберите "Личная книга макросов"
сделайте любое изменение в книге (ну смените ячейку, например) и остановите запись.
Закройте Excel.
Он спросит "Сохранять внесённые изменения в книге макросов?" - выберите "Да".

всё. у вас появился personal.xls
"C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLSTART\personal.xls"

теперь просто запустите Excel и убедитесь, что макрос доступен...

SERGE_BLIZNUK

Цитата:

а так пойдёт?

Я уж и не думал, что кто нить ответит. Спасибо!. сейчас буду пробовать.

Еще вопрос:
Есть ListBox, в нем надо сделать несколько столбцов. (в книжках описано как выбрать диапазон, или один столбец) а мне надо разные столбцы. Свойство RowSource.

Дорогой народ! Кто-нибудь знает почему при совместной работе с защищенной книгой, не работает Worksheets(name).Unprotect

когда снимаю общий доступ, то блоки вида

ActiveSheet.Unprotect
.
.
.
ActiveSheet.Protect

работают абсолютно нормально. А как только включаю общий доступ к книге, то пишет, что "Метод Unprotect завершен некорректно". Попросту - НЕ ФУРЫЧИТ, блин!

Как быть и что мне с этим делать???

CEMEH

По умолчанию в созданном ListBox всего 1 столбец. Поэтому какой бы Ты диапазон заполнения не указал - заполнится только первый столбец диапазона.

Чтобы в ListBox заполнились все столбцы диапазона задай число столбцов ListBox:
ListBox.ColumnCount = ...

Oyger
С этим то понятно, но мне надо не диапазон, а выбранные столбцы (выбранные диапазоны) Например как выбрать A1:A50, D1:D50, F1:F50

CEMEH

Боюсь что такой возможности в VBA просто нет - это же "облегченный" вариант VB.
Что могу тебе посоветовать:
К примеру Тебе надо вывести в списке столбцы

Цитата:

A1:A50, D1:D50, F1:F50

1) В RowSource задай весь диапазон: A1:F50. А потом ненужные столбцы в списке скрой - отведи под них нулевую ширину - ColumnWidths.
2) Пиши макрос с построчным заполнением данного списка...

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

griin

Цитата:

Написал макрос, вынес кнопку с ним на панель инструментов, но он работает только в пределах того файла, в котором создан. Мне нужно, чтобы эта кнопка срабатывала в любом открытом xls-файле, как это сделать?

Я сделал макрос, который добавляет в панель инструментов свои кнопки и отлично работает при нажатии на соотв. кнопку. Книг никаких открывать не надо.
Делал я это через свой AddIn, который размещал в папке оффиса Library. Также делал свои функции, которые потом использовал в ячейках рабочих книг (тоже через AddIn). Всё отлично работает. Офис 2000, русский.

To griin Если интересно - спрашивай.

Добавлено:

Цитата:

personal.xls

Ни разу не пользовался, думаю, что и не понадобится.

Можно, кстати, из какой-нибудь рабочей книги вызывать макрос, который находится в любой другой книге или ещё лучше - AddIn'е - (получается библиотека с нужным набором "инструментов").
Я так Api-функции реализую, чтобы громозкий код не таскать по всем книгам, которым он нужен. И легче стало ошибки исправлять или новые возможности добавлять, поскольку править надо всего-лишь 1 файл - AddIn, а не все файлы, что его используют.

Добавлено:
SERGE_BLIZNUK

Цитата:

прежде всего, почти уверен, что кнопка у Вас работает в КАЖДОМ открытом XLS

При условии, что файл-носитель кода кнопки загружен. Посему это можно сделать через автозапуск книги (что здесь предлагалось) или моим способом - через AddIn.
Только когда производете операции с данными на листе, то ссылку-объект на книгу или лист лучше не опускать, а указывать явно. И то и другое можно не использовать, поскольку в объекте на лист всегда есть ссылка на книгу, которой лист принадлежит (см. Parent).

Задачка такого плана:
Есть Excel, в нем макрос. и в конечном итоге надо передать данные в файл Word. Как это сделать? Кинте хоть маленький примерчек...

Тут на глаза книжечка попалась. Очень рекомендую таким как я (начинающим) можно даже в шапку ее поместить.
_http://www.rapidshare.ru/381930

CEMEH
А можно оглавление или кратенько, о чём книжка? А то анонимка какая-то получается.

nick7inc
Е.Н. Горных Программирование на VBA Учебное пособие.
http://www.rapidshare.ru/381930
ОГЛАВЛЕНИЕ
ВВЕДЕНИЕ
ПРОСТЕЙШАЯ АВТОМАТИЗАЦИЯ
Макросы
Редактирование макросов
Основные операторы языка VBA
Функции MsgBox и Inputbox
Форматирование значений данных
Функции пользователя
ОБЪЕКТЫ, СВОЙСТВА, МЕТОДЫ
Семейства объектов
Объект Application
Объект Workbook
Объект Worksheet и семейство Worksheets
Способы доступа к ячейкам. Объекты Range и Cells
Стандартные диалоговые окна
СОЗДАНИЕ ПОЛЬЗОВАТЕЛЬСКИХ ПРИЛОЖЕНИЙ
Создание пользовательских форм
Свойства, методы, события
Краткое описание элементов управления
Обработка ошибок
ПРАКТИЧЕСКИЕ ЗАДАНИЯ
Запись макросов в автоматическом режиме
Редактирование макросов
Создание пользовательских приложений

CEMEH

Цитата:

в конечном итоге надо передать данные в файл Word

В каком виде данные будут передаваться?
Содержимое ячеек подставлятся в Word как текст или диапазон - как таблица? А может график?.. Вставка будет производится в новый пустой документ или в уже существующий? Если в существующий, то в какое место - это для Word'а очень существенно?
Пиши конкретней: есть данные в таком-то виде. Надо вставить в Word чтобы было так-то и так-то... Тогда и помочь смогут лучше - именно по твоему вопросу.

Вот Тебе просто пример вставки в новй Word документ (предварительно в Excel подключи библиотеку Word'а):
Dim word_ As Word.Application
Set word_ = New Word.Application
word_.Documents.Add ' создаем новый документ Word
q = Cells(1, 1).Value ' то что будем вставлять в Word
word_.Selection.TypeText Text:=q ' собственно - вставляем в Word
word_.Visible = True ' делаем документ Word видимым, т.к. по умолчанию он не видим.