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

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

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

farafonov_sv

Дата: Пятница, 25.11.2011, 11:26 | Сообщение № 1

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

Ранг: Новичок

Сообщений: 13

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

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

Добрый день!
Помогите пож. с дополнением по макросу.
Написал вот такой код:
Вопрос вот в чем написал макрос при котором:
из С1 вычитается 5% и пишется в D1, потом из D1 вычитается 45% и пишется в E1 из С1 + 50% и в F1. Кроме того приводит все данные в цифровой формат и растягивает на 6 строк. Все завязано на кнопку.
Но так как у меня таблица динамическая мне необходимо прописать так что бы формула протаскивались на всю таблицу независимо от кол-ва строк.
Пробовал сделать через СЧЁТЗ, но он пишет ошибку:(

Code

Private Sub CommandButton1_Click()
Selection.NumberFormat = "#,##0.00"
ActiveCell.FormulaR1C1 = "=RC[-1]*(1-5%)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A6")
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.NumberFormat = "#,##0.00"
ActiveCell.FormulaR1C1 = "=RC[-1]*(1-45%)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A6")
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.NumberFormat = "#,##0.00"
ActiveCell.FormulaR1C1 = "=RC[-3]/(1-50%)"
ActiveCell.Offset(0, 0).Range("A1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A6")
Selection.NumberFormat = "#,##0.00"

End Sub

Понимаю что жутко избыточен, делал на основе нескольких макросов записанных в режиме камеры.

Большое спасибо за помощь!
В прикрепленном файле пример с цифрами.

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

Ответить

Jhonson

Дата: Пятница, 25.11.2011, 11:49 | Сообщение № 2

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

Ранг: Ветеран

Сообщений: 514

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

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

Не совсем понятно что нужно, попробуйте такой код:

(дописал несколько строчек сверху Вашего кода, и поменял ActiveCell.Range("A1:A6") на ActiveCell.Range(a),
код не проверял, но должно работать)

Code

Private Sub CommandButton1_Click()

      i = 1
      Do While Cells(i, 1) <> ""
          i = i + 1
      Loop
      a = "a1:a" & i - 1

   Selection.NumberFormat = "#,##0.00"
   ActiveCell.FormulaR1C1 = "=RC[-1]*(1-5%)"
   Selection.AutoFill Destination:=ActiveCell.Range(a)
   ActiveCell.Offset(0, 1).Range("A1").Select
   Selection.NumberFormat = "#,##0.00"
   ActiveCell.FormulaR1C1 = "=RC[-1]*(1-45%)"
   Selection.AutoFill Destination:=ActiveCell.Range(a)
   ActiveCell.Offset(0, 1).Range("A1").Select
   Selection.NumberFormat = "#,##0.00"
   ActiveCell.FormulaR1C1 = "=RC[-3]/(1-50%)"
   ActiveCell.Offset(0, 0).Range("A1").Select
   Selection.AutoFill Destination:=ActiveCell.Range(a)
   Selection.NumberFormat = "#,##0.00"
   End Sub

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

Сообщение отредактировал Jhonson - Пятница, 25.11.2011, 12:25

Ответить

farafonov_sv

Дата: Пятница, 25.11.2011, 12:15 | Сообщение № 3

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

Ранг: Новичок

Сообщений: 13

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

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

Спасибо!
Поставил ваш код в строке:
Selection.AutoFill Destination:=ActiveCell.Range("a") выдает ошибку

Мне надо что бы формулы рассчитывались по всему списку, не зависимо от того сколько строк в С1 (так как таблицы каждый раз разные может быть 2 строки а может за пару сотен)

Ответить

Jhonson

Дата: Пятница, 25.11.2011, 12:22 | Сообщение № 4

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

Ранг: Ветеран

Сообщений: 514

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

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

Selection.AutoFill Destination:=ActiveCell.Range(a)
нужно без кавычек

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

Ответить

_Boroda_

Дата: Пятница, 25.11.2011, 12:24 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16714

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

А так:

Code

Private Sub CommandButton1_Click()
r_ = Range("C" & Rows.Count).End(xlUp).Row
Range("D1:D" & r_).FormulaR1C1 = "=RC[-1]*(1-5%)"
Range("E1:E" & r_).FormulaR1C1 = "=RC[-1]*(1-45%)"
Range("F1:F" & r_).FormulaR1C1 = "=RC[-3]/(1-50%)"
Range("D1:F" & r_).NumberFormat = "#,##0.00"
End Sub

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

farafonov_sv

Дата: Пятница, 25.11.2011, 12:42 | Сообщение № 6

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

Ранг: Новичок

Сообщений: 13

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

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

Спасибо огромное!!!!
Все заработало!!! и всего 4 строки а я с ним долбаюсь уже почти неделю)
Пойду дальше крутить таблицу)
А какую-нибудь книжку не посоветуете почитать?
Рассчитанную на полного нуба в деле кодинга?

Сообщение отредактировал farafonov_sv - Пятница, 25.11.2011, 12:44

Ответить

farafonov_sv

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

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

Ранг: Новичок

Сообщений: 13

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

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

Подскажите пож. что означает:
Метод Select из класса Range завершен не верно.
Добавил в ваш код эту строку:

Sheets("Стоимости").Select
Range("E13").Select
Selection.FormulaR1C1 = "=SUM(Расчеты!C[1])"

