尋求幫助以實作使用包含“住宿和交通”的所有單元格搜索一系列單元格 E9:E 并將與它們相鄰的單元格的值更改為 0. ，我無法獲得任何具有類似主題的在線內容，我'我不太擅長 VBA 編碼，但我能夠理解代碼將在結果中提供什么。
我有一個帶有以下代碼的 Commandbutton1：
Sub CommandButton1_click()
Dim blanks As Excel.Range
Set blanks = Range("F9:F" & Cells(Rows.Count, 5).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
blanks.Value = blanks.Offset(0, -1).Value
End Sub
此外，我有一個命令按鈕，它將僅選擇非空白單元格。我需要上述結果，因為如果下面的代碼從列 E:F 中選擇非空白單元格，它不會選擇與包含“住宿和交通”的單元格相鄰的單元格，因為它們是空白單元格，并且會回傳錯誤“運行時錯誤‘1004’此操作不適用于多項選擇”。
下面的代碼與 [Go to Special => Constants] 的作用相同
Sub SelectNonBlankCells()
Dim rng As Range
Dim OutRng As Range
Dim InputRng As Range
Dim xTitle As String
On Error Resume Next
xTitle = Application.ActiveWindow.RangeSelection.Address
Set InputRng = Range("E8:F500")
ActiveWindow.ScrollRow = 1
For Each rng In InputRng
If Not rng.Value = "" Then
If OutRng Is Nothing Then
Set OutRng = rng
Else
Set OutRng = Application.Union(OutRng, rng)
End If
End If
Next
If Not (OutRng Is Nothing) Then
OutRng.Select
End If
End Sub
uj5u.com熱心網友回復：
如果您的目標是編輯與某些單元格相鄰的單元格，也許您可??以嘗試另一種方法。以下代碼基于該Range.Find函式的幫助檔案中的示例：
Sub DoSomething()
Dim sh As Worksheet
Set sh = ActiveSheet
Dim checkRange As Range
Set checkRange = sh.Range("E8:F500") ' your intended range to search
Dim foundRange As Range
Set foundRange = checkRange.Find("Accommodation & Transportation")
Dim firstAddr As String
If Not foundRange Is Nothing Then
firstAddr = foundRange.Address
Do
' use foundRange to access adjacent cells with foundRange.Offset(row, col)
'
'
foundRange.Offset(0, 1) = "all good"
Set foundRange = checkRange.FindNext(foundRange)
Loop While Not foundRange Is Nothing And foundRange.Address <> firstAddr
End If
End Sub
或者更好的是，您可以添加一些引數以使其更具可重用性：
Sub Main()
DoSomething "Accommodation & Transportation", ActiveSheet.Range("E8:F500")
End Sub
Sub DoSomething(ByVal findWhat As String, ByVal searchWhere As Range)
Dim foundRange As Range
Set foundRange = searchWhere.Find(findWhat)
Dim firstAddr As String
If Not foundRange Is Nothing Then
firstAddr = foundRange.Address
Do
' use foundRange to access adjacent cells with foundRange.Offset(row, col)
'
'
foundRange.Offset(0, 1) = "all good"
Set foundRange = searchWhere.FindNext(foundRange)
Loop While Not foundRange Is Nothing And foundRange.Address <> firstAddr
End If
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/377341.html