Доброе время суток, коллеги. Столкнулся в задаче Трансформация форм-карточек в нормализованную построчную ведомость Excel с несколько неожиданным для себя поведением Range.Offset для случая, когда ячейка, относительно которой задаём смещение к нужной ячейке, объединена с другой. В примере, ячейка А10 для которой вызываем Offset объединена с ячейкой A11. Если задать смещение -2, 0 (то есть на ячейку А8), то получаем результат -2;0 - как и хотелось. Но если задать смещение -2, 1 (на ячейку B8), то получаем результат -2;2 - то есть на ячейку С8. Получается, что используется неявно смещение с учётом объединения. И как в этом случае сослаться на B8? В задаче перешёл к абсолютным ссылкам, но всё же любопытно как в общем случае получать значение по требуемому смещению? P. S. Про объединённые ячейки - зло, знаю - но стоит ли об этом писать?
А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
Здравствуйте, коллеги! У "эффекта Андрея" есть еще один аспект:
Код
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 помнит, откуда ты пришел, даже при проходе через "строй" объединенных ячеек.
Все вышеперечислённые использования свойства "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
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
Андрей 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
Коллеги, огромное спасибо за исследования и предложения. Правда, пошёл через абсолютную адресацию - unmerge не приемлемо. Суть более общей задачи. Есть набор карточек ввода, сформированный как листы, созданные из одного листа-шаблона. Естественно, в этом случае, если взять какую-то ячейку за основу (предположим, что это некоторая ячейка, в которой текст стабильно начинается с определённого текста), то требуемые данные относительно этой ячейки будут всё время находится на одном и том же смещении по строкам/столбцам. Тогда для универсализации выгрузки данных с таких карточек и требуется код, который будет собирать данные с листов некоторой книги, используя вспомогательную таблицу Позиции, в которой описаны такие смещения и номера столбцов нормализованной таблицы вывода. Попробовал два варианта с абсолютной адресацией на листе карточки и загрузкой данных в массив. Но, в качестве ячейки-основы может выступить и ячейка, которая объединена с другой ячейкой. Заранее же это не известно. По скорости оказалось одинаково. 500 листов ввода обрабатываются почти 2 секунды. Если кому будет интересно, то тестовый образец источник №1 10.07.19 стволовые.xlsx (просто размножил лист в книге до 500 штук.
Юрий М написал: а разве мои варианты из #4 и #7 ... ?
Я подготовил файл с небольшим резюме, вы можете попробовать, но не знаю, хорошо ли я расшифровал всё для ваших методов или нет (пример применим до #2) ?