Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » lomobodo
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Макрос для переноса значений на другой лист по условиям
 
#
01.09.2015 11:03:52
В принципе, я частично решил свою задачу (хотя и пришлось добавить технический столбец). Макрос умеет делать следующее: копирует значения F7:R7, F8:R8 и т. д. на лист, который указан в ячейке С1, при этом копирует в соответствующие строки, т. е. видит, что в V8 указано "TB671102", находит соответствующие значение на другом листе, и копирует рядом строчку (значения, не формулы).
Если кому-то понадобится, можете скачать файл-пример ниже.

Если кто-то сможет подсказать, как реализовать оставшиеся фишки из моего запроса (если сам раньше не найду), то будет очень круто.
1) Хотелось бы, чтобы макрос срабатывал автоматически, когда переключаешь название бренда в ячейке А1 (удалось запустить макрос автоматически, но проблема в том, что он копирует значения на новый бренд, а не на старый, с которого переключили).
2) после выполнения моего макроса, необходимо чтобы в столбцы, которые имеют заголовок FC (M5:Q5) вставлялись обратно формулы, которые там сейчас указаны (после того, как они были затерты другим пользователем)

В случае, если доработаю сам, выложу сюда :)
Пример
 
Перейти    
Макрос для переноса значений на другой лист по условиям
 
#
26.08.2015 10:06:54
Что есть: на листе, название которого указано в ячейке C1 листа "A&P", макрос ищет значение из ячейки А22 листа "A&P" по всей колонки L (в примере на листе "FC-July"). Как он его находит, вставляет в соседние ячейки значение строки F22:R22 с листа A&P. (Т.е.  F22:R22 с листа A&P скопировалось на лист FC-July в ячейки M19:Y19)

Что хочу получить: Макрос переберет все значения с диапазона А7:А74 листа "A&P", найдет такие же значения на листе "FC-July", и так же перенесет соседние строчки (т. е. значения F7:R7 с листа A&P скопируются в M16:Y16 листа "FC-July", т. к. L16 листа "FC-July" = A7 листа "A&P", значения F9:R9 с A&P скопируется  в М20:Y20 на "FC-July" и т. д.) Что самое главное: на листе A&P в ячейке А1 есть выпадающий список с названием бренда ("TB"; "TP"). Надо чтобы значение строчек копировалось в соответствии с этими брендами. На листе "FC-July" они указаны в колонке K.

Таким образом 2 вопроса:
1) как заставить макрос искать значение А1 с листа A&P в колонке К листа FC-July, значение А22 с листа A&P в колонке L листа FC-July? (например, в А1 указано TP, тогда при нажатии на макрос F22:R22 с листа A&P скопировалось на лист FC-July в ячейки M19:Y19, однако если в А1 указано TB, то при нажатии на макрос скопируется в M70:Y70).
2) решив первый вопрос нужно подумать, как реализовать поиск не одного значения А22, а всех значений с диапазона А7:А74.

В мало текста это не умещается, стараюсь описать со всеми максимальными деталями, чтобы потом не спрашивали, "что же вы это не указали изначально" :)

ПРИМЕР
 
Перейти    
Макрос для переноса значений на другой лист по условиям
 
#
25.08.2015 18:10:34
Уважаемые знатоки, выручайте пожалуйста :)

Т. к. не получил ответов, начал копаться сам дальше, и вот что получилось реализовать:
- Макрос умеет выбирать название листа, на который необходимо перенести прогноз, из ячейки C1
- Макрос ищет в столбце А1 листа A&P номер счета (пока получилось только с одним, ячейка А22, отмечена желтым), находит такой же счет на листе FC-July, и копирует туда значения.

Мб сможете подсказать/указать нужное направление дальнейший доработки макроса:
1) Как заставить искать по очереди все счета с листа A&P на листе 2 и копировать туда значение? И самое главное, чтобы он не только сопоставлял счета на двух листах, но и определял название бренда с ячейки А1? Нигде не могу найти примера, чтобы макрос искал по двум параметрам :(

2) После переноса значений с листа A&P на лист FC-July прописывал формулу в тех столбцах, где название FC, такую же, как стоит сейчас в примере.

На счет поиска всех счетов есть идея (прописать много разных Dim As Range), но мне кажется можно сделать это как-то оптимальнее + не пойму, как заставить одновременно помимо счета искать название бренда.

Сможет кто подсказать? P.S. файл с получившимся на данный момент макросом во вложении
https://www.dropbox.com/s/5llxl4gjoj8v8m9/FC%20Model%20Beta.xlsm?dl=0
 
Перейти    
Макрос для переноса значений на другой лист по условиям
 
#
18.08.2015 14:27:49
Добавил в файл-пример макрос, который я записал макрорекордером.

