时间戳

首页 > 解决方案 > 自动填充公式到数据 VBA 的末尾

excel - 自动填充公式到数据 VBA 的末尾

问题描述

当我查找 VBA 以将公式自动填充到工作簿中所有工作表的数据末尾时，我发现了这个：

Sub FillFormula()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("Q2").Formula = "=CONCATENATE(A2,B2,C2)"
ws.Range("Q2", "Q" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Next
End Sub

它工作正常，但不适用于我的公式。我需要它来使用这个公式：

=CONCATENATE(A2,CHAR(10),"Serving:"," ", O2,CHAR(10),"Contact:"," ", B2," ", C2,CHAR(10), F2, ",", " ", G2, " ",H2,CHAR(10), I2,",", " ", J2, " ",K2,CHAR(10),"Phone:"," ",L2,CHAR(10), "Fax:"," ",M2,CHAR(10), "Email:", " ", D2,CHAR(10),"Website:", " ", N2,CHAR(10),P2,CHAR(10)," ",CHAR(10))

但是，由于公式中有许多 "，因此无法正常工作。

有什么建议么？

谢谢！

标签： excelvbaconcatenationformulaautofill

解决方案

就像 Marcucciboy2 所说，使用 VBA 时，您必须在公式中加倍引号：

Sub FillFormula()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Range("Q2").Formula = "=CONCATENATE(A2,CHAR(10),""Serving:"","" "", O2,CHAR(10),""Contact:"","" "", B2,"" "", C2,CHAR(10), F2, "","", "" "", G2, "" "",H2,CHAR(10), I2,"","", "" "", J2, "" "",K2,CHAR(10),""Phone:"","" "",L2,CHAR(10), ""Fax:"","" "",M2,CHAR(10), ""Email:"", "" "", D2,CHAR(10),""Website:"", "" "", N2,CHAR(10),P2,CHAR(10),"" "",CHAR(10))"
ws.Range("Q2", "Q" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Next
End Sub

推荐阅读