我正在嘗試制作一個宏，將下面的公式添加到單元格中，然后將其拖到整個列中并在 H 列中復制相同的公式
我想在 F 和 H 列中輸入公式的資料
Range("F1").formula = "=IF(ISERROR(MATCH(E1,G:G,0)),"",E1)"
Range("H1").formula = "=IF(ISERROR(MATCH(E1,I:I,0)),"",E1)"
uj5u.com熱心網友回復：
你需要加倍引號，即
Range("F1").formula = "=IF(ISERROR(MATCH(E1,G:G,0)),"""",E1)"
Range("H1").formula = "=IF(ISERROR(MATCH(E1,I:I,0)),"""",E1)"
但是，如果要將公式輸入到多個單元格，最好使用 FormulaR1C1 屬性，例如
Range("F1:F50").FormulaR1C1 = "=IF(ISERROR(MATCH(RC[-1],C[1],0)),"""",RC[-1])"
Range("H1:H50").FormulaR1C1 = "=IF(ISERROR(MATCH(RC[-3],C[1],0)),"""",RC[-3])"
uj5u.com熱心網友回復：
寫公式
Option Explicit
Sub WriteFormulas()
Const fRow As Long = 1
Const ColumnsAddress As String = "F:F,H:H"
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim lrCell As Range
Set lrCell = ws.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious)
If lrCell Is Nothing Then Exit Sub
Dim rCount As Long: rCount = lrCell.Row - fRow 1
With Intersect(ws.Rows(fRow).Resize(rCount), ws.Range(ColumnsAddress))
Dim cAddress As String: cAddress = .Cells(1).Offset(, -1).Address(0)
Dim rAddress As String: rAddress = .Areas(1).Offset(, 1).Address(, 0)
.Formula = "=IF(ISERROR(MATCH(" _
& cAddress & "," & rAddress & ",0)),""""," & cAddress & ")"
MsgBox "Formulas written to range '" & .Address(0, 0) & "'.", _
vbInformation
End With
End Sub
uj5u.com熱心網友回復：
如果你的桌子下沒有其他東西，那么我會試試這個：
With ActiveSheet ' replace with a proper worksheet object
Intersect(.Range("F:F,H:H"), .UsedRange).FormulaR1C1 = _
"=IF(ISERROR(MATCH(RC5, C[1], 0)), """", RC5)" ' RC5 == $E1
End With
如果已經寫了一些公式，那么我們可以使用AutoFillorCopy方法：
Interval = "1:" & Range("E1").End(xlDown).Row ' autofill along data in E
With Range("F1")
.Formula = "=IF(ISERROR(MATCH($E1,G:G,0)),"""",$E1)"
.AutoFill .Rows(Interval)
.Copy Range("H1").Rows(Interval)
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/468245.html
下一篇：回傳Excel中的最新條目