Указание ячеек, откуда берутся данные для Label на пользовательской форме

Пользователь

Сообщений: 183 Регистрация: 14.06.2014

16.07.2017 17:30:43

Здравствуйте дорогие форумчане!

Решила спрятать сам лист с данными и все отобразить на форме. Устала прописывать все в ручную каждую отдельную ячейку.
Кто может подсказать возможно ли упростить код?

Код

price_c1.Caption = "(" & Sheets("УЧЕТ").Range("Q45") & "тг" & ")"     'ЦЕНА ВСЕХ СОСОК НА ПОЛЬЗОВАТЕЛЬСКОЙ ФОРМЕ
price_c2.Caption = "(" & Sheets("УЧЕТ").Range("Q46") & "тг" & ")"
price_c3.Caption = "(" & Sheets("УЧЕТ").Range("Q47") & "тг" & ")"
price_c4.Caption = "(" & Sheets("УЧЕТ").Range("Q48") & "тг" & ")"
price_c5.Caption = "(" & Sheets("УЧЕТ").Range("Q49") & "тг" & ")"
price_c6.Caption = "(" & Sheets("УЧЕТ").Range("Q50") & "тг" & ")"
price_c7.Caption = "(" & Sheets("УЧЕТ").Range("Q51") & "тг" & ")"
price_c8.Caption = "(" & Sheets("УЧЕТ").Range("Q52") & "тг" & ")"
price_c9.Caption = "(" & Sheets("УЧЕТ").Range("Q53") & "тг" & ")"
price_c10.Caption = "(" & Sheets("УЧЕТ").Range("Q54") & "тг" & ")"
price_c11.Caption = "(" & Sheets("УЧЕТ").Range("Q55") & "тг" & ")"
price_c12.Caption = "(" & Sheets("УЧЕТ").Range("Q56") & "тг" & ")"
price_c13.Caption = "(" & Sheets("УЧЕТ").Range("Q57") & "тг" & ")"
price_c14.Caption = "(" & Sheets("УЧЕТ").Range("Q58") & "тг" & ")"
price_c15.Caption = "(" & Sheets("УЧЕТ").Range("Q59") & "тг" & ")"
price_c16.Caption = "(" & Sheets("УЧЕТ").Range("Q60") & "тг" & ")"
price_c17.Caption = "(" & Sheets("УЧЕТ").Range("Q61") & "тг" & ")"
price_c18.Caption = "(" & Sheets("УЧЕТ").Range("Q62") & "тг" & ")"
price_c19.Caption = "(" & Sheets("УЧЕТ").Range("Q63") & "тг" & ")"
price_c20.Caption = "(" & Sheets("УЧЕТ").Range("Q64") & "тг" & ")"

Буду очень признательна!

Sanja Модератор Сообщений: 16516 Регистрация: 10.01.2013	#2 16.07.2017 17:36:42 А что такое price_c1 и далее по списку? Согласие есть продукт при полном непротивлении сторон

Sanja

Модератор

Сообщений: 16516 Регистрация: 10.01.2013

16.07.2017 17:39:22

Для отображение данных используйте ListBox
Например

Код
For I = 45 To 64 arrList(I - 45) = "(" & Sheets("УЧЕТ").Range("Q" & I) & "тг" & ")" Next Me.ListBox1.List = arrList

Изменено: Sanja - 16.07.2017 17:47:08

Согласие есть продукт при полном непротивлении сторон

bekzus

Пользователь

Сообщений: 183 Регистрация: 14.06.2014

16.07.2017 17:49:24

Здравствуйте Sanja! Вы мне не раз помогали, за что Вам еще раз огромное спасибо!
"Price_c1" это label. Там должны быть указаны цены сосок, которые я продаю. Их очень много и в одном столбце. По отдельности писать трудно. Есть также бутылочки в соседнем столбце и тд.

Listbox? Не подумала об этом. Но уже столько прописала

Значит никак нельзя упростить?

RAN Пользователь Сообщений: 7207 Регистрация: 21.12.2012	#5 16.07.2017 18:00:16 берем строчку price_c1.Caption = "(" & Sheets("УЧЕТ").Range("Q45") & "тг" & ")" вставляем на лист, разбиваем, протягиваем, сцепляем (можно и не сцеплять), копируем обратно 100500 строк готово

