문제 설명
ActiveCell..Offset(‑1,0)=을 사용하는 If/And/Then/Else 문의 VBA 문제= (VBA Issue with If/And/Then/Else statement using ActiveCell..Offset(‑1,0)=)
C열의 셀을 선택하고 그 위의 두 셀이 0인지 확인하고 싶습니다. 두 셀이 0이면 ActiveCell을 1로 지정하고 싶습니다. 그렇지 않으면 ActiveCell을 0으로 지정하고 싶습니다. 그런 다음 초기 셀에서 3 아래에 있는 셀을 선택하고 이 과정을 반복하고 싶습니다. 나는 이것을 773번 하고 싶다. 내가 겪고있는 문제는 IF/AND 섹션에 있으며 1을 선택해야 할 때에도 항상 0을 선택합니다. 내가 뭘 잘못했는지 알 수 있습니다. CSV에서 변환된 Excel 파일에서 작업 중입니다.
Range("C4").Select
For i = 1 To 773
If ActiveCell.Offset(‑1, 0).Value = “0” And ActiveCell.Offset(‑2, 0).Value = “0” Then
ActiveCell = "1" Else
ActiveCell = "0"
ActiveCell.Offset(3, 0).Select
Next i
</code></pre>
참조 솔루션
방법 1:
It is best to avoid Activate and Select. Also, you can step the increment without having to add to the selection.
Sub test()
Dim sht As Workbook
Set sht = ActiveWorkbook 'or actual sheet name
For i = 4 To 773 Step 3
If sht.Cells(i ‑ 1, 4).Value = 0 And sht.Cells(i ‑ 2, 4).Value = 0 Then
sht.Cells(i, 4) = 1
Else
sht.Cells(i, 4) = 0
End If
Next i
End Sub
방법 2:
Testing a Range
- The unexpected result (zeros) is due to the use of "0" which cannot be found.
</ul>
Option Explicit
' Not recommended. Note how slow it is compared to the other solutions.
' The trick is in avoiding using "Select" and any 'flavor' of "Active".
' Runtime: 2100ms (over 2 seconds)
Sub testQuickFix()
Range("C4").Select
Dim i As Long
For i = 1 To 773
' This has to be one line: note the line separators ('_').
If ActiveCell.Offset(‑1, 0).Value = 0 _
And ActiveCell.Offset(‑2, 0).Value = 0 Then _
ActiveCell = 1 Else _
ActiveCell = 0
ActiveCell.Offset(3, 0).Select
Next i
End Sub
' Highly recommended, but maybe too advanced (You should learn about arrays).
' Note that this is only useful if the data are values, not formulas, because
' the whole range is overwritten.
' Runtime: 5ms
Sub testArray()
Dim rg As Range: Set rg = Range("C2:C2320")
Dim Data As Variant: Data = rg.Value
Dim i As Long
For i = 1 To UBound(Data, 1) Step 3
If Data(i, 1) = 0 And Data(i + 1, 1) = 0 Then
Data(i + 2, 1) = 1
Else
Data(i + 2, 1) = 0
End If
Next i
rg.Value = Data
End Sub
' Recommended.
' Runtime: 80ms
Sub testIfThenElseEndIf()
Dim rg As Range: Set rg = Range("C2:C4")
Dim i As Long
For i = 1 To 773
' This is considered more readable and is mostly used on SO.
If rg(1).Value = 0 And rg(2).Value = 0 Then
rg(3).Value = 1
Else
rg(3).Value = 0
End If
' It is short for:
' If rg.Cells(1).Value = 0 And rg.Cells(2).Value = 0 Then
' rg.Cells(3).Value = 1
' Else
' rg.Cells(3).Value = 0
' End If
' which I actually prefer.
Set rg = rg.Offset(3)
Next i
End Sub
' Not recommended.
' Runtime: 80ms
Sub testIfThenElse()
Dim rg As Range: Set rg = Range("C2:C4")
Dim i As Long
For i = 1 To 773
' This is valid, but rarely seen on SO.
' Note that this is one line.
'If rg(1).Value = 0 And rg(2).Value = 0 Then rg(3).Value = 1 Else rg(3).Value = 0
' Note that this is also one line, but uses a line separator.
If rg(1).Value = 0 And rg(2).Value = 0 Then rg(3).Value = 1 _
Else rg(3).Value = 0
Set rg = rg.Offset(3)
Next i
End Sub
' (A little less) recommended.
' Runtime: 80ms
Sub testIIF()
Dim rg As Range: Set rg = Range("C2:C4")
Dim i As Long
For i = 1 To 773
' This is another way.
rg(3).Value = IIf(rg(1).Value = 0 And rg(2).Value = 0, 1, 0)
Set rg = rg.Offset(3)
Next i
End Sub
(by Pat Berrigan、Darrell H、VBasic2008)
참조 문서