英文:
How to move row based on a value to another sheet?
问题
我想根据cStatus中的值查找和移动一行。
- 如果值为"Done"，则移至Sheet4。
- 如果值为"On-going"，则移至Sheet2。
- 如果值为""，则保留在当前工作表。
我拥有的代码不会生成任何错误消息，但也不会执行代码。
请注意，该代码不是我自己的。
Sub MoveBasedOnValue2()
Dim cStatus As Range, wsDest As Worksheet, Keywords As Range
Dim Table1 As Range, Table2 As Range
Set cStatus = Sheet1.Range("N2")
If Not cStatus Is Nothing Then
'Do While Len(cStatus.Value) > 0
Select Case LCase(cStatus.Value)
Case "Done": Set wsDest = Sheet4
Case "On-going": Set wsDest = Sheet2
Case Else: Set wsDest = Nothing
End Select
If Not wsDest Is Nothing Then
cStatus.EntireRow.Range("A2:N2").Cut _
Destination:=wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
End If
If cStatus Is Nothing Then
Set cStatus = Sheet1.Range("N1:N1000").Find(what:="Done, On-going")
Do While Len(cStatus.Value) > 0
Select Case LCase(cStatus.Value)
Case "done": Set wsDest = Sheet4
Case "on-going": Set wsDest = Sheet2
Case Else: Set wsDest = Nothing
End Select
If Not wsDest Is Nothing Then
cStatus.EntireRow.Cut _
Destination:=wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
Loop
End If
End Sub
这是代码的翻译部分，如有需要，请告诉我是否需要其他帮助。
英文:
I want to find & move a row based on the value in cStatus.
- If the value is "Done", then move to Sheet4.
- If the value is "On-going", then move to Sheet2.
- If the value is "", then stay to the current sheet.
The code I have won't come up with any error messages, but it also won't execute the code.
Note that the code isn't my own.
Sub MoveBasedOnValue2()
Dim cStatus As Range, wsDest As Worksheet, Keywords As Range
Dim Table1 As Range, Table2 As Range
Set cStatus = Sheet1.Range("N2")
If Not cStatus Is Nothing Then
'Do While Len(cStatus.Value) > 0
Select Case LCase(cStatus.Value)
Case "Done": Set wsDest = Sheet4
Case "On-going": Set wsDest = Sheet2
Case Else: Set wsDest = Nothing
End Select
If Not wsDest Is Nothing Then
cStatus.EntireRow.Range("A2:N2").Cut _
Destination:=wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
End If
If cStatus Is Nothing Then
Set cStatus = Sheet1.Range("N1:N1000").Find(what:="Done, On-going")
Do While Len(cStatus.Value) > 0
Select Case LCase(cStatus.Value)
Case "done": Set wsDest = Sheet4
Case "on-going": Set wsDest = Sheet2
Case Else: Set wsDest = Nothing
End Select
If Not wsDest Is Nothing Then
cStatus.EntireRow.Cut _
Destination:=wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If
Loop
End If
End Sub
答案1
得分: 0
如果要翻译的部分如下：
If you are not moving many rows then simply scan up the sheet checking the relevant cell value.
Option Explicit
Sub MoveBasedOnValue2()
Const COL_STATUS = "N"
Dim wsSrc As Worksheet, wsDest As Worksheet
Dim lastrow As Long, i As Long, n As Long
Dim t0 As Single: t0 = Timer
Set wsSrc = Sheet1
With wsSrc
lastrow = .Cells(.Rows.Count, COL_STATUS).End(xlUp).Row
For i = lastrow To 1 Step -1
Select Case LCase(Trim(.Cells(i, COL_STATUS)))
Case "done": Set wsDest = Sheet4
Case "on-going": Set wsDest = Sheet2
Case Else: Set wsDest = Nothing
End Select
If Not wsDest Is Nothing Then
.Rows(i).Cut _
Destination:=wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
.Rows(i).Delete
n = n + 1
End If
Next
End With
MsgBox n & " rows moved", vbInformation, Format(Timer - t0, "0.0 secs")
End Sub
翻译后的内容如下：
如果不需要移动很多行，只需简单地扫描工作表并检查相关的单元格值。
选项显式
Sub MoveBasedOnValue2()
Const COL_STATUS = "N"
Dim wsSrc As Worksheet, wsDest As Worksheet
Dim lastrow As Long, i As Long, n As Long
Dim t0 As Single: t0 = Timer
Set wsSrc = Sheet1
With wsSrc
lastrow = .Cells(.Rows.Count, COL_STATUS).End(xlUp).Row
For i = lastrow To 1 Step -1
Select Case LCase(Trim(.Cells(i, COL_STATUS)))
Case "done": Set wsDest = Sheet4
Case "on-going": Set wsDest = Sheet2
Case Else: Set wsDest = Nothing
End Select
If Not wsDest Is Nothing Then
.Rows(i).Cut _
Destination:=wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
.Rows(i).Delete
n = n + 1
End If
Next
End With
MsgBox n & " 行已移动", vbInformation, Format(Timer - t0, "0.0 秒")
End Sub
英文:
If you are not moving many rows then simply scan up the sheet checking the relevant cell value.
Option Explicit
Sub MoveBasedOnValue2()
Const COL_STATUS = "N"
Dim wsSrc As Worksheet, wsDest As Worksheet
Dim lastrow As Long, i As Long, n As Long
Dim t0 As Single: t0 = Timer
Set wsSrc = Sheet1
With wsSrc
lastrow = .Cells(.Rows.Count, COL_STATUS).End(xlUp).Row
For i = lastrow To 1 Step -1
Select Case LCase(Trim(.Cells(i, COL_STATUS)))
Case "done": Set wsDest = Sheet4
Case "on-going": Set wsDest = Sheet2
Case Else: Set wsDest = Nothing
End Select
If Not wsDest Is Nothing Then
.Rows(i).Cut _
Destination:=wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
.Rows(i).Delete
n = n + 1
End If
Next
End With
MsgBox n & " rows moved", vbInformation, Format(Timer - t0, "0.0 secs")
End Sub
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论