Замена части кода с макрорекодера для автозаполнения формул

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Замена части кода с макрорекодера для автозаполнения формул (Формулы/Formulas)

Артем_П

Дата: Понедельник, 16.04.2018, 09:16 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Всем здравствуйте!
Помогите пожалуйста подправить код в макросе (записывал в макрорекодере).
Суть макроса: в столбце Е есть данные по продолжительности звонка, которые имеют вид "N мин", "N мин, N сек", "N сек" и "пустые". Формулой в столбце М я проверяю содержит ли ячейка в столбце Е слово "мин", если содержит то она вставляется без изменения, если нет, то сцепляю ячейку вставляя "0 мин, ", чтобы получить формат там где нет значения минут "0 мин, N сек". Затем двойным нажатием по черному крестику протягиваю формулу до конца таблицы. Удаляю формулы в столбце М. После этого разделяю значение в столбце М с помощью "Текст по столбцам" через пробел, так чтобы цифры минут и секунд были в отдельных столбцах ( М и О соответственно). в столбце Q через формулу =ВРЕМЯ(0;М;О) преобразую данные во время, меняю формат на 37:30:55, и двойным щелчком по черному крестику протягиваю до конца таблицы. После удаляю формулы из столбца Q, и вырезаю столбцы до столбца Q, так чтобы эти значения оказались в столбце М. В ячейку М1 вставляю название столбца таблицы "Трафик".
Макрорекодер записал так что формулы протягиваются до 250 строки, руками подправил, так чтобы автозаполнение было до 5000 строки, но мне это не подходит, так как строк может быть от 100 и до 500к, мне нужно чтобы макрос протягивал формулу до конца таблицы. Понимаю что нужно что-то поменять в строках кода 11-12 и 29-30, но на что менять так и не понял, так как не в зуб ногой в ВБА. Просмотрел ответы на форуме, но так и не разобрался. Поэтому прошу людей знающих помочь подправить код.
Заранее огромное спасибо!!!
Пример в приложении.
P/S: Эксель 2016

К сообщению приложен файл: 7725507.xlsm (54.2 Kb)

Сообщение отредактировал Артем_П - Понедельник, 16.04.2018, 09:22

Ответить

СообщениеВсем здравствуйте!
Помогите пожалуйста подправить код в макросе (записывал в макрорекодере).
Суть макроса: в столбце Е есть данные по продолжительности звонка, которые имеют вид "N мин", "N мин, N сек", "N сек" и "пустые". Формулой в столбце М я проверяю содержит ли ячейка в столбце Е слово "мин", если содержит то она вставляется без изменения, если нет, то сцепляю ячейку вставляя "0 мин, ", чтобы получить формат там где нет значения минут "0 мин, N сек". Затем двойным нажатием по черному крестику протягиваю формулу до конца таблицы. Удаляю формулы в столбце М. После этого разделяю значение в столбце М с помощью "Текст по столбцам" через пробел, так чтобы цифры минут и секунд были в отдельных столбцах ( М и О соответственно). в столбце Q через формулу =ВРЕМЯ(0;М;О) преобразую данные во время, меняю формат на 37:30:55, и двойным щелчком по черному крестику протягиваю до конца таблицы. После удаляю формулы из столбца Q, и вырезаю столбцы до столбца Q, так чтобы эти значения оказались в столбце М. В ячейку М1 вставляю название столбца таблицы "Трафик".
Макрорекодер записал так что формулы протягиваются до 250 строки, руками подправил, так чтобы автозаполнение было до 5000 строки, но мне это не подходит, так как строк может быть от 100 и до 500к, мне нужно чтобы макрос протягивал формулу до конца таблицы. Понимаю что нужно что-то поменять в строках кода 11-12 и 29-30, но на что менять так и не понял, так как не в зуб ногой в ВБА. Просмотрел ответы на форуме, но так и не разобрался. Поэтому прошу людей знающих помочь подправить код.
Заранее огромное спасибо!!!
Пример в приложении.
P/S: Эксель 2016

