我正在subscript out of range error 9下面的 For 回圈的第二行。i = 7（行號），c1 = 59，c2=60（列號）。為什么我沒有讓下標超出范圍？
我應該使用1or2代替c1orc2嗎？當我使用1或2它沒有正確拾取我的列時。
Dim c1 As Variant
Dim c2 As Variant
Dim arr() as Variant
Set rw = ws.Rows(6)
lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
c1 = Application.Match("Col 59)", rw, 0)
c2 = Application.Match("Col 60", rw, 0)
If Not IsError(c1) And Not IsError(c2) Then 'found both column headers?
arr = ws.Range(rw.Cells(c1), rw.Cells(c2)).Resize(lastR).Value2
Else
MsgBox "One or both required column headers not found!"
End If
'Loop to find Empty and Non empty fields
For i = 7 To UBound(arr) 'Row 7 is the row the data starts
If (arr(i, c1) <> "" And arr(i, c2) = "") Or (arr(i, c2) <> "" And arr(i, c1) = "") Then
addToRange rngCopy, ws.Range("A" & i)
End If
Next i
uj5u.com熱心網友回復：
試試這個 - 更多代碼，但它使您無需對陣列與范圍坐標進行任何數學運算：
Sub Tester()
Const HEADER_ROW As Long = 6
Dim c1 As Variant, c2 As Variant, ws As Worksheet, rw As Range, lastR As Long
Dim arr1 As Variant, arr2 As Variant, rng1 As Range, rng2 As Range, v1, v2
Dim i As Long, rngCopy As Range
Set ws = ActiveSheet 'or some other specific sheet
Set rw = ws.Rows(HEADER_ROW)
lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
c1 = Application.Match("Col 59)", rw, 0)
c2 = Application.Match("Col 60", rw, 0)
If Not IsError(c1) And Not IsError(c2) Then 'found both column headers?
'define two ranges to pick up values from
Set rng1 = ws.Range(rw.Cells(c1), ws.Cells(lastR, c1))
Set rng2 = ws.Range(rw.Cells(c2), ws.Cells(lastR, c2))
'fill the arrays
arr1 = rng1.Value2
arr2 = rng2.Value2
Else
MsgBox "One or both required column headers not found!"
Exit Sub
End If
'Loop to find Empty and Non empty fields
For i = 2 To UBound(arr1, 1) 'i=1 would be the column headers
v1 = arr1(i, 1) 'read the two values to be compared
v2 = arr2(i, 1)
If (v1 <> "" And v2 = "") Or (v1 = "" And v2 <> "") Then
addToRange rngCopy, ws.Cells(rng1.Cells(i).Row, "A")
End If
Next i
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/530980.html
標籤：擅长vba
上一篇：在許多檔案上運行復雜的宏