bekzus Пользователь Сообщений: 183 Регистрация: 14.06.2014	#6 16.07.2017 18:05:42 Супер! Спасибо RAN! Все так просто:) И без ошибок к тому же. Изменено: bekzus - 16.07.2017 23:29:39

Sanja

Модератор

Сообщений: 16516 Регистрация: 10.01.2013

16.07.2017 18:21:21

Цитата
RAN написал: берем строчку ...... вставляем на лист, разбиваем, протягиваем, сцепляем (можно и не сцеплять), копируем обратно

Андрей, зачем девушку плохому учишь?

bekzus, не привыкай к такой 'оптимизации' кода

Согласие есть продукт при полном непротивлении сторон

bekzus Пользователь Сообщений: 183 Регистрация: 14.06.2014	#8 16.07.2017 18:24:42 Почему это плохо Sanja? Ну For each Next к label'ам тут же нельзя применить. Изменено: bekzus - 16.07.2017 23:30:06

Sanja

Модератор

Сообщений: 16516 Регистрация: 10.01.2013

16.07.2017 18:29:36

Потому что это не оптимизация КОДА, а упрощение набора 100500 строк НЕоптимизированного его же.
тем более, что

Цитата
bekzus написал: Есть также бутылочки в соседнем столбце и тд.

а в ListBox'е, например, есть возможность выводить данные в несколько столбцов, без необходимости их сцепления

Согласие есть продукт при полном непротивлении сторон

bekzus

Пользователь

Сообщений: 183 Регистрация: 14.06.2014

#10

16.07.2017 18:34:45

Sanja, Listbox совсем мне всю картину испортит на форме. Не так все будет выглядеть как хотелось бы. А тут хоть код и будет длинным, но именно как надо все будет выглядеть на форме.

Я неделю рисовала эти label и textbox. Вот поэтому я спрашивала, можно ли именно код сократить с использованием labelов.

Изменено: bekzus - 16.07.2017 23:30:24

Udik

Пользователь

Сообщений: 372 Регистрация: 07.03.2016

excel 2016х64 Контакты в профиле

#11

16.07.2017 18:35:12

ну это не оптимизация кода, а другой способ

Если же всю эту аккробатику записать кодом, то тоже сомнительная оптимизация. Все операции записи/чтения на лист тормознутее обработки в памяти.

Арфы - нет, возьмите бубен.

bekzus Пользователь Сообщений: 183 Регистрация: 14.06.2014	#12 16.07.2017 18:38:49 Здравствуйте Udik! Почему запись кодом тоже будет сомнительной оптимизацией?

Udik

Пользователь

Сообщений: 372 Регистрация: 07.03.2016

excel 2016х64 Контакты в профиле

#13

16.07.2017 18:40:43

Общий алгоритм приблизительно так можно сделать
Заносим в массив данные с листа.
Пробегаемся по всем контролькам формы в for each, если в имени есть price, то пишем в капшен из массива
Кстати, не понял зачем & "тг" & ")", можно же & "тг)"

Изменено: Udik - 16.07.2017 18:43:19

Арфы - нет, возьмите бубен.

Udik

Пользователь

Сообщений: 372 Регистрация: 07.03.2016

excel 2016х64 Контакты в профиле

#14

16.07.2017 18:45:17

Цитата
bekzus написал: Почему запись кодом тоже будет сомнительной оптимизацией

Так обращений к листу куча.

Арфы - нет, возьмите бубен.

bekzus

Пользователь

Сообщений: 183 Регистрация: 14.06.2014

#15

16.07.2017 18:51:39

Цитата
Udik написал: Пробегаемся по всем контролькам формы в for each, если в имени есть price, то пишем в капшен из массива

Класс!

Код
Dim c As Control Dim I As Long For Each c In Controls If Left(c.Name, 5) = "Price" Then For I = 45 To 64 c.Value = "(" & Sheets("УЧЕТ").Range("Q" & I) & "тг" & ")" Next End If Next c

Типа такого? Но это не будет работать. Потому что я пробовала в самом начале. Я не знаю как это применять.

А как можно занести в массив мои данные?

Изменено: bekzus - 16.07.2017 23:31:13

Udik

Пользователь

Сообщений: 372 Регистрация: 07.03.2016

excel 2016х64 Контакты в профиле

#16

16.07.2017 19:08:50

дык из массива надо брать только одно значение, ну или с листа. т.е. для листа устанавливаем I=45

