Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Как макросом изменить границы диапазона - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как макросом изменить границы диапазона (Макросы/Sub)
Как макросом изменить границы диапазона
Glass4217 Дата: Воскресенье, 17.03.2019, 05:02 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Доброго времени суток.

Помогите решить проблему с именованным диапазоном.
Есть таблица C5:E32, она заполнена числами.
Но нижняя граница - которая сейчас проходит по строке 23 - бывает то выше, то ниже.
В зависимости от того где начинается та первая строка, в которой все три значения - 0.
Сейчас все три значения - 0 в строке 24 (C24,D24,E24) ... а значит нижняя граница диапазона должна проходить по строке 23.

Вопрос - как макросом в самом именованном диапазоне - изменить границы диапазона (в зависимости от того, где проходит нижняя его граница) ?

Вот я тут примерно в файле нарисовал - там где одни нули - там красный шрифт, а где числа которые должны входить в диапазон - там зеленым шрифтом подкрасил - чтобы нагляднее было.
К сообщению приложен файл: 657987.xls (31.0 Kb)
 
Ответить
СообщениеДоброго времени суток.

Помогите решить проблему с именованным диапазоном.
Есть таблица C5:E32, она заполнена числами.
Но нижняя граница - которая сейчас проходит по строке 23 - бывает то выше, то ниже.
В зависимости от того где начинается та первая строка, в которой все три значения - 0.
Сейчас все три значения - 0 в строке 24 (C24,D24,E24) ... а значит нижняя граница диапазона должна проходить по строке 23.

Вопрос - как макросом в самом именованном диапазоне - изменить границы диапазона (в зависимости от того, где проходит нижняя его граница) ?

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

Автор - Glass4217
Дата добавления - 17.03.2019 в 05:02
Anchoret Дата: Воскресенье, 17.03.2019, 07:29 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
[vba]
Код
Sub aaa()
Dim aa As Range, dt$, a&, b&, c#
Set aa = [C5].CurrentRegion: a = aa.Rows.Count
Do
  On Error Resume Next
  Do While aa(a, 1) = 0
    a = a - 1
    If Err Then a = a + 1: Exit Do
  Loop
  On Error GoTo 0
  If Application.Sum(aa.Rows(a + 1)) = 0 Then Set aa = aa.Rows("1:" & a): Exit Do
Loop
dt = "=" & ActiveSheet.Name & "!" & aa.Address
ThisWorkbook.Names.Item(1).RefersTo = dt
End Sub
[/vba]
 
Ответить
Сообщение[vba]
Код
Sub aaa()
Dim aa As Range, dt$, a&, b&, c#
Set aa = [C5].CurrentRegion: a = aa.Rows.Count
Do
  On Error Resume Next
  Do While aa(a, 1) = 0
    a = a - 1
    If Err Then a = a + 1: Exit Do
  Loop
  On Error GoTo 0
  If Application.Sum(aa.Rows(a + 1)) = 0 Then Set aa = aa.Rows("1:" & a): Exit Do
Loop
dt = "=" & ActiveSheet.Name & "!" & aa.Address
ThisWorkbook.Names.Item(1).RefersTo = dt
End Sub
[/vba]

Автор - Anchoret
Дата добавления - 17.03.2019 в 07:29
Glass4217 Дата: Воскресенье, 17.03.2019, 15:30 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Anchoret, что-то не работает.
Добавил столбец F, заполненный числами.
Теперь выдает ошибку.
Подсвечивает строку:
[vba]
Код
If Application.Sum(aa.Rows(a + 1)) = 0 Then
[/vba]

Почему макрос вообще реагирует на столбец F ?
Ведь я же спрашивал про столбцы C,D и E.
К сообщению приложен файл: 657987-2-.xls (43.0 Kb)
 
Ответить
СообщениеAnchoret, что-то не работает.
Добавил столбец F, заполненный числами.
Теперь выдает ошибку.
Подсвечивает строку:
[vba]
Код
If Application.Sum(aa.Rows(a + 1)) = 0 Then
[/vba]

Почему макрос вообще реагирует на столбец F ?
Ведь я же спрашивал про столбцы C,D и E.

