» Excel VBA

Pantera3587

Цитата:

ActiveCell.Offset(0, 1).Select
i = ActiveCell ' здесь вроде как присваивается переменной выделенная первой активная ячейка, выделенная первой

После того, как Вы выделили другую ячейку методом Select, эта ячейка стала ActiveCell. В переменную i записывается значение в новой ячейке. Если Вам нужен адрес 1-й ячейки используйте i=ActiveCell.Address. Перед Select!
Подставляйте эту переменную (i) в ГПР так же, как Вы делаете с nr, использую конкатенацию строк (&).

Да, по умолчанию свойство Address возвращает абсолютный адрес, типа $A$1. Если нужно это изменить, используйте
Код: ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

Yuk

Сделала, только не знаю, правильно ли, т.к. после нажатия кнопки ок в первом диалоговом окне выдает ошибку: Run-Time "91": object variable or With block variable not set.
Что не так?
Sub МакросГПР2()

Dim str As String
Dim i As Range
Dim nr As Variant

str = InputBox("Введите заголовок столбца: ")
i = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
ActiveCell.Select
ActiveCell.Formula = str

ActiveCell.Offset(0, 1).Select
nr = InputBox("Введите номер строки: ")

ActiveCell.FormulaR1C1 = "=HLOOKUP(" & i & ",C1:C3," & nr & ",FALSE)"

End Sub

Кто знает как изменить выделение ячейки с черного цвета на красный?

Pantera3587
ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) - возвращает строковое значение типа "A1", присвоение интерфейсу i (Range) и вызывает ошибку

Pantera3587
Проблем несколько. Одна, как уже упомянул AndVGri в несоответствии типа переменной i. Это почему у вас Run-Time ошибка. Другая - формула должна подставляться через свойство .Formula, а не .FormulaR1C1.

Код: Dim i As String
...
ActiveCell.Formula = "=HLOOKUP(" & i & ",C1:C3," & nr & ",FALSE)"

Yuk
Поменяла Range на Variant и исправила на .Formula. Все заработало. Спасибо тебе иAndVGri.

Цитата:

Вы уверены, что вам нужна формула HLOOKUP (ГПР), а не VLOOKUP (ВПР)?

Просто изучала эту функцию и мне надоело каждый раз менять в формуле номер строки и исходное значение. Вот и подумала, что это можно как-то усовершенствовать. По моему получилась неплохая штука. Этот вариант можно наверное применить и к ВПР. Надо попробовать.
Еще раз спасибо за помощь!





Добавлено:
Yuk
Попробовала вместо ГПР использовать ВПР. Работает. Но есть одна проблема, если в искомой ячейке стоит дата, то выдает Н/Д, но стоит поменять ячейку с датой на формат ячейки текстовый в исходной таблице, как все работает. Попробовала в макросе переменной str присвоить Date, тогда макрос работает.
Можно ли как то сделать так, чтобы в первом диалоговом окне можно было набирать и текст, и число и дату? Вариант Dim str As Variant не проходит для даты.

И еще вопрос? Когда мы выделяем ячейку, то она выделяется черной рамкой. Как можно сделать так, чтобы она выделялась не черным, а допустим красным цветом? В настройках я нигде не нашла этого.

Yuk, спасибо за Microsoft Date and Time Picker Control. То что нужно


Добавлено:
Рано обрадовался
До закрытия книги компонент работает нормально, вернее с ним ничего не происходит. После повторного открытия книги, на его месте сначала появляется красный крест (на всю ширину предыдущего размера), а потом меняет свою форму на квадрат
В чем дело?

sonix555
У меня то же самое. Нашел, что вроде надо установить сервис пак 2 для офиса. Сам не пробовал.
Также помогает вот такой код:
Код: Private Sub Workbook_Open()
Sheets("Sheet1").Activate
ActiveSheet.Shapes("DTPicker1").Select
Selection.ShapeRange.ScaleHeight 1.05, msoFalse, msoScaleFromTopLeft
Range("A1").Select
End Sub

У меня без сервис пака вооще не работает (только крест и все). Со вторым сервисом работает, но меняется форма.
Код помог спасибо

Всем привет!
Есть такая проблемка.
Есть готовая прога, но немного неправильная! В ней заместо того чтобы установить защиту только на ячейки, защита ставится и на ячейки и на кнопки. В чем может быть косяк не пойму! Может кто знает как сделать так чтобы защита ставилась только на ячейки, а кнопки работали!
Кто сможет помочь, прогу скину на ящик. Заранее благодарен!

Yuk

Цитата:

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

Если не жалко, то пришли пожайлуста код

Pantera3587
В код Книги:

Код: Public cursel As Range

