Страницы: 1
RSS
Как правильно использовать Range.Offset, если используемая ячейка объединена с другой?
 
Доброе время суток, коллеги.
Столкнулся в задаче Трансформация форм-карточек в нормализованную построчную ведомость Excel с несколько неожиданным для себя поведением Range.Offset для случая, когда ячейка, относительно которой задаём смещение к нужной ячейке, объединена с другой.
В примере, ячейка А10 для которой вызываем Offset объединена с ячейкой A11.
Если задать смещение -2, 0 (то есть на ячейку А8), то получаем результат -2;0 - как и хотелось.
Но если задать смещение -2, 1 (на ячейку B8), то получаем результат -2;2 - то есть на ячейку С8. Получается, что используется неявно смещение с учётом объединения.
И как в этом случае сослаться на B8? В задаче перешёл к абсолютным ссылкам, но всё же любопытно как в общем случае получать значение по требуемому смещению?
P. S. Про объединённые ячейки - зло, знаю - но стоит ли об этом писать? :)
 
Андрей, привет! А костыль в виде "ход конём" (двойное смещение) не подойдёт?
?range("A10").Offset(-2,0).Offset(0,1).Address
 
А10 обьеденена с В10
однако смещение от А10
-2,0 - получаем А8 (как ожидалось)
а
-2,1 = С8!!! (а не В8, как ожидалось)
и дальше все логично, согласно новой логики
-2,2 = Д8
-2,3 = Е8
....
интересная новость
если обединить А10:С10, смещение -2,1 от А10 получаем Д8, т.е. ячейку на 1 правее обьединенной
и еще неожиданно получил если с клавиатуры стрелками походить по ближайшим к А10:С10 ячейкам
то из А10  стрелка вверх - это не всегда А9, а А9, В9 или С9 в зависимости от того с какой ячейки я попал в А10
программный оффсет (-1,0) - всегда А9
Изменено: Ігор Гончаренко - 21.07.2019 12:26:19
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Вот такая штуковина:
?range("A10")(-1,2).address
 
В общем случае туды, сюды, обратно.  :D
Код
    With ActiveSheet.Range(baseAddress)
        val = .Offset(offRow, offCol).Offset(1 - .MergeArea.Rows.Count, 1 - .MergeArea.Columns.Count).Value
    End With
 
Цитата
RAN написал:
туды, сюды, обратно.
Видимо, да. Но тогда нет и разницы с абсолютной адресацией.
Спасибо, коллеги. Следующий будет про Find :)
 
Ещё: ?range("A10").Cells(1,2).offset(-2,0).address
 
Здравствуйте, коллеги! У "эффекта Андрея" есть еще один аспект:
Код
Sub test()
 Range("A1:D1").Merge
 Debug.Print Range("A1:D1").Offset(, 1).Address
 Debug.Print Range("A1:D1").Offset(1).Address
End Sub

Обе отладочных печати показывают адрес диапазона, состоящего из одной ячейки.
Впрочем, это явление (как обычно) уже обсуждали.
Что касается перехода по стрелкам, отмеченного Игорем в #3, то Excel помнит, откуда ты пришел, даже при проходе через "строй" объединенных ячеек.
Изменено: sokol92 - 21.07.2019 14:18:50
Владимир
 
Все вышеперечислённые использования свойства "Offset" будут подсчитывать хорошо или плохо в зависимости от значений "offRow" и "offCol" (+ offRow, -offRow, offCol = 0, offCol = 1 и т. д.) и размера диапазона ("одномерный", "многомерный"), а также положения ячейки, например для метода RAN, если baseAddress = "A10" и offCol = 0, возникнет ошибка, потому что "1 - .MergeArea.Columns.Count" дает -1, отсюда мы имеем "Range("A10").Offset(-2, 0).Offset(1 - 1, 1 - 2) => Range("A10").Offset(-2, -1)"
Код
Sub ShowOffsetValue()
    Dim baseAddress As String, offRow As Long, offCol As Long
    Dim adr1 As String, adr2 As String, adr3 As String
    
    baseAddress = "A10": offRow = -2: offCol = 0
    
    On Error Resume Next
    
    'Andrey VG
    adr1 = Range(baseAddress).Offset(offRow, offCol).Address(0, 0)
    If Err.Number <> 0 Then Err.Clear: adr1 = "Error"
    
    'Yuriy M
    adr2 = Range(baseAddress).Offset(offRow, 0).Offset(0, offCol).Address(0, 0)
    If Err.Number <> 0 Then Err.Clear: adr2 = "Error"
    
    'RAN
    With Range(baseAddress)
        adr3 = .Offset(offRow, offCol).Offset(1 - .MergeArea.Rows.Count, 1 - .MergeArea.Columns.Count).Address(0, 0)
    End With
    If Err.Number <> 0 Then Err.Clear: adr3 = "Error"
    
    On Error GoTo 0
    
    Debug.Print adr1
    Debug.Print adr2
    Debug.Print adr3