Принцип должен быть такой же, только макрос должен как-то находить на листе "FC-July" соответствующий бренд (т. е. на листе "A&P" в ячейке А1 указан бренд "TP", тогда макрос перенесет все значения на лист "FC-July" в соответствующий бренд, который указан в столбце "L".)

Так же условием выступает номер счета (либо название счета), который макрос находит на листе "A&P" по столбцу "A", а на листе FC-July эти же счета идут по столбцу "N".

Мне кажется проблема должна быть актуальной для многих - макрос должен сформировать базу данных на отдельном листе после того, как пользователь файла внес все и нажал сохранить. Однако поиск по темам с похожими названиями дает не то - большинство пользователей переносят / копируют данные на другой лист, сохраняя структуру, здесь же надо по принципу, схожим с SUMIFS, но диапазоны условий "разноразмерные".
+ надо добавить, чтобы после выполнения действий макроса возвращалась формула, которая была указана до того, как пользователь внес значения в ячейки (пользователи всегда вносят изменения в ячейки, которые идут под названием "FC", а формула в них ссылается на табличку правее).

Уважаемые гуру экселя, подскажите пожалуйста, что добавить, чтобы значения переносились по условиям.
https://www.dropbox.com/s/5llxl4gjoj8v8m9/FC%20Model%20Beta.xlsm?dl=0
 
Перейти    
Макрос для переноса значений на другой лист по условиям
 
#
17.08.2015 17:59:36
Здравствуйте уважаемые форумчане, подскажите пожалуйста, возможно ли реализовать следующее (пример файла во вложении):

Пользователи файла вносят изменения в строки с заголовками FC (вбивают значение руками). В файле-примере это колонки M:Q.
После того, как пользователь внес все, что хотел, он либо сохраняет (закрывает) файл, либо перещелкивает на другой бренд (ячейка A1, в примере можно щелкать с "TP" на "TB").
Макрос делает следующее: переносит значения с каждой строки (строка J7:Q7, J8:Q8 и т. д. до J74:Q74, кроме строк пустых и содержащих формулу SUM) на лист "FC-July" в колонку "U" (название листа в ячейке C1) в соответствующие поля. К примеру, значение из O15 макрос перенесет на лист "FC-July" в ячейку U34, т. к. посмотрит, что в ячейке A15 код "6714202", в ячейке O2 указан период 2015010, и на листе FC-July (макрос найдет названия листа в ячейке C1) есть соответствующие коды и периоды (колонки M,N). Макрос так же увидит, что в А1 указан бренд TP, который так же имеется на листе FC-July в колонке "L".

После того, как макрос перенес все значения с листа A&P на лист FC-July, он возвращает ту же формулу, что и была в ячейке ранее, до того, как пользователь вбил туда свои значения.
Собственно все. Действие макроса происходит: а) при сохранении файла б) при изменение бренда в ячейке A1 из выпадающего списка. Перед тем, как сработать, макрос спросит "Сохранить текущий прогноз?" (данный выскакивающий вопрос могу добавить самостоятельно).
Название листа, на который переносить значение, макрос берет из ячейки C1.

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

Заранее спасибо!

P. S. файл-пример не удалось урезать меньше 300 кб, закачал на дропбокс https://www.dropbox.com/s/79thifjrywce5ve/FC%20Model%20Beta.xlsx?dl=0
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
30.06.2014 15:06:55
Hugo,
Точно, спасибо большое, это вообще лишние куски кода тут.
Остался только открытый вопрос с гиперссылкой...
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
30.06.2014 11:58:10
Странно, смайлики в ручную не удалял, просто вставил как код. Попытка №2
Код
Sub SendMailTest()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim cell As Range
   
    lRow = Sheets("2").Cells(Rows.Count, 5).End(xlUp).Row    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        strbody = "<font face=""Modern h medium"" size=""2"" color=""black"">" & "English see below!<br>" & _
        "       <br>" & _
        "ТЕКСТ ПИСЬМА<br>" & _
       "       <br>" & _
              "<font face=""Modern h medium"" size=""2"" color=""red"">" & "ЦИФРА КОТОРАЯ ПОДТЯГИВАЕТСЯ С ЯЧЕЙКИ I145: " & Format(Range("I145").Value, "# ### ###") & " руб. <br>" & _
             "ЦИФРА, КОТОРАЯ ПОДТЯГИВАЕТСЯ С ЛИСТА 2: " & Format(Sheets("2").Range("C" & lRow).Value, "# ### ###") & " ðóá. <br>" & _
             "ЦИФРА, КОТОРАЯ ПОДТЯГИВАЕТСЯ С ЛИСТА 2: " & Format(Sheets("2").Range("E" & lRow).Value, "# ### ###") & " %. <br>" & _
             "       <br>" & _
           "<font face=""Modern h medium"" size=""2"" color=""black"">" & "ТЕКУЩАЯ ДАТА " & Format(Now, "DD.MM.YYYY") & " ïî: " & "<br>" & _