Код
i=45 For Each c In Controls If Left(c.Name, 5) = "Price" Then c.Value = "(" & Sheets("УЧЕТ").Range("Q" & I) & "тг" & ")" i=i+1 End If Next c

Изменено: Udik - 16.07.2017 19:09:28

Арфы - нет, возьмите бубен.

Sanja

Модератор

Сообщений: 16516 Регистрация: 10.01.2013

#17

16.07.2017 19:12:03

Или такое извращение
Забираем данные с листа в массив, формируем строку для выгрузки в ОДИН Lable (должен быть достаточного размера) и вставляем эту строку в него. в ОДИН!!!

Код

arr = Worksheets("УЧЕТ").Range("Q45:Q64").Value
For I = 1 To UBound(arr)
    If iStr <> Empty Then
        iStr = iStr & "(" & arr(I, 1) & "тг)" & vbCrLf
    Else
        iStr = "(" & arr(I, 1) & "тг)" & vbCrLf
    End If
Next
Me.Label1.Caption = iStr

Согласие есть продукт при полном непротивлении сторон

Sanja

Модератор

Сообщений: 16516 Регистрация: 10.01.2013

#18

16.07.2017 19:15:00

Цитата
bekzus написал: Но это не будет работать.

Это не будет работать, потому что не у всякого Контрола есть свойство Value. У Lable, например, его нет. Нужно проверять каждое c на то, какого ТИПА этот контрол

Согласие есть продукт при полном непротивлении сторон

Udik

Пользователь

Сообщений: 372 Регистрация: 07.03.2016

excel 2016х64 Контакты в профиле

#19

16.07.2017 19:16:40

А массив как-то так

Код
Dim Массив As Variant Массив = Sheets("УЧЕТ").Range("Q45:Q64").Value

только он псевдодвумерный будет, т.е. вторая размерность из 1 столбца состоит.

Изменено: Udik - 16.07.2017 19:17:01

Арфы - нет, возьмите бубен.

Udik

Пользователь

Сообщений: 372 Регистрация: 07.03.2016

excel 2016х64 Контакты в профиле

#20

16.07.2017 19:19:15

Цитата
Sanja написал: Это не будет работать, потому что не у всякого Контрола есть свойство Value

Да, там кэпшн должен быть.

Арфы - нет, возьмите бубен.

RAN

Пользователь

Сообщений: 7207 Регистрация: 21.12.2012

#21

16.07.2017 19:22:36

Цитата
Sanja написал: Андрей, зачем девушку плохому учишь?

Мона и хорошему

Код
Private Sub UserForm_Initialize() Dim ar, i& ar = Sheets("УЧЕТ").Range("Q1:Q5").Value For i = 1 To UBound(ar) Controls("price_c" & i).Caption = "(" & ar(i, 1) & "тг)" Next End Sub

Код
ar = Sheets("УЧЕТ").Range("Q45:Q5000").Value

ничего не изменит, лишь-бы контролов хватило.

Изменено: RAN - 16.07.2017 19:26:30

Sanja

Модератор

Сообщений: 16516 Регистрация: 10.01.2013

#22

16.07.2017 19:24:33

Ага, сам сначала

Цитата
Sanja написал: Андрей, зачем девушку плохому учишь?

но зато потом

Цитата
Sanja написал: Или такое извращение

Согласие есть продукт при полном непротивлении сторон

bekzus Пользователь Сообщений: 183 Регистрация: 14.06.2014	#23 16.07.2017 19:25:51 Столько информации!! Сейчас все надо усвоить и попытаться применить все. Когда все получится напишу:) Такое большое супер "ПСЕВДОДВУМЕРНОЕ" спасибо:)! Аж язык сломаешь.

bekzus Пользователь Сообщений: 183 Регистрация: 14.06.2014	#24 16.07.2017 20:23:26 Все получилось! RAN, Ваш код использовала. Работает все как по маслу:) Какая красота! Всем всем огромное спасибо дорогие эксперты VBA! Sanja, Udik, Ran вы классные!

bekzus

Пользователь

Сообщений: 183 Регистрация: 14.06.2014

#25

17.07.2017 20:55:43

Здравствуйте еще раз дорогие Sanja, Udik, Ran!
Попыталась применить формулу Ran
для:

Код