Private Sub Workbook_Open()
Set cursel = Selection
cursel.BorderAround _
LineStyle:=xlContinuous, _
Weight:=xlThick, _
ColorIndex:=8
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
If cursel Is Nothing Then
Set cursel = Selection
End If
For b = 1 To 4
With cursel.Borders(b)
.Weight = xlThin
.ColorIndex = xlColorIndexAutomatic
.LineStyle = xlNone
End With
Next b
Target.BorderAround _
LineStyle:=xlContinuous, _
Weight:=xlThick, _
ColorIndex:=8
Set cursel = Target
Application.ScreenUpdating = True
End Sub

Переключение раскладки на русскую если в данный момент раскладка английская средствами WinAPI:

Код: Private Declare Function GetKeyboardLayoutName Lib "user32" _
Alias "GetKeyboardLayoutNameA" (ByVal pwszKLID As String) As Long

Private Declare Function ActivateKeyboardLayout Lib "user32" _
(ByVal HKL As Long, ByVal flags As Long) As Long


Sub ChangeKeyboardLayout()
Dim KeybLayoutName As String
KeybLayoutName = String(9, 0)
GetKeyboardLayoutName KeybLayoutName
'409 - английская,
'419 - русская
'Переключение раскладки на русскую если в данный момент раскладка английская
If Val(CStr(CLng(Left$(KeybLayoutName, InStr(1, KeybLayoutName, Chr(0)) - 1)))) = 409 Then
ActivateKeyboardLayout 0, 0
End If
End Sub

Troitsky
Спасибо большое за программу... но я вообще чайник в этом деле... Это надо куда написать? Открыть Редактор VBA, копировать туда... а потом что... Понимаю, что вопрос глупый... но времени нет самому разбираться... а надо до вторника поставить...

Antananarivu

Цитата:

Это надо куда написать? Открыть Редактор VBA

Добавить модуль, туда скопировать код.
А на событие Workbook_Open текущей книги (ЭтаКнига) повесить вызов процедуры смены раскладки:
Код: Private Sub Workbook_Open()
ChangeKeyboardLayout
End Sub

Да, спасибо огромное! Все таки странно что в Excel это не было реализовано стандартными средствами!

К сожалению только сегодня смог добраться до компьютера и попробовать реализовать написанную тобой процедуру. Я полный профан как в WinAPI так и в VBA, поэтому вопрос... Я сделал все как ты объяснил.. Excel попросил сохранить изменения, я сохранил как Книга1. Теперь, чтобы все работало, мне приходится каждый раз открывать эту Книгу1. Вопрос: а можно как нибудь сделать так, чтобы все работало, когда я кликаю стандартный ярлык Excel на рабочем столе (ну или в меню Пуск), а еще лучше, чтобы все работало для любого документа Excel - будь то созданный мной ранее документ или документ, созданный кем-то еще. Если лень объяснять, можно ссылку, попробую сам разобраться.

Antananarivu
Вставляй макрос в файл PERSONAL.XLS. Найдешь его в VBA редакторе. Этот файл запускается всегда в первую очередь и прячется в списке открытых файлов.

В данном случае переключение клавиатуры срабатывает не для конкретного файла, а для всего приложения. Поэтому вроде достаточно запустить макрос один раз. Если же нужно запускать макрос при открытии каждой книги, надо обрабатывать событие App_WorkbookOpen (прописать в том же personal.xls). Смотри здесь, как это делать.

наткнулся на фигню. неприятную.

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

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


Цитата:

Run-time error '1004':
Метод Select из класса Range завершен неверно

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


Цитата:

Sheets("calendar").Delete
Sheets("total").Select
Sheets("total").Copy Before:=Sheets(2)
Sheets("total (2)").Select
Sheets("total (2)").Name = "calendar"
ActiveSheet.Outline.ShowLevels RowLevels:=1
* Range("A8:AA122").Select
Range("AA122").Activate
Selection.Sort Key1:=Range("L8"), Order1:=xlAscending, Key2:=Range("C8") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1:AA130").Select
Range("AA130").Activate
ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$130"
Range("D8").Select

есть у кого-нибудь мысли?

korotetsky
Подозреваю, что проблема в этой строке:

Цитата:

ActiveSheet.Outline.ShowLevels RowLevels:=1

а) Попробуй перенести ее в конец.
б) Выдаст ли ошибку, если установить это свойство в 0 (показать все)?

Yuk
Сделал все как ты сказал: создал файл personal.xls в папке XLSTART. И в этот файл сохранил написанный выше программный код, запустил макрос. Теперь все в порядке: какой бы файл я не открывал - клавиатура переключается автоматически!
Но теперь новая напасть... когда я открываю новый файл (кликаю на ярлык Excel), ему автоматически присваивается новое имя Personal.xls (раньше присваивалось Книга1) и по умолчанию он пытается его сохранить как раз в папку XLSTART, поэтому приходится каждый раз вручную возвращаться в папку Мои документы и переименовывать в Книгу1. При этом в строке Рабочий каталог я оставил путь E:\Documents and Settings\User1\Мои документы, куда раньше сохранялись мои созданные документы - но это не помогло... Как то можно и от этого избавиться?
P.S. Еще, когда потом открываю этот документ (например, Книга1) автоматически открывается и Personal.xls. В общем, опять я явно что-то не то сделал.

