Как указать адрес ячейки через переменную

@nnndaniil · Регистрация: 18.01.2011

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

Нужно в ("C5:C116") вместо С116 вставить переменну i, которая обозначает ячейку. Подскажите пожалуйста как это реализовать?

@Alex77755 · 15.06.2012, 01:37

Visual Basic

"C5:" & i

Подразумевается, что в i есть и буква

@nnndaniil · 15.06.2012, 02:05 **[ТС]**

я сам запутался =(
нужно что бы макрос работал только для тех ячеек, где есть значение. Я хотел следующим кодом определить последнюю ячейку, вывести адрес ячейки в переменную i. В коде указать i как границу для макроса.

Visual Basic

Dim lLastRow As Long
  Dim lLastCol As Long
     lLastRow = Cells(Rows.Count, 3).End(xlUp).Row
      lLastCol = Cells(3, Columns.Count).End(xlToLeft).Column

Подскажите пожалуйста, как это реализовать?

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

Visual Basic

1	Selection.AutoFill Destination:=Range("C5:" & lLastRow, lLastCol), Type:=xlFillDefault

вот тут выдает ошибку Method 'Range' of object' _Global' Failed

@ikki · 15.06.2012, 04:51

Visual Basic

1	Range("C5", Cells(lLastRow, lLastCol))

@skol13 · 15.06.2012, 08:35

Сообщение от nnndaniil

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

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

Visual Basic

Public Function Разрыв(Лист, КонтрСтолбец, Optional СоСтроки As Integer)
'функция просматривает ячейки КонтрСтолбца и возвращает номер
'строки в которой найдена пустая ячейка (своеобразный EOF).
'Можно "искать" разрыв, начиная СоСтроки, иначе с первой
  Dim Строка  As Integer
  With Worksheets(Лист)
    If СоСтроки = 0 Then
        Строка = 1
    Else
        Строка = СоСтроки
    End If
 
    Do While .Cells(Строка, КонтрСтолбец).Value <> ""
        Разрыв = Строка
        Строка = Строка + 1
    Loop
  End With
End Function

Понятно что таким же образом, модифицировав макрос, можно найти крайний столбец.
Их пересечение даст крайнюю ячейку

@Gibboustooth · 15.06.2012, 10:07

Range(Cells(i0, j0), Cells(i1,j1))
Где
i0 - номер строки левой верхней ячейки диапазона,
j0 - Номер столбца левой верхней ячейки диапазона,
i1 - номер строки правой нижней ячейки диапазона,
j1 - номер столбца правой нижней ячейки диапазона.

@Dmitrii · 15.06.2012, 10:26

Сообщение от skol13

...функция просматривает ячейки КонтрСтолбца и возвращает номер строки в которой найдена пустая ячейка (своеобразный EOF)...

А разве не проще для этой цели использовать метод SpecialCells?
Пример:

Visual Basic

1	Worksheets(1).Columns(1).Cells.SpecialCells(xlCellTypeBlanks).Item(1).Row

@skol13 · 15.06.2012, 10:47

Сообщение от Dmitrii

А разве не проще для этой цели использовать метод SpecialCells?

А если у вас данные идут не с первой строки?
Например несколько строк пропущенных, потом что-то типа шапки таблицы, в которой тоже не все ячейки заполнены?
Какую строку вернет ваш метод?
Правильно - первую строку в которой есть пустая ячейка в столбце поиска.
Но она будет далеко не последняя из строк с данными.

Добавлено через 12 минут
вообще конечно способов может быть туча и разных.
Который и как применить - все зависит от конкретных условий.
ТС дана куча ответов на его вопрос - что нибудь да пригодится

@sulfur · 15.06.2012, 10:55

Сообщение от nnndaniil

я сам запутался =(
нужно что бы макрос работал только для тех ячеек, где есть значение.

я всегда делал так:

Visual Basic

With ОбластьТипаRange
Dim obLast as Range, obCurr as Range
set obLast = .Find("*", searchdirection:=xlPrevious)
if Not obLast is nothing then
set obCurr = obLast
do
set obCurr = .Find("*", after:=obCurr)
Call ОбработкаНайденнойЯчейки(obCurr)
loop obCurr.Address = obLast.Address
endif
end with

Там возникают проблемы, только если по ходу обработки удаляешь или вставляешь ячейки. Не помню этих тонкостей, но если кому надо будет...

Не по теме:

как тут в коде VB вставлять отступы? тэг[INDENT] не работает =/

@Dmitrii · 15.06.2012, 10:58

skol13, моя реплика относится не к задаче автора темы, а к Вашей функции.

Сообщение от skol13

А если у вас данные идут не с первой строки?..

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

Visual Basic

1	Worksheets(1).Range(Cells(СоСтроки, 1), Cells(65536, 1)).Cells.SpecialCells(xlCellTypeBlanks).Item(1).Row

@skol13 · 15.06.2012, 11:02

Сообщение от sulfur

Не по теме:
как тут в коде VB вставлять отступы? тэг[INDENT] не работает =/

Не по теме:

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

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

Сообщение от Dmitrii

Да ведь ничего же не мешает задать вместо целого столбца нужный диапазон.

Ну так функция то была написана или может быть даже откуда то выдернута в непонятно каком забытом году

Багаж знаний то растет.
А сейчас просто по привычке тащу ее из проекта в проект

@sulfur · 15.06.2012, 11:04

хотя со SpecialCells проще, оказывается =)

@nnndaniil · 15.06.2012, 12:59 **[ТС]**

Сообщение от Gibboustooth

Range(Cells(i0, j0), Cells(i1,j1))

Решил сделать по Вашему примеру, но теперь мне выдает ошибку в строке Selection.AutoFill Destination.
Вот собственно весь код, мне нужно до последней ячейки, в которой есть данные, столбца С- вставить формулу.

Visual Basic

Range("C5").Select
    ActiveCell.FormulaR1C1 = "=++RC[2]/RC[1]"
    Range("C5").Select
    
    Selection.AutoFill Destination:=Range(Cells(i5, j3), Cells(i200, j3)), Type:=xlFillDefault
    
    Range("C5:C200").Select
    ActiveWindow.SmallScroll Down:=200

@Gibboustooth · 15.06.2012, 13:16

Сообщение от nnndaniil

Решил сделать по Вашему примеру, но теперь мне выдает ошибку в строке Selection.AutoFill Destination.

i и j перед числами не надо ставить. "0" и "1" в моих примерах - просто индексы. Надо было отформатировать:

Range(Cells(i₀, j₀), Cells(i₁,j₁))

Извинияюсь

@nnndaniil · 15.06.2012, 13:50 **[ТС]**

Сообщение от Gibboustooth

i и j перед числами не надо ставить. "0" и "1" в моих примерах - просто индексы. Надо было отформатировать:

Range(Cells(i₀, j₀), Cells(i₁,j₁))

Извинияюсь

Поставил, все заработало, спасибо!
Но я рассчитывал на другой результат, что в этом диапазоне, он будет искать последнюю заполненную ячейку и вставлять формулу только до последней заполненной ячейки, а не для всего диапазона.
Подскажите пожалуйста, как это можно реализовать?

@Gibboustooth · 15.06.2012, 14:25

Сообщение от nnndaniil

Но я рассчитывал на другой результат, что в этом диапазоне, он будет искать последнюю заполненную ячейку и вставлять формулу только до последней заполненной ячейки, а не для всего диапазона.
Подскажите пожалуйста, как это можно реализовать?

выбор последней заполненной ячейки на листе:

Visual Basic

1	Cells.Find("*", , , , xlByRows, xlPrevious)

Если нужно найти последнюю ячейку в определенном столбце:

Visual Basic

1	Columns(i).Find("*", , , , xlByRows, xlPrevious)

где i - номер столбца.

В вашем случае код будет выглядеть так:

Visual Basic

1	Selection.AutoFill Destination:=Range(Cells(5, 3), Columns(3).Find("*", , , , xlByRows, xlPrevious)), Type:=xlFillDefault

@nnndaniil · 15.06.2012, 14:42 **[ТС]**

Сообщение от Gibboustooth

В вашем случае код будет выглядеть так:

Visual Basic

1	Selection.AutoFill Destination:=Range(Cells(5, 3), Columns(3).Find("*", , , , xlByRows, xlPrevious)), Type:=xlFillDefault

Теперь на этой строчке

Visual Basic

1	Selection.AutoFill Destination:=

выдает ошибку, что метод AutoFill из класса Range завершен не верно.

Код сейчас такой:

Visual Basic

 Range("C5").Select
    ActiveCell.FormulaR1C1 = "=++RC[2]/RC[1]"
    Range("C5").Select
    Selection.AutoFill Destination:=Range(Cells(5, 3), Columns(3).Find("*", , , , xlByRows, xlPrevious)), Type:=xlFillDefault
Range("C5:C150").Select

В чем может быть ошибка?

@ikki · 15.06.2012, 15:18

такую ошибку может выдавать только в одном случае - если Selection совпадает с Destination
конкретно для Вашего случая - все ячейки в столбце C ниже C5 - пустые.

@nnndaniil · 15.06.2012, 15:52 **[ТС]**

Сообщение от ikki

такую ошибку может выдавать только в одном случае - если Selection совпадает с Destination
конкретно для Вашего случая - все ячейки в столбце C ниже C5 - пустые.

Точно!! Извините меня, за мою недальновидность.
У меня сначала макрос вставляет пустой столбце С, затем в него протягивается формула.
Можно сделать что бы формулу копировало до последней строки, в которой есть данные?
Например столбец B, строка 120 является последней, то что бы формулу в столбце С протянуло до 120 строки.

@ikki · 15.06.2012, 16:02

попробуйте так:

Visual Basic

1	[c5].resize([b:b].Find("*", , , , xlByRows, xlPrevious).row-4,1).formular1c1= "=RC[2]/RC[1]"

@nnndaniil 1 / 1 / 0 Регистрация: 18.01.2011 Сообщений: 15
	15.06.2012, 15:52 [ТС]	19
	Сообщение от ikki такую ошибку может выдавать только в одном случае - если Selection совпадает с Destination конкретно для Вашего случая - все ячейки в столбце C ниже C5 - пустые. Точно!! Извините меня, за мою недальновидность. У меня сначала макрос вставляет пустой столбце С, затем в него протягивается формула. Можно сделать что бы формулу копировало до последней строки, в которой есть данные? Например столбец B, строка 120 является последней, то что бы формулу в столбце С протянуло до 120 строки. 0

@Gibboustooth 735 / 203 / 11 Регистрация: 23.06.2011 Сообщений: 440
	15.06.2012, 10:07	6
	Range(Cells(i0, j0), Cells(i1,j1)) Где i0 - номер строки левой верхней ячейки диапазона, j0 - Номер столбца левой верхней ячейки диапазона, i1 - номер строки правой нижней ячейки диапазона, j1 - номер столбца правой нижней ячейки диапазона. 1

@skol13 369 / 78 / 6 Регистрация: 23.05.2012 Сообщений: 232
	15.06.2012, 10:47	8
	Сообщение от Dmitrii А разве не проще для этой цели использовать метод SpecialCells? А если у вас данные идут не с первой строки? Например несколько строк пропущенных, потом что-то типа шапки таблицы, в которой тоже не все ячейки заполнены? Какую строку вернет ваш метод? Правильно - первую строку в которой есть пустая ячейка в столбце поиска. Но она будет далеко не последняя из строк с данными. Добавлено через 12 минут вообще конечно способов может быть туча и разных. Который и как применить - все зависит от конкретных условий. ТС дана куча ответов на его вопрос - что нибудь да пригодится 1

@skol13 369 / 78 / 6 Регистрация: 23.05.2012 Сообщений: 232
	15.06.2012, 11:02	11
	Сообщение от sulfur Не по теме: как тут в коде VB вставлять отступы? тэг[INDENT] не работает =/ Не по теме: Я просто из макроса копирую код - все с отступами получается. Правда я табулятором отступы в макросе привык делать - с пробелами может и не выходит - не знаю Добавлено через 2 минуты Сообщение от Dmitrii Да ведь ничего же не мешает задать вместо целого столбца нужный диапазон. Ну так функция то была написана или может быть даже откуда то выдернута в непонятно каком забытом году Багаж знаний то растет. А сейчас просто по привычке тащу ее из проекта в проект 1

@sulfur 200 / 98 / 2 Регистрация: 24.09.2011 Сообщений: 261
	15.06.2012, 11:04	12
	хотя со SpecialCells проще, оказывается =) 1

