Друзья немного сократить код VBA

@hannu · Регистрация: 08.04.2015

Author24 — интернет-сервис помощи студентам

Ребят, написал код. Но он слишком большой. очень много If. Как бы мне его сократить.
Механика кода проста. Есть сводная таблица, он оттуда просматривает значения и суммирует их вставляя в определенные ячейки результат моего вычисления. Т.е он находит сначала 2011 и все что меньше, выдает сумму, потом по 2012 также и до 2016.

Visual Basic

Sub syma()
Dim w As Worksheet
Dim s1, s2, s3, s4, s5, s6, x As Integer
Dim a, b, c, d, e, f, lr As String
a = "2012"
b = "2013"
d = "2014"
e = "2015"
f = "2016"
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set w = ActiveWorkbook.Sheets("Ò3")
For x = 5 To lr
c = Cells(x, 1)
If (Left(c, 4) < a) Then
s1 = s1 + Cells(x, 2)
w.Cells(4, 3) = s1
End If
 
If (Left(c, 4) = a) Then
s2 = s2 + Cells(x, 2)
w.Cells(5, 3) = s2
End If
 
If (Left(c, 4) = b) Then
s3 = s3 + Cells(x, 2)
w.Cells(6, 3) = s3
End If
If (Left(c, 4) = d) Then
s4 = s4 + Cells(x, 2)
w.Cells(7, 3) = s4
End If
If (Left(c, 4) = e) Then
s5 = s5 + Cells(x, 2)
w.Cells(8, 3) = s5
End If
If (Left(c, 4) = f) Then
s6 = s6 + Cells(x, 2)
w.Cells(9, 3) = s6
End If
 
Next x
End Sub

@Казанский · 06.12.2016, 16:06

hannu, похоже тут функцией СУММЕСЛИ можно обойтись, приложите файл-пример.

@hannu · 06.12.2016, 16:25 **[ТС]**

Вопрос в том, что там, обычная таблица завязана на сводную.
Т.е получается динамический диапазон.
Т.е когда то данных больше, когда то меньше. А при помощи функции нужно обозначать определенный диапазон

@hannu · 06.12.2016, 16:28 **[ТС]**

Вот прикладываю.

@unick12345 · 06.12.2016, 16:57

типа такого?

@hannu · 06.12.2016, 17:02 **[ТС]**

Не совсем, он из сводной таблицы(Лист С3) должен брать необходимые данные, суммировать их и вставлять в Т3.
Макрос на это есть, но там много переменных и If. Мне челвоек сказал что можно при помощи While сделать,
он будет смотреть в сводной таблицы данные, если 4 символа левых от даты = 2016 то суммировать все ячейки с кол-во обьекта и 2016 годом. Ну и также по 2015 , 2014, 2013

@unick12345 · 06.12.2016, 17:08

hannu, а посмотри сам макрос. Да и... там нет третьего листа. В данном случае данные берет с первого, и результат ставит на 3 (то есть O3).

Добавлено через 1 минуту
а если нужно с 1 во второй (С3 в ТЗ), то просто в макросе поменять листы

Добавлено через 30 секунд

Visual Basic

Sub syma()
Dim w As Worksheet
Dim s1, s2, s3, s4, s5, s6, x As Integer
Dim a, b, c, d, e, f, lr As String
a = "2012"
b = "2013"
d = "2014"
e = "2015"
f = "2016"
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set w = ActiveWorkbook.Sheets("O3")
For x = 5 To lr
c = Cells(x, 1)
Select Case Left(c, 4)
Case a: w.Cells(5, 3) = w.Cells(5, 3) + Cells(x, 2)
Case b: w.Cells(6, 3) = w.Cells(6, 3) + Cells(x, 2)
Case d: w.Cells(7, 3) = w.Cells(7, 3) + Cells(x, 2)
Case e: w.Cells(8, 3) = w.Cells(8, 3) + Cells(x, 2)
Case f: w.Cells(9, 3) = w.Cells(9, 3) + Cells(x, 2)
Case Else
w.Cells(4, 3) = w.Cells(4, 3) + Cells(x, 2)
End Select
Next x
End Sub

@hannu · 06.12.2016, 17:15 **[ТС]**

Точно, через case. Блин сейчас поковыряюсь, сделаю. А теперь другой момент, в случае если данные поменяются он их проссумирует с предыдущими. тут я так понимаю нужен цикл на обнуление или как?

@unick12345 · 06.12.2016, 17:16

то есть?

@hannu · 06.12.2016, 17:18 **[ТС]**

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