Привет всем!
Есть куча книг, название каждой - название месяца.В ячейке А1 пишим месяц.
Нужна формула или макрос чтобы автоматом извлекалость название месяца из А1, минус 1 и вытаскивается С11 из книги "месяц-1" Лист 23 ячейка С12.
Простите что путано побпробую по другому.
А1=сентябрь, С11=D:\[август]Лист 23!С12

kramrus
Alt-F11, на своей книге Insert | Module (Вставить / Модуль)
там пишешь приблизительно такой текст:
Код: Function PrevMonth(c As Range)
PrevMonth = ""
If UCase(c.Value) = "СЕНТЯБРЬ" Then
PrevMonth = "D:\[Август]Лист23!$C$12"
End If
End Function

Люди... не дайте пропасть.. немного же осталось! Как мне разобраться с этим personal.xls!

Добавлено:
Ну слава Богу...сам разобрался... Тормозил.. Но и вы тоже хороши... ломались подсказать! Естественно никакого personal.xls не надо было создавать искуственно - он сам появляется, когда запишешь хотя бы один макрос! А я создавал его руками - отсюда видимо задваивание файла (ну я так для себя понял) и дикие глюки... Или вы уже настолько продвинутые программеры, что не можете себе представить пользователя, у которого нет ни одного написанного макроса?

kramrus

Цитата:

название месяца из А1, минус 1 и вытаскивается С11 из книги "месяц-1" Лист 23 ячейка С12.
Простите что путано побпробую по другому.
А1=сентябрь, С11=D:\[август]Лист 23!С12

в ячейку C11 должно попасть значение из 23 листа или можно обойтись ссылкой?

нашёл как это сделать, но только макросом, через функцию не получилось ;-(((

Код:
Sub Макрос1()
Range("C11").Clear
If UCase(Range("A2").Value) = "СЕНТЯБРЬ" Then
Range("C11").Formula = "='D:\[Август.xls]Лист23'!$C$12"
End If
If UCase(Range("A2").Value) = "ОКТЯБРЬ" Then
Range("C11").Formula = "='D:\[Сентябрь.xls]Лист23'!$C$12"
End If
End Sub

Провел в поиске пол-дня... Туплю, наверное.
Вобщем, заколебал меня этот Эксель со своими "мозгами".
Закачиваю текстовый файл с колонками текста, цифр, дат. Он формируется извне.
Даты в нем идут в формате dd.mm.yyyy , а т.к. системный разделитель даты у меня "/", то энти даты эксель воспринимает, как текст. Тупая замена с вызовом вручную "Поиск/Замена" точки на слэш приводит эксель в чувство и 01.10.2006 становится 1/10/2006, но (!). Применение метода Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False дает результат 10/01/2006, и функция МЕСЯЦ уже вместо октября возвращает ЯНВАРЬ, что не есть гут. Куда "рыть"?

Спасибо за макрос.
НО! Можно ли в макросе в пути ссылки указать вместо месяца ячейку в которой находится название месяца. Если А1=Январь, А2=декабрь, А3=ноябрь.
И еще еслинужно в ячейки С11....С32 вставить значение из нужной книги, ячейки С23...С44???????

Подскажите плиз !
как в Worksheet_Calculate()
получить адрес ячейки которая пересчиталась ?

to Kramus
Макрос и так берет ячейку... Не очень понятен первый вопрос.


По второму вопросу:
В макросе (вместо "Лист23" вставьте нужный)
' пробежимся по ячекам С11-С32 активного листа

Код: for i=11 to 32
Range("C"+cstr(i)).Formula = "='D:\[нужная книга.xls]Лист23'!$C$"+cstr(i+12)
next

kramrusМожно ли в макросе в пути ссылки указать вместо месяца ячейку в которой находится название месяца. Если А1=Январь, А2=декабрь, А3=ноябрь. [/q]
можно... только я не понял, что вы хотите... ;-))
можно так, выделяете любую ячейку, запускаете макрос, тот месяц, который в текущей ячейке, и случит для вычислений.

Цитата:

еслинужно в ячейки С11....С32 вставить значение из нужной книги, ячейки С23...С44???????

легко - через цикл. ;-)

Цитата:

Ilyansk

Дело в том что у меня в єтой ячейке автосумма
и мне надо проверять, если пересчиталась ячейка то делать проверку и
просить ввести коментарии.

по этому мне надо чёткую ячейку