Ответить

farafonov_sv

Дата: Понедельник, 28.11.2011, 14:12 | Сообщение № 8

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

Ранг: Новичок

Сообщений: 13

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

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

да тут целое тех задание.=(
Если есть время и желание буду благодарен!
Есть такая таблица:
На странице "расчеты" заполняются "потроха" таблицы предоставляемой клиентом (наименование, кол-во, балансовая стоимость)
После этого по Вашему макросу рассчитываются след. значения: ликвидационная, рыночная и залоговая стоимости.
На странице стоимости, итого по столбцам заносятся в графы таблицы.
А вот как сделать остальное я не знаю(
НА странице "Приложение_1" должны заполниться следующие строки:
Наименование, общая оценка сюда должны быть скопированы данные с листа "расчеты" Наименование, залоговая стоимость (общая оценка)
И в конце таблицы в идеале должна прописаться сумма по каждому столбцу.
и это все на 1 кнопку желательно)
Такие таблицы делаю каждый день и не по одной в ручную уже устал забивать)
ссылка на файл http://narod.ru/disk....sm.html
весит 5м, почему не пойму пустые листы там

Сообщение отредактировал farafonov_sv - Понедельник, 28.11.2011, 14:37

Ответить

farafonov_sv

Дата: Понедельник, 28.11.2011, 15:41 | Сообщение № 9

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

Ранг: Новичок

Сообщений: 13

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

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

может из за макросов так тяжко было?

К сообщению приложен файл: -28_11_11-.xlsx (11.1 Kb)

Ответить

_Boroda_

Дата: Понедельник, 28.11.2011, 18:06 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 16714

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

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Не совсем понял, но, может, так?

Для нового файла кнопку нужно скопировать из этого и вставить в новый. Или создать свою новую и назвать ее CommandButton1. Или назвать новую как захочется, но в макросе написать Private Sub CommandButton1_Click() вместо синего свое название

Перед макросами, на самом верху, есть строка Option Explicit - убейте ее
или второй строкой макроса допишите Dim r_
Private Sub CommandButton1_Click()
Dim r_
With Лист1

I. r_ = .Range("c" & Rows.Count).End(xlUp).Row
1) Rows.Count - кол-во строк на листе (различно для 2003 и 2007/2010)
2) Range("c" & Rows.Count) - виртуально "встаем" в ячейку C1048576
3) Range("c" & Rows.Count).End(xlUp) - якобы жмем Ctrl+стрелка вверх
4) r_ = .Range("c" & Rows.Count).End(xlUp).Row - присваеваем переменной r_ номер строки, в которой выделена ячейка 3).

II. Лист3.Range("e" & r_ + 1 & ":G" & r_ + 1).FormulaR1C1 = "=SUM(R[-" & r_ - 1 & "]C:R[-1]C)" - в столбцы E-G строку r_ (см. п. I.) пишем формулу =СУММ(то, что выше)

К сообщению приложен файл: _28_11_11_2.xlsb (24.9 Kb)

Ответить

farafonov_sv

Дата: Вторник, 29.11.2011, 13:52 | Сообщение № 11

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

Ранг: Новичок

Сообщений: 13

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

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

Да то что надо!!!
Спасибо Вам огромное!
Подскажите пож. а в приложении №1 как то можно что бы он в конце каждого столбца ставил Итого?

подскажите пож. как этот код перенести в другую таблицу?
Сделал все зеркально в чистой книге, пишет ошибку

Пишет: Compile error:
Variable not defined

Private Sub CommandButton3_Click() выделяет желтым
r_ = - выделяет синим

Сообщение отредактировал farafonov_sv - Вторник, 29.11.2011, 14:21

Ответить

farafonov_sv

Дата: Среда, 30.11.2011, 09:14 | Сообщение № 12

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

Ранг: Новичок

Сообщений: 13

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

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

не пойму дома все работает на работе не фига((( пишет ошибку и хоть ты тресни
Может из-за разницы в экселе?
Вот сделал сегодня файл:

К сообщению приложен файл: _Microsoft_Offi.xlsb (19.6 Kb)

Сообщение отредактировал farafonov_sv - Среда, 30.11.2011, 09:39

Ответить

farafonov_sv

Дата: Среда, 30.11.2011, 10:21 | Сообщение № 13

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

Ранг: Новичок

Сообщений: 13

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

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

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

Ответить

farafonov_sv

Дата: Четверг, 01.12.2011, 10:26 | Сообщение № 14

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

Ранг: Новичок

Сообщений: 13

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

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

Доброе утро я опять к вам)
С самим кодом разобрался и сделал таблицы как надо остальные, но остались два вопроса:
r_ = .Range("c" & Rows.Count).End(xlUp).Row - не могу расшифровать эту строчку, понимаю, что аргументу r_ присваивается выражение, но что оно делает не пойму=(
и второе
Лист3.Range("e" & r_ + 1 & ":G" & r_ + 1).FormulaR1C1 = "=SUM(R[-" & r_ - 1 & "]C:R[-1]C)" код который считает сумму в конце строки.
Для первого листа я если правильно понял можно "Лист1" пропустить будет по умолчанию на первом листе.
А что за что отвечает здесь, что бы пристроить к другим таблицам?
Спасибо

Ответить

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

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