Получается если на сводной таблице 3 раза запустить код он будет тупо складывать и складывать складывать и складывать.
А нужно чтобы один раз выдал необходимое значение.

@unick12345 · 06.12.2016, 17:18

Visual Basic

1	w.Range(w.Cells(4, 3), w.Cells(9, 3)).ClearContents

типа такого??? это перед циклом, после присвоения листа

@hannu · 06.12.2016, 17:27 **[ТС]**

да я так понимаю он очищает диапазон ячеек. Но тогда нужно будет 2 кнопки делать. Одной кнопкой очищать а второй вносить.

И второй небольшой вопрос, как сделать, чтобы он не по каждому столбцу а например по Case:a находил значения меньше 2012 и данные по колличеству квартир и по площади отражал также в таблице Т3

Добавлено через 3 минуты
Т.е для следующих данных нужно будет составлять такой же код, но только смещать его верно?

toiai · 06.12.2016, 17:31

А почему нельзя составить сводную таблицу на листе ТЗ напрямую с данных?

@unick12345 · 06.12.2016, 17:33

hannu, не 2 кнопки...

Visual Basic

Sub syma()
Dim w As Worksheet
Dim x As Integer
Dim a, b, c, d, e, f, lr As String
a = "2012"
b = "2013"
d = "2014"
e = "2015"
f = "2016"
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set w = ActiveWorkbook.Sheets("Т3")
w.Range(w.Cells(4, 9), w.Cells(9, 9)).ClearContents
w.Range(w.Cells(4, 11), w.Cells(9, 11)).ClearContents
w.Range(w.Cells(4, 13), w.Cells(9, 13)).ClearContents
For x = 5 To lr
c = Cells(x, 1)
Select Case Left(c, 4)
Case a: w.Cells(5, 9) = w.Cells(5, 9) + Cells(x, 2)
Case b: w.Cells(6, 9) = w.Cells(6, 9) + Cells(x, 2)
Case d: w.Cells(7, 9) = w.Cells(7, 9) + Cells(x, 2)
Case e: w.Cells(8, 9) = w.Cells(8, 9) + Cells(x, 2)
Case f: w.Cells(9, 9) = w.Cells(9, 9) + Cells(x, 2)
Case Else
w.Cells(4, 9) = w.Cells(4, 9) + Cells(x, 2)
w.Cells(4, 11) = w.Cells(4, 11) + Cells(x, 4)
w.Cells(4, 13) = w.Cells(4, 13) + Cells(x, 6)
End Select
Next x
End Sub

если у тебя файл такой же как выставлен, посмотри после выполнения макроса лист ТЗ

@hannu · 06.12.2016, 17:43 **[ТС]**

Потому что сводная таблица привязана у меня к выгрузке и делается совершенно из другого массива данных. А таблица это некоторое визуальное отображение части необходимой информации

Добавлено через 10 минут

Visual Basic

Option Explicit
 
Sub syma()
Dim w As Worksheet
Dim x As Integer
Dim a, b, c, d, e, f, lr As String
a = "2012"
b = "2013"
d = "2014"
e = "2015"
f = "2016"
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set w = ActiveWorkbook.Sheets("Т3")
w.Range(w.Cells(4, 3), w.Cells(9, 3)).ClearContents
w.Range(w.Cells(4, 5), w.Cells(9, 5)).ClearContents
w.Range(w.Cells(4, 7), w.Cells(9, 7)).ClearContents
For x = 5 To lr
c = Cells(x, 1)
Select Case Left(c, 4)
Case a: w.Cells(5, 3) = w.Cells(5, 3) + Cells(x, 2)
Case b: w.Cells(6, 3) = w.Cells(6, 3) + Cells(x, 2)
Case d: w.Cells(7, 3) = w.Cells(7, 3) + Cells(x, 2)
Case e: w.Cells(8, 3) = w.Cells(8, 3) + Cells(x, 2)
Case f: w.Cells(9, 3) = w.Cells(9, 3) + Cells(x, 2)
Case Else
w.Cells(4, 3) = w.Cells(4, 3) + Cells(x, 2)
w.Cells(4, 5) = w.Cells(4, 5) + Cells(x, 4)
w.Cells(4, 7) = w.Cells(4, 7) + Cells(x, 6)
 
End Select
Next x
End Sub

вот так он вставляет в те значения куда нужно. Единственный момент он заполняет только первую строку по 2011, остальные пустые.

@unick12345 · 06.12.2016, 17:45

а потому что там год во всех строчках меньше 2012

@hannu · 06.12.2016, 17:46 **[ТС]**

а как же быть с 2013, 2014. ?? 2015 и 2016

@unick12345 · 06.12.2016, 17:48

