我想找到周數并將格式應用于該單元格。周數是使用中的 weeknum 公式自動生成的Sheets("Program").Range("N3")。
我有5張。在第一張紙Overview中，資料在第 8 行，并且格式有效。在表 2 到 5 中，資料位于第 4 行。因此，我選擇了所有 4 張表并使用相同的邏輯。但是格式不適用于作業表BBB, CCC, DDD. 我預期的影像輸出。
我的程式沒有顯示任何錯誤并且無法正常作業。誰能幫我？
Sub FindandFormat()
Dim ws1, ws2, ws3 As Worksheet
Dim CW As String
Dim rng2, rng1 As Range
Set ws1 = ThisWorkbook.Worksheets("Overview")
Set ws2 = ThisWorkbook.Worksheets("AAA")
' "Format to show the actual week in every sheet"
CW = "W" & ThisWorkbook.Worksheets("Program").Range("N3").Value - 1
With ws1
Set rng1 = .Rows("8:8").Find(What:=CW, LookIn:=xlValues)
With rng1.Interior
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
End With
End With
With ws2
Set rng2 = .Rows("4:4").Find(What:=CW, LookIn:=xlValues)
ThisWorkbook.Sheets(Array("AAA", "BBB", "CCC", "DDD")).Select
With rng2.Interior
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
End With
End With
End Sub
uj5u.com熱心網友回復：
請注意，如果您宣告Dim ws1, ws2, ws3 As Worksheetonlyws3是 type Worksheet，而其他都是 type Variant。在 VBA 中，您需要為每個變數指定一個型別，或者默認情況下它們是Variant：Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet. 你的也一樣Range。
此代碼中的問題是選擇這些作業表只會選擇它們。
With ws2
Set rng2 = .Rows("4:4").Find(What:=CW, LookIn:=xlValues)
ThisWorkbook.Sheets(Array("AAA", "BBB", "CCC", "DDD")).Select
With rng2.Interior
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
End With
End With
您將格式With rng2.Interior和rng2參考應用于明確.Rows("4:4")…地With ws2僅適用于ws2! ws2無論選擇哪些作業表，它都適用。
相反，您需要遍歷作業表并將格式應用于每個作業表：
Dim WorksheetNames As Variant ' define the worksheet names you want the format to apply to
WorksheetNames = Array("AAA", "BBB", "CCC", "DDD")
Dim WorksheetName As Variant
For Each WorksheetName In WorksheetNames ' loop through all worksheet names in the array
Dim FoundAt As Range ' try to find CW in each worksheet
Set FoundAt = ThisWorkbook.Worksheets(WorksheetName).Rows("4:4").Find(What:=CW, LookIn:=xlValues)
' check if CW was found otherwise show error message
If Not FoundAt Is Nothing Then
With FoundAt.Interior ' perform format change
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
End With
Else
MsgBox """" & CW & """ was not found.", vbOKonly
End If
Next WorksheetName
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/513824.html