"ТЕКСТ ПИСЬМА<br>" & _
"ТЕКСТ ПИСЬМА<br>" & _
"ТЕКСТ ПИСЬМА<br>" & _
"       <br>" & _
"ТЕКСТ ПИСЬМА И СНОВА ЦИФРА С ЯЧЕЙКИ I145: " & "<font face=""Modern h medium"" size=""2"" color=""red"">" & Format(Range("H145").Value, "# ### ###") & " руб. <br>" & _
"       <br>" & _
"<font face=""Modern h medium"" size=""2"" color=""black"">" & "ТЕКСТ ПИСЬМА<br>" & _
"       <br>" & _
"ТЕКСТ ПИСЬМА<br>" & _
"СЮДА БЫ ВСТАВИТЬ ГИПЕРССЫЛКУ<br>" & _
              "С УВАЖЕНИЕМ, <br>" & _
              "       <br>" & _
                   "ТЕКСТ ПИСЬМА<br>" & _
               "ТЕКСТ ПИСЬМА"
        On Error Resume Next
    With OutMail
     .Display
        .To = Range("E2").Value
        .Subject = Range("F2").Value
       .HTMLBody = strbody & .HTMLBody
        .Attachments.Add Range("H2").Value
        .Attachments.Add Range("I2").Value
        .Attachments.Add Range("J2").Value
        .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub
Подскажите еще, пожалуйста, как можно обойти ограничение "too many line continuations", которое появляется (как подсказывает гугл) после добавления & _ более 24 раз. Т. е. задача составить длинное письмо, в котором необходимы переходы со строки на строку.
И есть ли возможность вставить в текст письма гиперссылку (которая без макроса вставляется с помощью "Вставка >>>> Гиперссылка". Пробовал посмотреть необходимый код с помощью записи макроса, но не получается вставить его в тело письма:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
       "C:\Programm Files\1.xlsx", TextToDisplay:= _
       "C:\Programm Files\1.xlsx"
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
30.06.2014 09:31:08
Sanja,
Спасибо, все работает!
На всякий случай вставляю код целиком, если кому-то понадобится решать такую же задачу - отправлять письма из Эксель, беря различные значения с разных листов.
Макрос формирует письмо, вставляет в письмо значение ячейки I145 с листа, на котором запущен + последнее значение по столбцу Е с листа с названием "2" + вставляет текущую дату.
Для тех, кто делает регулярную рассылку писем на основании каких-либо отчетов будет полезен.
Спасибо знатокам с форума
Код
Sub SendMailTest()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim cell As Range
    lRow = Sheets("2".Cells(Rows.Count, 5).End(xlUp).Row
    
    
    Set OutApp = CreateObject("Outlook.Application"
    Set OutMail = OutApp.CreateItem(0)
        strbody = "<font face=""Modern h medium"" size=""2"" color=""black"">" & "Уважаемые коллеги<br>" & _
              "<B>Первая цифра: </B>" & Format(Range("I145".Value, "# ### ###" & " руб. <br>" & _
             "<B>Вторая цифра: </B>" & Format(Sheets("2".Range("E" & lRow).Value, "# ### ###" & " руб. <br>" & _
           "Отчет на: " & Format(Now, "DD.MM.YYYY" & "<br>" & _
              "- Инструкцию найдете тут <br>" & _
              "<U><B></B></U><br>" & _
               "С уважением,"
        On Error Resume Next
    With OutMail
     .Display
        .To = Range("E2".Value
        .Subject = Range("F2".Value
       .HTMLBody = strbody & .HTMLBody
        .Attachments.Add Range("H2".Value
        .Attachments.Add Range("I2".Value
        .Attachments.Add Range("J2".Value
        .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End S
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
27.06.2014 15:59:42
Юрий М, можно и без цитирования, больше не буду злоупотреблять :)

Sanja, спасибо! а можно еще допилить, чтобы он не с текущего листа по столбцу искал последнее значение, а с листа 2 (скрытый в оригинальном файле, но подозреваю, что для Visual Basic главное название листа, а не то, что он скрыт)
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
27.06.2014 14:37:39
Вот
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
27.06.2014 14:29:36
Цитата
Sanja пишет: не проверял
Проверил, выдает 400 ошибку Visial Basic
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
27.06.2014 13:49:17
Цитата
Игорь пишет:
Игорь, благодарю!
По первым двум вопросам все получилось (понял, что строка переносилась из-за лишнего <br> ;)
Насчет третьего вопроса: как заставить Excel брать самое нижнее значение из столбца E со второго скрытого листа?
Т. е. должно быть что-то такое:
"Вторая цифра: " & Format(САМОЕ НИЖНЕЕ ЗНАЧЕНИЕ ПО СТОЛБЦУ Е С ЛИСТА 2).Value, "# ### ###" ;)  & " ðóá. <br>" & _
 
Перейти    
Формат числа в макросе для отправки почты из Excel, Положение подтягивающегося числа и его формат
 
#
27.06.2014 13:21:48
Здравствуйте, уважаемые знатоки Excel.
Просьба помочь доделать макрос, который отправляет почту с помощью Outlook из файла Excel во вложении.
Собственно сам макрос:


Код
Sub SendMailTest()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim cell As Range
    
    
    Set OutApp = CreateObject("Outlook.Application"
    Set OutMail = OutApp.CreateItem(0)
        strbody = "<font face=""Modern h medium"" size=""2"" color=""black"">" & "Уважаемые коллеги!<br>" & _
              "<B>Первая цифра:</B><br>" & Range("I145".Value & _
           "Отчет на: Здесь надо поставить текущую дату<br>" & _
              "- Инструкция находится здесь <br>" & _
              "<U><B></B></U><br>" & _
               "С уважением,"
        On Error Resume Next
    With OutMail
     .Display
        .To = Range("E2".Value
        .Subject = Range("F2".Value
       .HTMLBody = strbody & .HTMLBody
        .Attachments.Add Range("H2".Value
        .Attachments.Add Range("I2".Value
        .Attachments.Add Range("J2".Value
        .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub
Как хотелось бы чтобы выглядело, и как выглядит на самом деле - прикладываю скриншоты.
Как выглядит сейчас:

Как хотелось бы:

Вопросы следующие:
1) как сделать желаемое форматирование и чтобы цифра отображалась на нужной строке в нужном положении, а не сливалась со следующей строкой?
2) Что прописать, чтобы цеплял текущую дату?
3) Если возможно, нужна еще вторая строчка в тексте письма, на которой будет написано следующее: "Вторая цифра: *тянет значение со скрытого листа 2,
ячейка Е126*", Ситуация заключается в том, что на листе 2 периодически разное кол-во строк, и нужна крайняя нижняя по столбцу Е.
 
Перейти    
Макрос с поиском решения внутри (зациклить), Не работает код
 
#
16.06.2014 08:53:52
Pelena, спасибо огромное - макрос заработал как надо!   :)
Изменено: lomobodo - 16.06.2014 08:54:03
 
Перейти    
Макрос с поиском решения внутри (зациклить), Не работает код
 
#
12.06.2014 22:55:42
Цитата
Pelena пишет: Вы уверены, что бинарное? Это только 0 или 1
Уверен, так и надо  :)  
Спасибо, посмотреть получится только в понедельник - домашний Мак с экселем ошибку какой-то библиотеки при запуске макроса выдает.
Изменено: lomobodo - 14.06.2014 14:40:48
 
Перейти    
Макрос с поиском решения внутри (зациклить), Не работает код
 
#
11.06.2014 18:32:53
Конечно, прикрепил файл
 
Перейти    
Макрос с поиском решения внутри (зациклить), Не работает код
 
#
11.06.2014 18:24:10
Здравствуйте, уважаемые гуру Эксель. Подобные темы уже были на форуме (и я с ними ознакомился). Тем не менее, макрос по каким-то причинам не срабатывает. Привожу его здесь:
Код
Sub Solver()
  
Dim i&
For i = 4 To 10           
           SolverOk SetCell:="$O$" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$F$,$J$,$N$" & i _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$O$" & i, Relation:=1, FormulaText:="$P$" & i
    SolverAdd CellRef:="$F$" & i, Relation:=5, FormulaText:="бинарное"
    SolverAdd CellRef:="$J$" & i, Relation:=5, FormulaText:="бинарное"
    SolverAdd CellRef:="$N$" & i, Relation:=5, FormulaText:="бинарное"
    SolverOk SetCell:="$O$" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$F$,$J$,$N$" & i _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$O$" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$F$,$J$,$N$" & i _
        , Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve True
    Next i
End Sub
Что ожидалось от макроса: Поиск решения по заданным параметрам будет срабатывать в строках 4 - 10.
Что происходит после запуска: Эксель что-то просчитывает, но значение ячеек не меняется.
Надеюсь на вашу помощь, потому что уже и не знаю, в чем проблема.
Версия Excel: 2010
 
Перейти    
Страницы: 1
Наверх