Автор - Glass4217
Дата добавления - 17.03.2019 в 15:30
Nic70y Дата: Воскресенье, 17.03.2019, 15:52 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
Вопрос - как макросом
а чем формула не устраивает?
Код
=СМЕЩ(Лист3!$C$5:$E$5;;;ПОИСКПОЗ("000";Лист3!$C$5:$C$32&Лист3!$D$5:$D$32&Лист3!$E$5:$E$32;)-1)
К сообщению приложен файл: 1207946.xls (29.0 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение
Вопрос - как макросом
а чем формула не устраивает?
Код
=СМЕЩ(Лист3!$C$5:$E$5;;;ПОИСКПОЗ("000";Лист3!$C$5:$C$32&Лист3!$D$5:$D$32&Лист3!$E$5:$E$32;)-1)

Автор - Nic70y
Дата добавления - 17.03.2019 в 15:52
Glass4217 Дата: Воскресенье, 17.03.2019, 16:36 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Nic70y,потому что формула работает постоянно при каждом пересчете листа.
А мне нужно однократное срабатывание при одном нажатии кнопки макроса.

К тому же в вашем решении - пустует место значений, хотя там должны отображаться числа.
Сейчас же там - написано : {...}


Сообщение отредактировал Glass4217 - Воскресенье, 17.03.2019, 16:44
 
Ответить
СообщениеNic70y,потому что формула работает постоянно при каждом пересчете листа.
А мне нужно однократное срабатывание при одном нажатии кнопки макроса.

К тому же в вашем решении - пустует место значений, хотя там должны отображаться числа.
Сейчас же там - написано : {...}

Автор - Glass4217
Дата добавления - 17.03.2019 в 16:36
Nic70y Дата: Воскресенье, 17.03.2019, 16:48 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
К тому же в вашем решении - пустует место значений
не понял, о чем Вы.
К сообщению приложен файл: 6014142.gif (6.6 Kb)


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Воскресенье, 17.03.2019, 16:48
 
Ответить
Сообщение
К тому же в вашем решении - пустует место значений
не понял, о чем Вы.

Автор - Nic70y
Дата добавления - 17.03.2019 в 16:48
Glass4217 Дата: Воскресенье, 17.03.2019, 17:02 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Nic70y, я вот о чем:
 
Ответить
СообщениеNic70y, я вот о чем:

Автор - Glass4217
Дата добавления - 17.03.2019 в 17:02
Glass4217 Дата: Воскресенье, 17.03.2019, 17:23 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Anchoret, и еще вопрос - а этот код - какой именно именованный диапазон меняет (если таких диапазонов несколько) ?

В коде сейчас - нигде не отображается название именованного диапазона.
 
Ответить
СообщениеAnchoret, и еще вопрос - а этот код - какой именно именованный диапазон меняет (если таких диапазонов несколько) ?

В коде сейчас - нигде не отображается название именованного диапазона.

Автор - Glass4217
Дата добавления - 17.03.2019 в 17:23
Pelena Дата: Воскресенье, 17.03.2019, 17:39 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19161
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Ну, если формулой не нравится, то можно так попробовать
[vba]
Код
Sub aaa()
    Dim aa As Range, a&, i&
    Set aa = [C5].CurrentRegion: a = aa.Rows.Count
    For i = 1 To a
        If Application.Sum(aa.Cells(i, 1).Resize(, 3)) = 0 Then
            ThisWorkbook.Names("Диапазон1").RefersTo = "=" & ActiveSheet.Name & "!$C$5:$E$" & i + 3: Exit For
        End If
    Next i
End Sub
[/vba]


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНу, если формулой не нравится, то можно так попробовать
[vba]
Код
Sub aaa()
    Dim aa As Range, a&, i&
    Set aa = [C5].CurrentRegion: a = aa.Rows.Count
    For i = 1 To a
        If Application.Sum(aa.Cells(i, 1).Resize(, 3)) = 0 Then
            ThisWorkbook.Names("Диапазон1").RefersTo = "=" & ActiveSheet.Name & "!$C$5:$E$" & i + 3: Exit For
        End If
    Next i
End Sub
[/vba]

Автор - Pelena
Дата добавления - 17.03.2019 в 17:39
Nic70y Дата: Воскресенье, 17.03.2019, 17:59 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 8705
Репутация: 2260 ±
Замечаний: 0% ±

Excel 2010
я вот о чем
та это ерунда.
ну и поприколу:
[vba]
Код
Sub u__()
    u = Evaluate("=MATCH(""000"",C:C&D:D&E:E, 0)")
    ThisWorkbook.Names("Диапазон1").RefersTo = Sheets("Лист3").Range("C5:E" & u - 1)
End Sub
[/vba]


ЮMoney 41001841029809
 
Ответить
Сообщение
я вот о чем
та это ерунда.
ну и поприколу:
[vba]
Код
Sub u__()
    u = Evaluate("=MATCH(""000"",C:C&D:D&E:E, 0)")
    ThisWorkbook.Names("Диапазон1").RefersTo = Sheets("Лист3").Range("C5:E" & u - 1)
End Sub
[/vba]

Автор - Nic70y
Дата добавления - 17.03.2019 в 17:59
Anchoret Дата: Воскресенье, 17.03.2019, 19:36 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Glass4217, на будущее - файл-пример должен отражать реальную структуру данных. Если именованных диапазонов несколько ,то стоит указать и это.
[vba]
Код
Sub aaa()
Dim aa As Range, a&, dt$
a = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Set aa = Intersect(Rows("5:" & a), Columns("C:E")): a = aa.Rows.Count
Do
  On Error Resume Next
  Do While aa(a, 1) = 0
    a = a - 1
    If Err Then a = a + 1: Exit Do
  Loop
  On Error GoTo 0
  If Application.Sum(aa.Rows(a + 1)) = 0 Then Set aa = aa.Rows("1:" & a): Exit Do Else a = a - 1
Loop
On Error Resume Next: dt = ThisWorkbook.Names("Диапазон1").RefersTo
If Err Then
  ThisWorkbook.Names.Add "Диапазон1", "=" & ActiveSheet.Name & "!" & aa.Address
Else: ThisWorkbook.Names("Диапазон1").RefersTo = "=" & ActiveSheet.Name & "!" & aa.Address
End If: Err.Clear
End Sub
[/vba]
Если вдруг именованные диапазоны куда-то подевались, то макрос добавит.


Сообщение отредактировал Anchoret - Понедельник, 18.03.2019, 00:34
 
Ответить
СообщениеGlass4217, на будущее - файл-пример должен отражать реальную структуру данных. Если именованных диапазонов несколько ,то стоит указать и это.
[vba]
Код
Sub aaa()
Dim aa As Range, a&, dt$
a = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
Set aa = Intersect(Rows("5:" & a), Columns("C:E")): a = aa.Rows.Count
Do
  On Error Resume Next
  Do While aa(a, 1) = 0
    a = a - 1
    If Err Then a = a + 1: Exit Do
  Loop
  On Error GoTo 0
  If Application.Sum(aa.Rows(a + 1)) = 0 Then Set aa = aa.Rows("1:" & a): Exit Do Else a = a - 1
Loop
On Error Resume Next: dt = ThisWorkbook.Names("Диапазон1").RefersTo
If Err Then
  ThisWorkbook.Names.Add "Диапазон1", "=" & ActiveSheet.Name & "!" & aa.Address
Else: ThisWorkbook.Names("Диапазон1").RefersTo = "=" & ActiveSheet.Name & "!" & aa.Address
End If: Err.Clear
End Sub
[/vba]
Если вдруг именованные диапазоны куда-то подевались, то макрос добавит.

Автор - Anchoret
Дата добавления - 17.03.2019 в 19:36
Glass4217 Дата: Воскресенье, 17.03.2019, 19:49 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Anchoret, вроде бы все работает.

Но макрос почему-то захватывает одну строчку сверху, если в ней - что-то есть.
Например когда я размещаю строчкой выше - заголовок - то макрос включает в именованный диапазон и этот заголовок (то есть с четвертой строки его начинает, а не с пятой).
Хотя вроде бы - в макросе записано C5 - то есть обозначение верхней границы диапазона (пятая строка).

Почему так происходит и как это поменять ?
К сообщению приложен файл: 54453.xls (40.5 Kb)
 
Ответить
СообщениеAnchoret, вроде бы все работает.

Но макрос почему-то захватывает одну строчку сверху, если в ней - что-то есть.
Например когда я размещаю строчкой выше - заголовок - то макрос включает в именованный диапазон и этот заголовок (то есть с четвертой строки его начинает, а не с пятой).
Хотя вроде бы - в макросе записано C5 - то есть обозначение верхней границы диапазона (пятая строка).

Почему так происходит и как это поменять ?

Автор - Glass4217
Дата добавления - 17.03.2019 в 19:49
Anchoret Дата: Воскресенье, 17.03.2019, 19:55 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Glass4217, см.выше
 
Ответить
СообщениеGlass4217, см.выше

Автор - Anchoret
Дата добавления - 17.03.2019 в 19:55
Glass4217 Дата: Воскресенье, 17.03.2019, 20:03 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Anchoret, спасибо теперь все работает.

И вообще всем спасибо за ответы.
 
Ответить
СообщениеAnchoret, спасибо теперь все работает.

И вообще всем спасибо за ответы.

Автор - Glass4217
Дата добавления - 17.03.2019 в 20:03
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Как макросом изменить границы диапазона (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!