Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
макрос последовательной подстановки данных из нескольких столбцов листа А в заданные ячейки листа Б
 
#1
24.04.2012 21:29:51
Всем доброго времени суток!  
 
Похоже "венцом" схемы реализации моих перерасчетов будет исполнение данной задачки.  
Помогите пожалуйста с данной проблемой..  
 
В написании макросов абсолютный НОЛЬ, хотя учиться никогда не поздно, а лучше всего это делать на конкретном схожем примере..  
Перерасчетов подобных, у меня предостаточно, помогите пожалуйста на одном примере, а с остальными постараюсь как нибудь разобраться  
 
Так вот, суть возникшей сложности :  
 
Необходим макрос или формула, которые значения ячеек из диапазона О8:О3000 листа Данные последовательно подставят в ячейку С77 листа Расчеты , а значения ячеек из диапазона К8:К3000 листа Данные последовательно подставят в ячейку Н76 листа Расчеты, при том что подставление должно идти параллельно по строкам А8:АP8  
Это связано с тем, что значения О8 и К8 являются исходными данными для формул, которые распологаются на листе Расчеты и ячейки С77 и Н76 являются входными ячейками в существующую цепь перерасчетов. А вот результат последовательных перерасчетов из ячейки Н120 листа Расчеты необходимо вернуть на лист Данные в ячейки столбца R  
Помогите пожалуйста реализовать данную схему  !    
Было бы замечательно, привязать исполнение данного макроса к кновке "ВЫПОЛНИТЬ ПЕРЕСЧЕТ"  
 
на основе этой подсказки постараюсь реализовать остальные схемы перерасчетов, которые фигурирую на даном листе.  вот только входные ячейки листа Расчеты останутся прежними, а именно С77 и Н76. В связи с этим возникает вопрос - Как организовать выполнение двух и более макросов,которые имеют входные и выходные данные на одних и тех же листах, но ссылаются на разные диапазоны ячеек. Вернее на листе Данные у них диапазоны разные, а вот на листе Расчеты ячейки одни и те же..  
 
Не знаю, как доходчиво смог изложить существующую проблему..    Четко понимаю, что куда нужно подставлять, а вот не знаю как организовать это подставление последовательным и без "наворачивания" книги лишними дополнительными листами..
-
 
 
 
#2
25.04.2012 00:47:01
Примерно так  
 
Sub bb()  
Dim i&, Inp1 As Range, Inp2 As Range, Out As Range  
Set Inp1 = [Расчеты!C77]
Set Inp2 = [Расчеты!H76]
Set Out = [Расчеты!H120]
With Sheets("Данные")  
For i = 8 To 3000  
   Inp1 = .Cells(i, "O")  
   Inp2 = .Cells(i, "K")  
   .Cells(i, "R") = Out  
Next  
End With  
End Sub
 
 
 
#3
25.04.2012 22:06:56
Все работает !!!!  
 
Спасибо !  
 
Вот только вопрос - как избавиться от #ЗНАЧ!?    
Сообщение об ошибке возникает в столбце R в том случае если нет значений в ячейках  диапазонов О8:О3000 и K8:K3000  
 
и еще один момент - исполнение макроса подстановки, необходимо выполнять только в случае наличия значений и в ячейки О8 и в К8. Если в какой то из них нет значения, то R8 оставлять пустой, а переходить на проверку и заполнение О8, К8 и R8 соответственно.
-
 
 
 
#4
26.04.2012 13:09:09
Так как ??   Может кто нибудь помочь внести корректировки в макрос ?
 
 
 
#5
26.04.2012 13:14:43
Добавьте оператор If  
 