hannu, ввести в отчет :-)

@hannu · 06.12.2016, 17:51 **[ТС]**

Так там они есть. Просто почему то не собираются

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

@unick12345 · 06.12.2016, 17:55

hannu, последний раз. Лист СЗ - 16 строк, заполнены с 5 по 15 датами, где год меньше 2012.
Остальные данные с потолка возьму

@hannu 0 / 0 / 0 Регистрация: 08.04.2015 Сообщений: 135
	06.12.2016, 17:51 [ТС]	19
	Так там они есть. Просто почему то не собираются Добавлено через 1 минуту там фильтр стоит в сводной, если его убрать, как раз даты другие появятся. 0

@Казанский 15145 / 6418 / 1731 Регистрация: 24.09.2011 Сообщений: 9,999
	06.12.2016, 16:06	2
	hannu, похоже тут функцией СУММЕСЛИ можно обойтись, приложите файл-пример. 0

@hannu 0 / 0 / 0 Регистрация: 08.04.2015 Сообщений: 135
	06.12.2016, 16:25 [ТС]	3
	Вопрос в том, что там, обычная таблица завязана на сводную. Т.е получается динамический диапазон. Т.е когда то данных больше, когда то меньше. А при помощи функции нужно обозначать определенный диапазон 0

@hannu 0 / 0 / 0 Регистрация: 08.04.2015 Сообщений: 135
	06.12.2016, 17:02 [ТС]	6
	Не совсем, он из сводной таблицы(Лист С3) должен брать необходимые данные, суммировать их и вставлять в Т3. Макрос на это есть, но там много переменных и If. Мне челвоек сказал что можно при помощи While сделать, он будет смотреть в сводной таблицы данные, если 4 символа левых от даты = 2016 то суммировать все ячейки с кол-во обьекта и 2016 годом. Ну и также по 2015 , 2014, 2013 0

@hannu 0 / 0 / 0 Регистрация: 08.04.2015 Сообщений: 135
	06.12.2016, 17:15 [ТС]	8
	Точно, через case. Блин сейчас поковыряюсь, сделаю. А теперь другой момент, в случае если данные поменяются он их проссумирует с предыдущими. тут я так понимаю нужен цикл на обнуление или как? 0

@unick12345 1856 / 1178 / 190 Регистрация: 27.03.2009 Сообщений: 4,558
	06.12.2016, 17:16	9
	то есть? 0

@hannu 0 / 0 / 0 Регистрация: 08.04.2015 Сообщений: 135
	06.12.2016, 17:18 [ТС]	10
	Получается, я обновил сводную запустил макрос. Потом данные поменял запустил и он новые данные сложит с предыдущими, которые не очистились. Получается если на сводной таблице 3 раза запустить код он будет тупо складывать и складывать складывать и складывать. А нужно чтобы один раз выдал необходимое значение. 0

@hannu 0 / 0 / 0 Регистрация: 08.04.2015 Сообщений: 135
	06.12.2016, 17:27 [ТС]	12
	да я так понимаю он очищает диапазон ячеек. Но тогда нужно будет 2 кнопки делать. Одной кнопкой очищать а второй вносить. И второй небольшой вопрос, как сделать, чтобы он не по каждому столбцу а например по Case:a находил значения меньше 2012 и данные по колличеству квартир и по площади отражал также в таблице Т3 Добавлено через 3 минуты Т.е для следующих данных нужно будет составлять такой же код, но только смещать его верно? 0

toiai 3218 / 967 / 223 Регистрация: 29.05.2010 Сообщений: 2,086
	06.12.2016, 17:31	13
	А почему нельзя составить сводную таблицу на листе ТЗ напрямую с данных? 0

@unick12345 1856 / 1178 / 190 Регистрация: 27.03.2009 Сообщений: 4,558
	06.12.2016, 17:45	16
	а потому что там год во всех строчках меньше 2012 0

@hannu 0 / 0 / 0 Регистрация: 08.04.2015 Сообщений: 135
	06.12.2016, 17:46 [ТС]	17
	а как же быть с 2013, 2014. ?? 2015 и 2016 0

@unick12345 1856 / 1178 / 190 Регистрация: 27.03.2009 Сообщений: 4,558
	06.12.2016, 17:48	18
	hannu, ввести в отчет :-) 0

@unick12345 1856 / 1178 / 190 Регистрация: 27.03.2009 Сообщений: 4,558
	06.12.2016, 17:55	20
	hannu, последний раз. Лист СЗ - 16 строк, заполнены с 5 по 15 датами, где год меньше 2012. Остальные данные с потолка возьму 0