我有以下代碼，我想用這三個公式覆寫單元格 S11 到 S13 中的公式：
='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y11
='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y12
=SUM(S12:S13)
Sub update_formulas()'
'
'
Dim i As Integer
Dim VarCellValue As String
Dim VarCellValue2 As String
Dim VarCellValue3 As String
Dim currwbk As Workbook
Set currwbk = ActiveWorkbook
' This contains a range of spreadsheet file names to open
For i = Range("A2").Value To Range("C2").Value
Application.DisplayAlerts = False
Workbooks.Open (Range("A3").Value & VarCellValue & "\" & VarCellValue & ".xlsm"), UpdateLinks:=3
Range("S11").Select
Sheets("SHEET 1").Select 'I want this to be dynamic but select the first tab in each sheet
'Here is where the code will go. Currently it selects from another sheet, but I'd like to pull it straight out of the module.
Range("S12:S14").PasteSpecial Paste:=xlPasteFormulas
Next i
End Sub
uj5u.com熱心網友回復：
- 直接寫公式就行了。
- 使用
Workbook變數。 - 確保完全符合任何
Range通話條件。例如，您使用Range("A3").Value & VarCellValue & "\" & VarCellValue & ".xlsm". 是不合格的Range("A3")，這意味著它的父作業表和父作業簿是未指定的，并且是隱含的ActiveSheet/ActiveWorkbook。
Dim wb As Workbook
Set wb = Workbooks.Open(Range("A3").Value & VarCellValue & "\" & VarCellValue & ".xlsm", UpdateLinks:=3) ' note the placement of parentheses
With wb.Worksheets(1)
.Range("S12").Formula = "='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y11"
.Range("S13").Formula = "='https://cloud.sharepoint.com/Shared Documents/[New York.xlsx]SHEETNAME(1)'!$Y12"
.Range("S14").Formula = "=SUM(S12:S13)"
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/522000.html
標籤：擅长vba