Sheets("УЧЕТ").Range("N3").Value = Val(Sheets("УЧЕТ").Range("M3")) - Val(Sheets("ПРОДАЖИ").Cells(1, 10))
Sheets("УЧЕТ").Range("N4").Value = Val(Sheets("УЧЕТ").Range("M4")) - Val(Sheets("ПРОДАЖИ").Cells(1, 11))
Sheets("УЧЕТ").Range("N5").Value = Val(Sheets("УЧЕТ").Range("M5")) - Val(Sheets("ПРОДАЖИ").Cells(1, 12))
Sheets("УЧЕТ").Range("N6").Value = Val(Sheets("УЧЕТ").Range("M6")) - Val(Sheets("ПРОДАЖИ").Cells(1, 13))
Sheets("УЧЕТ").Range("N7").Value = Val(Sheets("УЧЕТ").Range("M7")) - Val(Sheets("ПРОДАЖИ").Cells(1, 14))
Sheets("УЧЕТ").Range("N8").Value = Val(Sheets("УЧЕТ").Range("M8")) - Val(Sheets("ПРОДАЖИ").Cells(1, 15))
Sheets("УЧЕТ").Range("N9").Value = Val(Sheets("УЧЕТ").Range("M9")) - Val(Sheets("ПРОДАЖИ").Cells(1, 16))
Sheets("УЧЕТ").Range("N10").Value = Val(Sheets("УЧЕТ").Range("M10")) - Val(Sheets("ПРОДАЖИ").Cells(1, 17))
Sheets("УЧЕТ").Range("N11").Value = Val(Sheets("УЧЕТ").Range("M11")) - Val(Sheets("ПРОДАЖИ").Cells(1, 18))
Sheets("УЧЕТ").Range("N12").Value = Val(Sheets("УЧЕТ").Range("M12")) - Val(Sheets("ПРОДАЖИ").Cells(1, 19))
Sheets("УЧЕТ").Range("N13").Value = Val(Sheets("УЧЕТ").Range("M13")) - Val(Sheets("ПРОДАЖИ").Cells(1, 20))
Sheets("УЧЕТ").Range("N14").Value = Val(Sheets("УЧЕТ").Range("M14")) - Val(Sheets("ПРОДАЖИ").Cells(1, 21))
Sheets("УЧЕТ").Range("N15").Value = Val(Sheets("УЧЕТ").Range("M15")) - Val(Sheets("ПРОДАЖИ").Cells(1, 22))
Sheets("УЧЕТ").Range("N16").Value = Val(Sheets("УЧЕТ").Range("M16")) - Val(Sheets("ПРОДАЖИ").Cells(1, 23))
Sheets("УЧЕТ").Range("N17").Value = Val(Sheets("УЧЕТ").Range("M17")) - Val(Sheets("ПРОДАЖИ").Cells(1, 24))
Sheets("УЧЕТ").Range("N18").Value = Val(Sheets("УЧЕТ").Range("M18")) - Val(Sheets("ПРОДАЖИ").Cells(1, 25))
Sheets("УЧЕТ").Range("N19").Value = Val(Sheets("УЧЕТ").Range("M19")) - Val(Sheets("ПРОДАЖИ").Cells(1, 26))
Sheets("УЧЕТ").Range("N20").Value = Val(Sheets("УЧЕТ").Range("M20")) - Val(Sheets("ПРОДАЖИ").Cells(1, 27))
Sheets("УЧЕТ").Range("N21").Value = Val(Sheets("УЧЕТ").Range("M21")) - Val(Sheets("ПРОДАЖИ").Cells(1, 28))
Sheets("УЧЕТ").Range("N22").Value = Val(Sheets("УЧЕТ").Range("M22")) - Val(Sheets("ПРОДАЖИ").Cells(1, 29))

и получилось вот это, но почему то он не работает. Или формула только действует на строки, а не на столбцы. Или дело все таки в пользовательской форме?

Код

Dim ar, i&
ar = Sheets("ПРОДАЖИ").Range(Cells(1, 10), Cells(1, 29)).Value 
For i = 1 To UBound(ar)
    Sheets("УЧЕТ").Cells(i + 2, 14).Value = Val(Sheets("УЧЕТ").Cells(i + 2, 15).Value) - Val(ar(1, i))
Next

Подскажите пожалуйста, что я делаю не так.

Изменено: bekzus - 17.07.2017 20:56:53

RAN Пользователь Сообщений: 7207 Регистрация: 21.12.2012	#26 17.07.2017 21:36:02 Как обратиться к диапазону из VBA Собака тут порылась.