End Sub

Кажется, что единственный способ получить правильные показания это использовать "UnMerge":
Код
Sub ShowOffsetValue()
    Dim baseAddress As String, offRow As Long, offCol As Long
    Dim adr4 As String
    Dim adrs As String
    
    baseAddress = "A10": offRow = -2: offCol = 0
    
    On Error Resume Next
    
    adrs = Range(baseAddress).MergeArea.Address(0, 0)
    Range(adrs).UnMerge
    adr4 = Range(baseAddress).Offset(offRow, offCol).Address(0, 0)
    If Err.Number <> 0 Then Err.Clear: adr4 = "Error"
    Range(adrs).Merge
    
    On Error GoTo 0
    
    Debug.Print adr4
End Sub
 
Добрый день!
еще пара вариантов костылей
Код
val = Range(Application.ConvertFormula("r[" & offRow & "]c[" & offCol & "]", xlR1C1, xlA1, , Range(baseAddress))).Value

Код
val = ExecuteExcel4Macro("ABSREF(""r[" & offRow & "]c[" & offCol & "]"",Indirect(""" & baseAddress & """,1))")
 
Значит, наступила нам мозговая атака ...  :)
Код
Sub abc_zyx()
    Dim baseAddress As String, offRow As Long, offCol As Long
    Dim vval
    
    baseAddress = "A10": offRow = 2: offCol = 3
    vval = Evaluate("OFFSET(" & Range(baseAddress).Address(0, 0, xlA1) & "," & offRow & "," & offCol & ",1,1)")
End Sub
 
ocet p, а разве мои варианты из #4 и #7 дадут неверный результат или приведут к ошибке? Оба они в Immediate показывают нужный адрес: В8
 
Андрей VG,  Андрей привет,
Вариант аналогичный Unmerge - делать это на сервисном листе, и брать полученный адрес.
Вариант расчетный,
Код
Option Explicit

Public Sub ShowOffsetValue()
    Dim baseAddress As String, offRow As Long, offCol As Long, val
    Dim offRowCor As Long, offColCor As Long
    baseAddress = ActiveSheet.Range("A2")
    offRow = ActiveSheet.Range("B2")
    offCol = ActiveSheet.Range("C2")
    With ActiveSheet.Range(baseAddress)
        If .MergeCells Then
            If offRow > 0 And .MergeArea.Rows.Count > 1 Then
                offRowCor = offRow - .MergeArea.Rows.Count
            End If
            If offCol > 0 And .MergeArea.Columns.Count > 1 Then
                offColCor = offCol - .MergeArea.Columns.Count
            End If
        End If
        val = .Offset(offRow, offCol).Offset(offRowCor, offColCor).Value
    End With
    MsgBox "Смещение от ячейки " & baseAddress & vbLf _
        & "по строке " & offRow & vbLf _
        & "по столбцам " & offCol & vbLf _
        & "значение " & val
        
End Sub


Не внимательно прочел Что у RАNдрея.
Изменено: БМВ - 21.07.2019 23:14:36
По вопросам из тем форума, личку не читаю.
 
Коллеги, огромное спасибо за исследования и предложения. Правда, пошёл через абсолютную адресацию - unmerge не приемлемо.
Суть более общей задачи. Есть набор карточек ввода, сформированный как листы, созданные из одного листа-шаблона. Естественно, в этом случае, если взять какую-то ячейку за основу (предположим, что это некоторая ячейка, в которой текст стабильно начинается с определённого текста), то требуемые данные относительно этой ячейки будут всё время находится на одном и том же смещении по строкам/столбцам. Тогда для универсализации выгрузки данных с таких карточек и требуется код, который будет собирать данные с листов некоторой книги, используя вспомогательную таблицу Позиции, в которой описаны такие смещения и номера столбцов нормализованной таблицы вывода. Попробовал два варианта с абсолютной адресацией на листе карточки и загрузкой данных в массив. Но, в качестве ячейки-основы может выступить и ячейка, которая объединена с другой ячейкой. Заранее же это не известно.
По скорости оказалось одинаково. 500 листов ввода обрабатываются почти 2 секунды.
Если кому будет интересно, то тестовый образец источник №1 10.07.19 стволовые.xlsx (просто размножил лист в книге до 500 штук.
 
Цитата
Юрий М написал:
а разве мои варианты из #4 и #7 ... ?
Я подготовил файл с небольшим резюме, вы можете попробовать, но не знаю, хорошо ли я расшифровал всё для ваших методов или нет (пример применим до #2) ?
Страницы: 1
Наверх