@Gibboustooth 735 / 203 / 11 Регистрация: 23.06.2011 Сообщений: 440
	15.06.2012, 13:16	14
	Сообщение от nnndaniil Решил сделать по Вашему примеру, но теперь мне выдает ошибку в строке Selection.AutoFill Destination. i и j перед числами не надо ставить. "0" и "1" в моих примерах - просто индексы. Надо было отформатировать: Range(Cells(i₀, j₀), Cells(i₁,j₁)) Извинияюсь 1

@nnndaniil 1 / 1 / 0 Регистрация: 18.01.2011 Сообщений: 15
	15.06.2012, 13:50 [ТС]	15
	Сообщение от Gibboustooth i и j перед числами не надо ставить. "0" и "1" в моих примерах - просто индексы. Надо было отформатировать: Range(Cells(i₀, j₀), Cells(i₁,j₁)) Извинияюсь Поставил, все заработало, спасибо! Но я рассчитывал на другой результат, что в этом диапазоне, он будет искать последнюю заполненную ячейку и вставлять формулу только до последней заполненной ячейки, а не для всего диапазона. Подскажите пожалуйста, как это можно реализовать? 0

@ikki призрак 3263 / 891 / 119 Регистрация: 11.05.2012 Сообщений: 1,702 Записей в блоге: 2
	15.06.2012, 15:18	18
	такую ошибку может выдавать только в одном случае - если Selection совпадает с Destination конкретно для Вашего случая - все ячейки в столбце C ниже C5 - пустые. 2