Sub bb()  
Dim i&, Inp1 As Range, Inp2 As Range, Out As Range  
Set Inp1 = [Расчеты!C77]
Set Inp2 = [Расчеты!H76]
Set Out = [Расчеты!H120]
With Sheets("Данные")  
For i = 8 To 3000  
if .Cells(i, "O")<>"" and .Cells(i, "K")<>"" then  
Inp1 = .Cells(i, "O")  
Inp2 = .Cells(i, "K")  
.Cells(i, "R") = Out  
end if  
Next  
End With  
End Sub
 
 
 
#6
26.04.2012 14:18:17
Уважаемый, Казанский, Вы в очередной раз стали "соломинкой для утопающего" :)  
 
Огромной спасибо !  
 
все работает замечательно!  
 
надеюсь, на основе этого макроса смогу реализовать намеченные задумки по пересчетам..
-
 
 
 
#7
14.05.2012 15:39:10
Уважаемый, Казанский!  
Макрос работает "на ура"! :) Вот только чем больше исходных значений.., тем дольше приходится ждать окончания работы макроса.  Занимает кучу времени!  
Да и каждый раз пересчет выполняется с самого начала, не смотря на то, что результат предыдущей работы макроса уже фигурирует на листе..  
 
Я находил несколько вариантов ускорения работы макроса на сайте, но не соображу как их можно корректно внести в данный макрос.  
Если не составит труда, подскажите пожалуйста, как можно ускорить работу данного макроса при использовании входных диапазонов в 10-15000 значений-строк..
-
 
 
 
#8
14.05.2012 16:15:34
{quote}{login=skif}{date=14.05.2012 03:39}{thema=}{post}Занимает кучу времени!{/post}{/quote}  
охотно верю :)  
это ж сдохнуть можно - записать два значения в ячейки листа, дождаться, когда закончится пересчет листа, считать результат из ячейки листа и записать его опять на лист в другое место...  
 
и так - несколько тысяч раз подряд.  
на месте Excel'я я бы Вас убил :))  
(шутка)  
 
>> как можно ускорить...  
имхо, очень желательно перенести все расчеты в макрос - ускорение будет в разы, а то и в десятки раз.  
правда, тут всё зависит от самих расчётов :)  
 
у меня был случай - ускоряли с человеком его самодельный макрорекодерный макрос с кое-как прикрученными циклами... суть была такая же - изменение в цикле входных данных для расчетов, запись на лист, расчет, считывание с листа, запись в журнал работы.  
 
просидели один день + две ночи, ускорили почти в 2000 раз (я сам удивился! :)  
правда, там некоторая часть ускорения пришлась просто на отказ от селектов и активейтов (чего в макросе Казанского нет) - раз в 15-20. остальное - 100-120-кратное ускорение - только за счет переноса всех расчетов в макрос.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#9
14.05.2012 17:27:25
ну...   вижу у Вас опыт комментирования сообщений большой.. ;)  
а если по делу - предложение перегнать пересчеты в макрос хорошее, НО - к большому моему сожалению, уровень познаний в написании макросов у меня не велик (можно сказать вообще отсутствует), а привлекать людей, не вижу смысла, т.к. макрос подстановки данных всего лишь "маленькая шестеренка" в общем "механизме" пересчетов..  Весь "механизм" работает благополучно.., вот только ожидание результата немного напрягает..  
Конечно, если это время исполнения макроса невозможно сократить.., то вопрос сам собой снимается. :) чудеса иногда случаются.., но только там где это возможно :)
 
 
 
#10
14.05.2012 17:35:48
Можно сократить на некоторую долю - если заполнять результатами не ячейки по-одной, а созданный пустой массив.  
В конце одним махом его выгрузить на лист.  
Если расчёты не слишком сложные - может быть время сократится даже на треть.
 
 
 
#11
14.05.2012 17:40:46
ну, если переносить расчеты в макрос не можете / не хотите,  
то могу дать только пару мелких предложений в дополнение к макросу Казанского.    
 
