» Excel VBA (часть 2)

Oyger
Хм...конечно путаю..TextBox у меня...и кажется чего бы проще...записал в него даты ДД.ММ.ГГ согласно ControlSource=xxx!A1 получил ее в ячейке...и напротив поместил в ячейку дату...зачитал вее в текстбокс...увы не все так просто. Вмешиваются региональные настройки. Просто я не програмист..но прожку написал отличная и работает на 5...но только на моем компе)))...

Oyger
а можешь написать все 15 параметров (кроме выше названных) для паролирования/снятия пароля (форматирование строк, ячеек, стобцов и т.д.)


уже не нужно, по поиску Excel -> Worksheet.Protect Method (HV10063121)

Подскажите пожалуйста простой вопрос:

Как переменной iCount присвоить значение суммы например D5:D10 ?

Здравствуйте, помогите с следующей задачей.
Имеются 4 листа в книге. На 5 листе, в ячейке А1 стоит формула, суммирующая ячейки А1 с других листов.
Т.е. Лист4!А1=Лист1!A1+Лист2!A1+Лист3!A1

Вот что у меня получилось:


Код: Sub formula()

Dim i As Integer
'Sheets("Swod").Range("A1") = "="
For i = 1 To Sheets.Count - 1

If i = Sheets.Count - 1 Then
Sheets("Swod").Range("A1") = Sheets("Swod").Range("A1") + _
Sheets.Item(i).Name + "!A1"
Else
Sheets("Swod").Range("A1") = Sheets("Swod").Range("A1") + _
Sheets.Item(i).Name + "!A1+"
End If
Next i
End Sub

zqweq
Чтобы записать формулу в ячейку напиши:
Sheets("Swod").Range("A1").FormulaLocal = "ХХХ"
Вместо ХХХ вставь саму форму. Т.е. можешь зайти в ячейку в самом экселе, набрать там нужную формулу (ну как при обычной работе делаешь), скопировать ее как есть (начиная с "=") и вставить в макрос.
А когда обращаешься к значению в ячейке, дописывай Value. В твоем случае (когда ты формулу в ячейке заменяешь на новую с дополнением) это будет выглядеть так:
Sheets("Swod").Range("A1")FormulaLocal = Sheets("Swod").Range("A1").Value + Sheets.Item(i).Name + "!A1"
А еще лучше (позволю себе немного натации) сначала текст формулы забивай весь в переменную текстовую, а потом ее запиши в ячейку как формулу. Пример:
Dim Text as string
Text = "="
Text = Text & Sheets(XXX).Name & "!A1+"
....
Text = Text & Sheets(XXX).Name "!A1"
Sheets("Swod").Range("A1")FormulaLocal = Text
Text = Empty
Вместо ХХХ - номер листа по порядку

Oyger
Не совсем понял, Вместо ХХХ - номер листа по порядку - в цикл что-ли или просто перечислить. Просто там листы будут каждый раз по разному называться, поэтому тупо Лист1 Лист 2 Лист 3 подставить не удастся, там более глобальная задача.
Спасибо за "натации", думаю они мне пригодятся)), работать ещё с ними долго.


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

zqweq
Я написал

Цитата:

Вместо ХХХ - номер листа по порядку

А не имя листа. А это и означает "Номер листа по порядку" - как они у тебя в книге идут.
Да - если у тебя листов куча, а надо формулу по все - загани в цикл.

Добавлено:
zqweq

Цитата:

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

Не знаю. Не должен. Запусти макрос в построчной отработке (встань в тело макроса и жми F8). Посмотри на чем он глючит.

Oyger
Спасибо, разобрался с этим. Висло из-за того что не было sp 2 for MS Office 2003.

Такой ещё вопрос:
Необходимо скопировть листы из разных книг в другую книгу. Все книги лежат в разных местах.


