我是 VBA 初學者。我得到一個“運行時錯誤 91 物件變數或未設定塊變數”試圖運行以下代碼。這是我正在嘗試做的事情：
- 選擇我的作業表中的所有資料
- 將選擇命名為 AllData
- 遍歷此范圍，在 D 列中找到“X”的任何位置，將 W 列的值更改為“否”。
該錯誤指的是我嘗試將No_Of_Rows設定為我的范圍的行數的第 9 行。顯然我應該事先“設定”我的物件..？但我不知道我做錯了什么......
在此先感謝您的幫助。
Sub ChngColW()
Dim AllData As Range
Dim No_Of_Rows As Integer
Dim i As Long
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "AllData"
No_Of_Rows = AllData.Rows.Count
For i = 1 To No_Of_Rows
If AllData("D" & i).Value = "X" Then
AllData("W" & i).Value = "No"
End If
Next i End Sub
uj5u.com熱心網友回復：
快速修復
Sub ChangeColumnW()
' Reference the worksheet.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Reference the range.
Dim AllData As Range
Set AllData = ws.Range("A1", ws.Range("A1").End(xlToRight).End(xlDown))
' Name the range (useless).
'AllData.Name = "AllData"
' Now you could refer to it with 'Range("AllData")' but what's the benefit?
Dim i As Long
For i = 2 To AllData.Rows.Count
If AllData(i, "D").Value = "X" Then
AllData(i, "W").Value = "No"
End If
Next i
End Sub
替代
- 只是在列中“查看”而不關心范圍，包括一些改進。
Sub ChangeColumnNoConstants()
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
Dim r As Long
Dim fCount As Long
For r = 2 To lRow
If StrComp(CStr(ws.Cells(r, "D").Value), "X", vbTextCompare) = 0 Then
ws.Cells(r, "W").Value = "No"
fCount = fCount 1
End If
Next r
MsgBox "Found 'X' in " & fCount & " cells.", vbInformation
End Sub
Sub ChangeColumnUsingConstants()
Const sCol As String = "D"
Const sCrit As String = "X"
Const dCol As String = "W"
Const dCrit As String = "No"
Const fRow As Long = 2
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, sCol).End(xlUp).Row
Dim r As Long
Dim fCount As Long
For r = fRow To lRow
If StrComp(CStr(ws.Cells(r, sCol).Value), sCrit, vbTextCompare) = 0 Then
ws.Cells(r, dCol).Value = dCrit
fCount = fCount 1
End If
Next r
MsgBox "Found '" & sCrit & "' in " & fCount & " cells.", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/487659.html