excel 如何让它根据值选择一行？

oxiaedzo 于 2023-01-21 发布在其他

关注(0)|答案(1)|浏览(219)

我正在简化一个excel工作表，我希望行中的信息根据值进行传输。如果值=“done”，我希望它传输到Carc。如果值=“On-going”，我希望它传输到Ccon（还没有输入）。这已经写在VBA中，但我愿意尝试其他事情，如果它会使事情更容易。
主要的是，我试图找到一种方法，使代码已经作出，更简单，更实用。唯一的事情我还没有弄清楚的是如何让它选择一个单独的行，而不是所有的行。

Sub MoveBasedOnValue2()

Dim TakeCell As Range
Dim DestCell As Range

Dim Status As Range
Dim Cjob As Worksheet
Dim CArc As Worksheet

Dim Contact As Range, Subject As Range, JobNo As Range, QuoteNo As Range
Dim Dateofcommision As Range, Ddate As Range

Set Cjob = Sheet4
Set CArc = Sheet1

If Cjob.Range("G2") = "Done" Then

Set Contact = Cjob.Range("A2")
Set Subject = Cjob.Range("B2")
Set QuoteNo = Cjob.Range("C2")
Set JobNo = Cjob.Range("D2")
Set Dateofcommision = Cjob.Range("E2")
Set Ddate = Cjob.Range("F2")

Status.Select
Contact.Select
Subject.Select
QuoteNo.Select
JobNo.Select
Dateofcommision.Select
Ddate.Select

If CArc.Range("A2") = "" Then
    Set DestCell = CArc.Range("A2")
Else
    Set DestCell = CArc.Range("A1").End(xlDown).Offset(1, 0)
End If

Contact.Copy DestCell
Subject.Copy DestCell.Offset(0, 1)
QuoteNo.Copy DestCell.Offset(0, 2)
JobNo.Copy DestCell.Offset(0, 3)
Dateofcommision.Copy DestCell.Offset(0, 4)
Ddate.Copy DestCell.Offset(0, 5)

Status.ClearContents
Contact.ClearContents
Subject.ClearContents
QuoteNo.ClearContents
JobNo.ClearContents
Dateofcommision.ClearContents
Ddate.ClearContents
End If

excel

来源：https://stackoverflow.com/questions/75173488/how-do-i-get-it-to-select-a-single-row-based-on-the-value

1条答案

按热度按时间

nbewdwxp1#

你可以这样做：

Sub MoveBasedOnValue2()

    Dim cStatus As Range, wsDest As Worksheet
    
    Set cStatus = Sheet4.Range("G2") 'first cell to check status
    
    Do While Len(cStatus.Value) > 0
        Select Case LCase(cStatus.Value)
            Case "done": Set wsDest = Sheet1
            Case "on-going": Set wsDest = Sheet2 'for example
            Case Else: Set wsDest = Nothing      'no move to make
        End Select
        
        If Not wsDest Is Nothing Then 'got a destination sheet?
            'here Range("A1:F2") is *relative* to the whole row...
            cStatus.EntireRow.Range("A1:F2").Cut _
               Destination:=wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)
        End If
        
        Set cStatus = cStatus.Offset(1, 0) 'next source row
    Loop
End Sub

赞(0）回复(0）举报 2023-01-21

我来回答

excel 如何让它根据值选择一行？

1条答案

相关问题

热门标签

最新问答