bekzus

Пользователь

Сообщений: 183 Регистрация: 14.06.2014

#27

17.07.2017 22:17:46

Спасибо RAN за ссылку. Было очень познавательно

Применила знания полученные из статьи. Поняла, что либо указываешь диапазон так:

Код
Dim ar, i& ar = Sheets("ПРОДАЖИ").Range("J1:AC1").Value For i = 1 To UBound(ar) Sheets("УЧЕТ").Cells(i + 2, 14).Value = Val(Sheets("УЧЕТ").Cells(i + 2, 15).Value) - Val(ar(1, i)) Next

Либо так:

Код

    Dim ar, i&
    ar = Sheets("ПРОДАЖИ").Range(Sheets("ПРОДАЖИ").Cells(1, 10), Sheets("ПРОДАЖИ").Cells(1, 29)).Value 
    For i = 1 To UBound(ar)
        Sheets("УЧЕТ").Cells(i + 2, 14).Value = Val(Sheets("УЧЕТ").Cells(i + 2, 15).Value) - Val(ar(1, i))
    Next

Но это не помогло

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

В любом случае спасибо за подсказку

Изменено: bekzus - 17.07.2017 23:00:56

Sanja

Модератор

Сообщений: 16516 Регистрация: 10.01.2013

#28

17.07.2017 22:23:08

Цитата
bekzus написал: но почему то он не работает Но это не помогло

а КАК он должен работать? Не помогло ЧЕМУ?

Согласие есть продукт при полном непротивлении сторон

bekzus

Пользователь

Сообщений: 183 Регистрация: 14.06.2014

#29

17.07.2017 22:33:36

Здравствуйте Sanja! Хотела оптимизировать код, подобный вчерашнему, который вы не рекомендуете использовать и я решила попытаться оптимизировать все коды.

Код

Sheets("УЧЕТ").Range("N3").Value = Val(Sheets("УЧЕТ").Range("M3")) - Val(Sheets("ПРОДАЖИ").Cells(1, 10))
Sheets("УЧЕТ").Range("N4").Value = Val(Sheets("УЧЕТ").Range("M4")) - Val(Sheets("ПРОДАЖИ").Cells(1, 11))
Sheets("УЧЕТ").Range("N5").Value = Val(Sheets("УЧЕТ").Range("M5")) - Val(Sheets("ПРОДАЖИ").Cells(1, 12))
Sheets("УЧЕТ").Range("N6").Value = Val(Sheets("УЧЕТ").Range("M6")) - Val(Sheets("ПРОДАЖИ").Cells(1, 13))
Sheets("УЧЕТ").Range("N7").Value = Val(Sheets("УЧЕТ").Range("M7")) - Val(Sheets("ПРОДАЖИ").Cells(1, 14))
Sheets("УЧЕТ").Range("N8").Value = Val(Sheets("УЧЕТ").Range("M8")) - Val(Sheets("ПРОДАЖИ").Cells(1, 15))
Sheets("УЧЕТ").Range("N9").Value = Val(Sheets("УЧЕТ").Range("M9")) - Val(Sheets("ПРОДАЖИ").Cells(1, 16))
Sheets("УЧЕТ").Range("N10").Value = Val(Sheets("УЧЕТ").Range("M10")) - Val(Sheets("ПРОДАЖИ").Cells(1, 17))
Sheets("УЧЕТ").Range("N11").Value = Val(Sheets("УЧЕТ").Range("M11")) - Val(Sheets("ПРОДАЖИ").Cells(1, 18))

Решила применить код RAN, который мне показался более менее понятным.

Код
Dim ar, i& ar = Sheets("ПРОДАЖИ").Range("J1:AC1").Value For i = 1 To UBound(ar) Sheets("УЧЕТ").Cells(i + 2, 14).Value = Val(Sheets("УЧЕТ").Cells(i + 2, 15).Value) - Val(ar(1, i)) Next

Прочитала статью, которую скинул RAN. Код был с ошибками относительно указаний диапазонов. Поняла, что нужно точнее указывать. Исправила, но не помогло.

RAN Пользователь Сообщений: 7207 Регистрация: 21.12.2012	#30 17.07.2017 22:58:41 У вас арихметика хромает, или зрение слабое Range("M3")==Cells(3, 13) Range("N3") ==Cells(3, 14) Range("O3")==Cells(3, 15)