Formula vba Excel

Question

在下面的代码中，所有行的公式是固定的，并且考虑g3，而每一行必须改变g3。例如，在第4行中，公式中应记录G4。

如何根据公式中的每一行插入G

sub n()
IFor n = 3 To 916
If Not IsEmpty(Worksheets("Sheet1").cells(n, "A")) Then
Worksheets("Sheet1").cells(n, "A").Formula = "=diff(shamsi(),g3)/30"
End If
Next

MsgBox "Done"
End Sub

Answer 1

写公式

快速修复

• 在这种特殊情况下，最简单的方法是使用r1c1风格的引用，正如Skin在注释中建议的那样:

  Worksheets("Sheet1").Cells(n, "A").FormulaR1C1 = "=diff(shamsi(),RC[6])/30"
  

  其中R表示同一行，而C[6]表示相对于每个源单元格向右6列。

改善

Option Explicit
Sub WriteFormulas()

' Reference the range.

Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
Dim rg As Range: Set rg = ws.Range("A3:A916")

' Combine the critical cells into a unioned range.

Dim urg As Range, cel As Range

For Each cel In rg.Cells
If Not IsEmpty(cel.Value) Then
If urg Is Nothing Then Set urg = cel Else Set urg = Union(urg, cel)
End If
Next cel

If urg Is Nothing Then Exit Sub

' Write the formula (in one go).

Dim FirstAddress As String
FirstAddress = urg.Cells(1).EntireRow.Columns("G").Address(0, 0)

urg.Formula = "=diff(shamsi()," & FirstAddress & ")/30"
' Or as Skin suggested in the comments:
'urg.FormulaR1C1 = "=diff(shamsi(),RC[6])/30"

' Inform.

MsgBox "Done"
End Sub