Sub bb()  
Dim i&, Inp1 As Range, Inp2 As Range, Out As Range  
Set Inp1 = [Расчеты!C77]
Set Inp2 = [Расчеты!H76]
Set Out = [Расчеты!H120]
Application.ScreenUpdating=False  
Application.Calculation = xlCalculationManual  
With Sheets("Данные")  
For i = 8 To 3000  
if Len(.Cells(i, "O")) then  
if Len(.Cells(i, "K")) then  
Inp1.Value = .Cells(i, "O").Value  
Inp2.Value = .Cells(i, "K").Value  
Application.Calculate  
.Cells(i, "R").Value = Out.Value  
end if  
end if  
Next  
End With  
Application.Calculation=xlCalculationAutomatic  
End Sub  
 
ускорение однозначно будет, а вот насколько значительным - судить сложно.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#12
14.05.2012 17:48:05
{quote}{login=Hugo}{date=14.05.2012 05:35}{thema=}{post}Можно сократить на некоторую долю - если заполнять результатами не ячейки по-одной, а созданный пустой массив.  
В конце одним махом его выгрузить на лист.  
Если расчёты не слишком сложные - может быть время сократится даже на треть.{/post}{/quote}  
Предложение интересное, но данные в диапазонах возникают ежесуточно и из них сделать готовый массив данных невозможно.  
Работа макроса инициализируется по нажатию на кнопку запуска и его ускорить думаю возможно, если не высчитывать уже имеющиеся значения, а "продолжить" дальше
-
 
 
 
#13
14.05.2012 17:48:56
Попробуйте так (без проверки):  
 
Sub bb()  
   Dim i&, Inp1 As Range, Inp2 As Range, Out As Range  
   Dim a, b, c  
   Set Inp1 = [Расчеты!C77]
   Set Inp2 = [Расчеты!H76]
   Set Out = [Расчеты!H120]
   With Sheets("Данные")  
   a = .Range("O8:O3000").Value  
   b = .Range("K8:K3000").Value  
   c = .Range("R8:R3000").Value  
       For i = 1 To 2992  
           If a(i, 1) <> "" Then  
           If b(i, 1) <> "" Then  
               Inp1 = a(i, 1)  
               Inp2 = b(i, 1)  
               c(i, 1) = Out  
           End If  
           End If  
       Next  
    .Range("R8:R2992").Value = c  
   End With  
End Sub
 
 
 
#14
14.05.2012 17:53:08
только сейчас заметил:  
 
вероятно, в макросе имеется небольшпя неточность для случаев пустых ячеек в столбцах O и K - сейчас макрос просто пропускает такие строки, НЕ МЕНЯЯ соответствующую ячейку в столбце R.  
скорее всего, это логически неправильно, поэтому сразу после строки  
With Sheets("Данные")  
 
я советую добавить строку  
.[r8:r3000].clearcontents
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#15
14.05.2012 17:55:37
Если данные обрабатываются группами - то думаю несложно этот код доработать:  
1.определить разницу между последней заполненной строкой в столбцах K и R  
2.создать массивы из этих трёх диапазонов  
3.обработать как выше, но цикл for i=1 to ubound©  
 
Но тестить не на чем - сообразите хоть какие данные и формулы в файле.
 
 
 
#16
14.05.2012 18:09:22
Сам что-то придумал... :)  
 
Sub bb()  
   Dim i&, Inp1 As Range, Inp2 As Range, Out As Range  
   Dim a, b, c  
   Dim iLastRow1&, iLastRow2&  
 
   Set Inp1 = [Расчеты!C77]
   Set Inp2 = [Расчеты!H76]
   Set Out = [Расчеты!H120]
   With Sheets("Данные")  
       iLastRow1 = .Range("O" & Rows.Count).End(xlUp).Row  
       iLastRow2 = .Range("R" & Rows.Count).End(xlUp).Row  
       a = .Range("O" & iLastRow2 & ":O" & iLastRow1).Value  
       b = .Range("K" & iLastRow2 & ":K" & iLastRow1).Value  
       c = .Range("R" & iLastRow2 & ":R" & iLastRow1).Value  
       For i = 1 To UBound©  
           If a(i, 1) <> "" Then  
               If b(i, 1) <> "" Then  
                   Inp1 = a(i, 1)  
                   Inp2 = b(i, 1)  
                   c(i, 1) = Out  
               End If  
           End If  
       Next  
       .Range("R" & iLastRow2 & ":R" & iLastRow1).Value = c  
   End With  