Код: Workbooks("c:\work\macros.xls").Worksheets("swod").Copy After:=Workbooks("d:\1\paste.xls").Worksheets("Лист1")

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

zqweq
Открыл книгу, куда вставляешь.
Открыл книгу из которой вставляешь.
Скопировал.
Закрыл книгу из которой вставлял.
Открыл другую книгу.
Вставил. Закрыл.
И т.д.
Эксель не работает с закрытыми книгами.
Solenaja
Макрос есть для любой задачи. А вот сможешь ли ты написать этот макрос?..

иЗВИНИТЕ,
только учусь делать хорошие макросы.
Помоги мне, сделать
1.опередедить цвет в ехесле,
2. сделать проверку при например желтом цвете необходимо в следующем стобце проставлять цифру например 5
Спасибо за помощь

Range("A1").Interior.ColorIndex
желтый - это 6

Oyger
я имею ввиду уже готовый

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

Solenaja

Цитата:

я имею ввиду уже готовый

ну Вы же поймите, что готовый Вам не подойдёт! Везде есть свои нюансы. Названия листов, книг, где расположены, куда и как копировать... И вообще, я не понял, что означает - "лист + листы(ы)" ?... и что означает " и чтобы не было связей с основным листом, т.е. ссылки в ячейках в формулах были прямые" ?...
как минмимум подготовьте пример с Вашими файлами (буквально несколько листов, на них несколько заполненных нужных данных - можно абракадабру, обязательно листик с тем, как оно должно получится в результате. всё это запакуйте и выложите на любой бесплатный хостинг (zalil.ru mytempdir.ru, ifolder.ru rapidshare.com) сюда в форум ссылку на скачивание данного архивчика с примером. Тогда можно будет предметно поговорить!

USERJT
прежде всего зависит от того, как был получен цвет - через задание цвета заливки или через условное форматирование (это, как говорят в Одессе - "две большие разницы!")

Цитата:

1.опередедить цвет в ехесле,

Возможно. Пользовательской функцией.

Код:
Function НомерЦвета(Ячейка As Range)
НомерЦвета = Ячейка.Interior.ColorIndex
End Function

все это можно не для русского ехесля, а для версии на английском языке.

USERJT
1) в шапке ссылочка на "Список соответствия имен функций в английской и русской версиях Excel"
2) вместо: =ЕСЛИ(НомерЦвета(A1)=6;5;"")
можно написать =IF(НомерЦвета(A1)=6;5;"")
если Вам не нравятся Русские имена пользовательских функций - так измените на что угодно:
Function Get_Color_of_Cell(MyCell As Range)
Get_Color_of_Cell = MyCell.Interior.ColorIndex
End Function
тогда будет: =IF(Get_Color_of_Cell(A1)=6;5;"")

Большое спасибо за помощь
Сейчас проверю и напишу о результах.

Solenaja
Может я не правильно понял вопрос, но у меня есть такой макрос:

Код:
Dim index As Integer
Dim shCount As Integer
Dim arg1, arg2, arg3 As String
Dim i As Long, x As Variant


shCount = ActiveWorkbook.Sheets.count

For index = 4 To shCount

arg2 = Sheets(index).Name ' file name

With Sheets(index)
.Unprotect
.Copy
End With

x = ActiveWorkbook.LinkSources
If Not IsEmpty(x) Then
For i = 1 To UBound(x)
ActiveWorkbook.BreakLink Name:=x(i), Type:=xlExcelLinks
Next i
End If

ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True

ChDir "C:\Documents and Settings\..."
ActiveWorkbook.SaveAs (arg2)

MaximuS G и All
вот что нужно сделать
исходные данные
http://www.sendspace.com/file/kihx51

Solenaja

Как расположены эти 20 листов... Можно ли их отсортировать - например первые 20 зеленые, и дальше сод, скид, валют, примеч, адрес...






Добавлено:
Положения листов постоянные, не меняються ?