Автор - Артем_П
Дата добавления - 16.04.2018 в 09:16

Nic70y

Дата: Понедельник, 16.04.2018, 09:23 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8754

Репутация: 2270 ±

Замечаний: 0% ±

Excel 2010

~~файла нет~~
а уже есть, но написал без файла
что-то много манипуляций....
[vba]

Код

u = Cells(Rows.Count, 1).End(xlUp).Row
Range("b1:b" & u).FormulaR1C1 = "=RC[-1]+18"

[/vba]
думаю все можно упростить

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Понедельник, 16.04.2018, 09:24

Ответить

Артем_П

Дата: Понедельник, 16.04.2018, 09:29 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата Nic70y, 16.04.2018 в 09:23, в сообщении № 2 ()

думаю все можно упростить

Я бы конечно с радостью все упростил, Но не умею в ВБА.
Ваш код мне необходимо вставить в строчки 11, 12 и 29,30 только заменить имена столбцов на свои, формулу прописать свою, я правильно понял?

Ответить

Karataev

Дата: Понедельник, 16.04.2018, 09:35 | Сообщение № 4

Группа: Проверенные

Ранг: Старожил

Сообщений: 1334

Репутация: 533 ±

Замечаний: 0% ±

Excel

Артем_П, в Excel'e есть такое понятие "поиск последней строки". Вам нужно в макросы добавлять код по поиску последней строки.

[vba]

Код

Sub Преобразовать_время()
'
' Преобразовать_время Макрос
'

'
    Dim lr As Long

    'поиск последней строки по столбцу "A"
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(SEARCH(""мин"",RC[-8])),RC[-8],CONCATENATE(""0 мин, "",RC[-8]))"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M" & lr)
    Range("M2:M" & lr).Select
    Columns("M:M").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
        True
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=TIME(0,RC[-4],RC[-2])"
    Range("Q2").Select
    Selection.NumberFormat = "[h]:mm:ss;@"
    Selection.AutoFill Destination:=Range("Q2:Q" & lr)
    Range("Q2:Q" & lr).Select
    Columns("Q:Q").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("M:P").Select
    Range("P1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("M1").Select
        With Range("M1")
    .Value = "Трафик"
    End With
End Sub

[/vba]

[vba]

Код

[/vba]

Karataev

Ответить

СообщениеАртем_П, в Excel'e есть такое понятие "поиск последней строки". Вам нужно в макросы добавлять код по поиску последней строки.

[vba]

Код

[/vba]

Автор - Karataev
Дата добавления - 16.04.2018 в 09:35

Артем_П

Дата: Понедельник, 16.04.2018, 09:42 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Karataev, Nic70y, спасибо вам ОГРОМНОЕ за помощь.
Все работает!
Буду сидеть рассматривать код и понимать как вы это сделали. Теперь свободного времени станет немного больше и появиться возможность поучить ВБА.
Еще раз огромное спасибо за помощь!

Ответить

Артем_П

Дата: Понедельник, 16.04.2018, 11:00 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

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

Сообщение отредактировал Артем_П - Понедельник, 16.04.2018, 11:01

Ответить

Nic70y

Дата: Понедельник, 16.04.2018, 12:50 | Сообщение № 7

Группа: Друзья

Ранг: Экселист

Сообщений: 8754

Репутация: 2270 ±

Замечаний: 0% ±

Excel 2010

[vba]

Код

Sub U__749()
    u = Cells(Rows.Count, 1).End(xlUp).Row
    Range("m2:m" & u).FormulaR1C1 = _
        "=(IF(ISERR(SEARCH(""мин"",RC[-8])),""0:"","""")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-8],""мин"","":""),""сек"",),"","",))/60"
    Range("m2:m" & u) = Range("m2:m" & u).Value
    Range("m2:m" & u).NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Range("m1") = "Трафик"
End Sub

[/vba]
вдруг правильно

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Понедельник, 16.04.2018, 12:53

Ответить

Страница 1 из 1
1