End Sub
 
 
 
#17
15.05.2012 19:18:28
Попробовал предложенные варианты и убедился, что есть тут место для чуда :)  все работает ЗНАЧИТЕЛЬНО ШУСТРЕЕ и вполне устраивает.    
 
Товарищи, аль Господа,  ikki & Hugo   крайне признателен Вам за активное участие и содействие.  Спасибо !
-
 
 
 
#18
15.05.2012 19:33:49
Hugo, данный макрос интересен, но есть один нюанс - при целенаправленном/случайном удалении какого-нибудь значения из диапазонов в столбцах О, К, R  после первого исполнения макроса, блокируется его работа при повторном исполнении/перерасчете.  Дальнейшее выполнение не возможно, до тех пор пока не удалить все данные расчета по столбцу R.  Это не очень удобно, т.к. провоцирует дополнительные манипуляции пользователя.  
Если ни где ни чего не удалять в столбцах, то действительно все работает достаточно быстро.
-
 
 
 
#19
15.05.2012 19:57:52
У меня возник дополнительный вопрос - каким образом и где конкретно необходимо изменить макрос  
 
Sub bb()  
Dim i&, Inp1 As Range, Inp2 As Range, Out As Range  
Dim a, b, c  
Set Inp1 = [Расчеты!C77]
Set Inp2 = [Расчеты!H76]
Set Out = [Расчеты!H120]
With Sheets("Данные")  
a = .Range("O8:O3000").Value  
b = .Range("K8:K3000").Value  
c = .Range("R8:R3000").Value  
For i = 1 To 2992  
If a(i, 1) <> "" Then  
If b(i, 1) <> "" Then  
Inp1 = a(i, 1)  
Inp2 = b(i, 1)  
c(i, 1) = Out  
End If  
End If  
Next  
.Range("R8:R2992").Value = c  
End With  
End Sub  
 
для того чтоб использовать дополнительную "троицу" диапазонов\столбцов листа Данные, так же подставляя их в те же самые "входные ячейки" листа Расчеты.  
На пример подстановка данных из столбцов  C и G c заполнением результата в столбец I.  
Подстановка "троицы №2" можно выполнить после, исполнения "троицы №1" о которой говорилось ранее и на которую написан данный макрос.  
 
Если проще это расписать на макросе предложенном ikki, то можно и на нем..  Это не принципиально. Скорость исполнения обоих макросов приемлима. более того скажу, что оба макроса "взял на заметку" для дальнейшей работы..
-
 
 
 
#20
15.05.2012 20:00:26
Каким бы хорошим не был макрос, он не будет работать (или ничего не обработает), если удалять данные. Исключите вот это:    
"при целенаправленном/случайном удалении какого-нибудь значения из диапазонов в столбцах О, К, R"
 
 
 
#21
15.05.2012 20:03:40
Дело в том, что есть задумки об использовании нескольких "троиц" столбцов данных для увеличения статистики перерасчетов.  Комп вроде "мощный" намерен напрягать его по полной.. :) пусть отрабатывает свою стоимость :)
-
 
 
 
#22
15.05.2012 20:19:10
{quote}{login=Юрий М}{date=15.05.2012 08:00}{thema=}{post}Каким бы хорошим не был макрос, он не будет работать (или ничего не обработает), если удалять данные. {/post}{/quote}  
Ваше замечание логично, но к сожаление без этого не обойтись. "Вылеты из приемлимых" значений в столбце R все же приходится удалять и вторичный запуска этого макроса неизбежен.  
Конечно можно организовать Кнопку по "стиранию" всего диапазона R, но повторяюсь, что это дополнительные манипуляции
-
 
 
 
