VBA将列表中的最后一行填写公式 [英] VBA to fill formula down till last row in column

问题描述

所以我正在开发一个项目，我试图让我的VBA代码将单元格M3中的公式一直下降到数据集的末尾。我使用列L作为我的基础来确定最后一个具有数据的单元格。我的公式是两个单元格的连接，它们之间有一个文本逗号。所以在excel中，我的公式是= G3&，& L3

So I'm working on a project and I'm trying to get my VBA code to draw down the formula that's in cell M3 all the way down to the end of the data set. I'm using column L as my base to determine what the last cell with data in it is. My formula is a concatenate of two cells with a text comma in-between them. So in excel my formula is =G3&","&L3

我想要excel绘制这个公式，所以在单元格M4中它将是= G4& & L4
单元格M5将是= G5&，& L5等等。

I want excel to draw down this formula so in cell M4 it would be =G4&","&L4 Cell M5 would be =G5&","&L5 and so on.

我的代码看起来像：

Range("$M$3").Formula = Range("G3") & (",") & Range("L3")

Dim Lastrow As Long

Application.ScreenUpdating = False

Lastrow = Range("L" & Rows.Count).End(xlUp).Row
Range("M4").FormulaR1C1 = Range("G4") & (",") & Range("L4")
Range("M4").AutoFill Destination:=Range("M4:M" & Lastrow)
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

我的输出只是简单地将单元格M3中的文本值向下拉到最后数据集。我搜索了几个小时，试图寻找一个修复，但似乎找不到一个尝试完成我要去的。

My output is simply pulling down the text values from cell M3 all the way down to the end of the data set. I've searched around for several hours trying to look for a fix, but can't seem to find one that is trying to accomplish what I'm going for.

推荐答案

实际上是一个班轮。不需要使用 .Autofill

It's a one liner actually. No need to use .Autofill

Range("M3:M" & LastRow).Formula = "=G3&"",""&L3"

这篇关于VBA将列表中的最后一行填写公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！