MaximuS G
листы идут строго по порядку в зависимости от названия группы, но не алфавитном порядке
в примере два листа 18 и 20 - соотвественно на 20 и 22 месте от начала (первые два это содержание и скидки) - положение их не меняется, во всяком случае пока, тк всего 20 групп товаров и 20 листов соответственно
название листов и их кол-во может измениться в будущем - ввиду того что может добавиться, к примеру, ещё одна группа товаров или понадобится ещё один вспомагательный лист для расчетов или наоборот две группы товаров объединяться в одну

Solenaja

Dim arg As String
Dim i As Integer

For i = 1 To Sheets.Count
arg = Sheets(i).Name
If ActiveWorkbook.Sheets(i).Tab.ColorIndex = 14 Then нахожу зеленые листы
Sheets(Array("СОДЕРЖАНИЕ", "Скидки", arg, "Валюта", "Примечание", "Адрес")).Copy

'копирую зеленый лист вместе с другими. Теперь что я обнаружил, что когда копируешь
листы в которых есть ссылки между собой, то ссылка на книгу из которой копируется не добавляеться, может у Вас что-то другое? ))... Вообще-то вот этот код я использую для редактирования формул...
Но тут он просто бессмыслен...
''
Sheets(arg).Select
Range("A1:Z1000").Select
Selection.Replace What:="[All_temp.xls]", Replacement:="", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
''
ChDir "C:\Documents and Settings\..." ' сохраняю и закрываю книгу
ActiveWorkbook.SaveAs (arg)
ActiveWorkbook.Close
Windows("All_temp.xls").Activate ' перехожу к поиску следующего зеленого листа
End If
Next

Добрый день господа. Суть такова: есть Ячейки A1 B1 C1. В ячейке A1 забита формула допустим =СУММ(100/$C$27*$D$27). Нужно, чтобы при записи в С1 слова "Сентябрь", в ячейку B1 копировалось И значение И формула И условное форматирование из A1. То есть все, что бы скопировалось при обычном копировании A1 в другую ячейку. Это нужно для того, чтобы при последующем копировании B1 не ссылалась на С1, поскольку Значение в С1 у меня будет меняться.
Подскажите, будьте любезные, как сделать макрос, а то даже не знаю, в какую сторону копать...

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

Подскажите, пожалуйста, как записасть значение в массив:
For i = 1 to 10
cells(5, i) = y(i) ...
Тоесть будет 10 значений y: y1 y2 y3y4 y5 ...
Мне нужен массив Arr(y1...y10)

Pravoved90


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

Sub Макрос1()

'Выделение ячейки A1
Range("A1").Select

'Копировать
Selection.Copy

'Выделение ячейки B1
Range("B1").Select

'Пункт меню ПРАВКА-->Специальная вставка-->Все-->ОК
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False

End Sub

А там, уже в зависимости от того вводите вы данные через форму или сразу на лист, ставите в соответствующее место вызов макроса.

Цитата:

jocer

Цитата:
как избавиться от появления этого сообщения?

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

Допустимо. А по-подробнее?

Спасибо за ответ, только опять небольшая загвоздка. Я создал макрос A1B1C1 копируется в A2B2C2. Как мне написать, что этот макрос выполняется если в ячейке D1 написано слово, допустим, "счастье". Если можно, покажите на примитивном примере, а то я с макросами весьма официально и на "Вы".))

Pravoved90

if Range("D1").Value="cчастье" then
имя_макроса
End if

Добавлено:
MaximuS G

Private Sub CommandButton1_Click()
Dim Mas_Array(1 To 10) As Integer
Dim i As Integer

'Запись в массив
For i = 1 To 10
Mas_Array(i) = Cells(i, 1).Value
Next i

'Вывод данных массива
For i = 1 To 10
Cells(i, 3).Value = Mas_Array(i)
Next i
End Sub


А также читайте то, что рекомендовано к прочтению ShIvADeSt.