#23
15.05.2012 20:28:18
{quote}{login=skif}{date=15.05.2012 08:19}{thema=Re: }{post}Конечно можно организовать Кнопку по "стиранию" всего диапазона R, но повторяюсь, что это дополнительные манипуляции{/post}{/quote}Зачем кнопку? Перечитайте сообщение ikki от 14.05.2012, 17:53
 
 
 
#24
15.05.2012 21:10:59
Вы правы. Спасибо, что обратили мое внимание на рекомендацию ikki.  
 
может порекомендуете с чего начать в познании азов VBB? не постыжусь слова "для чайников" :)
-
 
 
 
#25
15.05.2012 21:19:26
Список полезной литературы можно посмотреть в "Копилке"
 
 
 
#26
15.05.2012 23:11:30
Благодарю!
-
 
 
 
#27
15.05.2012 23:14:29
Так как с дополнительным вопрос - каким образом и где конкретно необходимо изменить макрос ?  
 
Sub bb()  
Dim i&, Inp1 As Range, Inp2 As Range, Out As Range  
Dim a, b, c  
Set Inp1 = [Расчеты!C77]
Set Inp2 = [Расчеты!H76]
Set Out = [Расчеты!H120]
With Sheets("Данные")  
a = .Range("O8:O3000").Value  
b = .Range("K8:K3000").Value  
c = .Range("R8:R3000").Value  
For i = 1 To 2992  
If a(i, 1) <> "" Then  
If b(i, 1) <> "" Then  
Inp1 = a(i, 1)  
Inp2 = b(i, 1)  
c(i, 1) = Out  
End If  
End If  
Next  
.Range("R8:R2992").Value = c  
End With  
End Sub  
 
для того чтоб использовать дополнительную "троицу" диапазонов\столбцов листа Данные, так же подставляя их в те же самые "входные ячейки" листа Расчеты.  
На пример подстановка данных из столбцов C и G c заполнением результата в столбец I.  
Подстановка "троицы №2" можно выполнить после, исполнения "троицы №1" о которой говорилось ранее и на которую написан данный макрос.  
 
Если проще это расписать на макросе предложенном ikki, то можно и на нем.. Это не принципиально.
-
 
 
 
#28
15.05.2012 23:40:14
Чтоб сильно не мудрить, напишите ещё один такой макрос Sub cc() с другими диапазонами в строках  
a = .Range("O8:O3000").Value  
b = .Range("K8:K3000").Value  
c = .Range("R8:R3000").Value  
.Range("R8:R2992").Value = c  
и может быть числом в строке  
For i = 1 To 2992  
Кстати, выгрузка вероятно должна быть  
.Range("R8:R3000").Value = c  
что-то я там накосячил чуть..  
 
Ну и затем выполнять макрос  
 
sub запускающий()  
bb  
cc  
end sub
 
 
 
#29
22.05.2012 14:27:39
Согласно Вашим рекомендациям я все и сделал  
Все работает ОТЛИЧНО !   спасибо ОГРОМНОЕ !  :)  
 
P.S.: чуток занят был.., по сему благодарю с некоторым опозданием. НО ИСКРЕННЕ! :)
-
 
 
 
#30
26.05.2012 21:04:02
{quote}{login=Hugo}{date=15.05.2012 11:40}{thema=}{post}Чтоб сильно не мудрить, напишите ещё один такой макрос Sub cc() с другими диапазонами в строках... Ну и затем выполнять макрос  
sub запускающий()...{/post}{/quote}  
Если не сложно.. Подскажите, пожалуйста, как эти существующие однотипные макросы запускать одновременно на исполнение по нажатию на одну кнопку ?  
Макросы с кнопкой все в одном листе книги..
-
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх