Вопрос:
Я новичок в написании макросов в VBA, но я прилагаю все усилия, чтобы как можно быстрее изучить как можно больше. Во всяком случае, задача, которую я пытаюсь выполнить, довольно проста, но у меня возникают проблемы с возможностью сделать это.
То, что я хочу сделать, – вставить формулу во все строки в последнем пустом столбце электронной таблицы.
Таким образом, в этом случае в выделенные ячейки, показанные на скриншоте: Пример:
Тем не менее, я не хочу полагаться на ввод (“K1: K” и lastrow), то, что я хочу сделать, это создать ссылку на последний столбец и последнюю строку.
До сих пор мне удалось вставить формулу во весь столбец, используя следующую команду:
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column fvlookup = «=vlookup(@1,@2,@3,False)» fvlookup = Replace (fvlookup, «@1», rng.Address) fvlookup = Replace (fvlookup, «@2″, [LookupFile.csv]LookupFile!$B:$1») fvlookup = Replace (fvlookup, «@3», «5») .Columns(lastcol).Formula = fvlookup
Но позже в процессе, над которым я работаю, я хочу удалить все “# N/A” и поместить их в другую вкладку с именем “JE”, потому что некоторые элементы в таблице фактически не имеют значения в таблице, на которую я смотрю, и нужны JE, созданные для них. Итак, у меня было бы много ненужных строк на новой вкладке, если бы я пошел по этому маршруту.
Во всяком случае, я пробовал это:
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0,1).Column fvlookup = «=VLOOKUP(@1,@2,@3,False)» fvlookup = Replace(fvlookup, «@1», rng.Address) fvlookup = Replace(fvlookup, «@2», «[LookupFile.csv]LookupFile!$B:$I») fvlookup = Replace(fvlookup, «@3», «5») With .Columns(lastcol) lrow = .range(«A» & .Rows.Count).End(xlUp).Row .Rows(lrow).Formula = fvlookup End With
Но он только помещает формулу в “K1” (ссылаясь на прикрепленное изображение)
Я также попытался выбрать это значение после вставки формулы и автоматического заполнения (я знаю, что рекомендуется избегать выбора диапазонов с помощью vba, но я все еще хотел попробовать в крайнем случае), но он говорит, что это запрещено.
Я также пытался использовать:
.range(lastcol & .range(«A» & .Rows.Count).End(xlUp).Rows).Formula = fvlookup
Но это дает мне ошибку времени выполнения “1004”: определяемая приложением или объектная ошибка. Я попытался создать переменную с именем ‘lrange’ и установить ее так же, как я установил lastcol, но не повезло (он возвращает то же сообщение об ошибке, что и выше). Я также пробовал редактировать lastcol для lastcol.Column или.Columns(lastcol) в приведенной выше строке, но все равно ничего.
Я попытался изучить похожие вопросы, и во всех рекомендациях рекомендуется определить две переменные (последний и последний столбец) и вставить формулу в этот диапазон, но это, похоже, не работает с тем, что я использую.
Я чувствую, что процесс, который пытается выполнить VBA, ограничивается только возможностью вставлять данные во весь столбец или его часть с тем, как я устанавливал макрос вверх, так как он не смог найти последний столбец и вставить все путь к последнему ряду с тем, как я его настраивал. Поэтому я приношу свои извинения, если это так, или если бы я написал это по-другому.
Любые рекомендации или указания, которые любой может предоставить по этой теме, будут высоко оценены.
Спасибо за ваше время!
Лучший ответ:
Вместо того, чтобы зацикливаться в конце, я просто использовал .FillDown
Cells(2, lastcol).FormulaR1C1 = fvlookup Range(Cells(2, lastcol), Cells(lrow, lastcol)).FillDown Ответ №1
Как насчет замены вашего кода на что-то вроде этого:
Sub foo() lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column fvlookup = «=VLOOKUP(@1,@2,@3,False)» fvlookup = Replace(fvlookup, «@1», Rng.Address) fvlookup = Replace(fvlookup, «@2», «[LookupFile.csv]LookupFile!$B:$I») fvlookup = Replace(fvlookup, «@3», «5») lrow = .Range(«A» & .Rows.Count).End(xlUp).Row For i = 2 To lrow .Cells(i, lastcol).Formula = fvlookup Next i End Sub
Это будет проходить от строки 2 до последней и добавить формулу в lastcol.
Ответ №2
Пока таблица не имеет полностью пустой строки или столбца (как это не должно) Range(«A1»).CurrentRegion определяет размер таблицы. Затем .Rows.Count и .Columns.Count дает вам информацию, необходимую вам, чтобы заполнить соседний столбец формулой.
Вы также можете заполнить ячейки столбца формулами за один раз, используя FormulaR1C1 – при условии, что вы будете осторожны с привязкой к FormulaR1C1. Вот пример:
Dim tableRange As Range Dim x As Integer, y As Integer Set tableRange = Range(«A1»).CurrentRegion x = tableRange.Rows.Count y = tableRange.Columns.Count ‘fill the adjacent column with a SUM function summing across each row Range(Cells(2, y + 1), Cells(x, y + 1)).FormulaR1C1 = «=SUM(RC[-» & y & «]:RC[-1])»
(Вы также можете использовать y и 1, чтобы дать этому новому столбцу заголовок.)
Если вам нужно реплицировать VLOOKUP на определенный (абсолютный) адрес, например $B:$I тогда я бы назвал этот диапазон и ввел имя в формулу.
Примером такого является:
.FormulaR1C1 = «=VLOOKUP(RC[-1],mylookup,2,FALSE)»
где mylookup – это имя диапазона, а RC[-1] ссылается на ячейку сразу слева от каждой формулы.
