Struggling to move object based on the value to respect sheet

0 votes

For a while now, I've been working on this code, but I can't seem to get it to accomplish what I want. I've been circling it in my mind. In essence, I want it to locate and relocate a row based on the value in cStatus. When the value is "Done," it is transferred to Sheet 4. The value is transferred to Sheet2 if the value is "On-going." If the value is "," it remains. Right now, the code I have won't run, but it also won't provide any error messages. I'm not sure how to proceed because I'm not sure what the problem is. I probably made a typo, but I don't see where. Any guidance would be highly valued.

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
Feb 14, 2023 in Others by narikkadan
• 63,720 points
554 views

1 answer to this question.

0 votes

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
answered Feb 14, 2023 by Kithuzzz
• 38,020 points

Related Questions In Others

0 votes
0 answers

Write a DAX query to obtain the bottom 5 customers based on the order price

Jan 22, 2020 in Others by anonymous
• 170 points
408 views
0 votes
1 answer

Excel create an Index on the first sheet with links to subsequent sheets

This seems to be a potential duplicate ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,720 points
1,294 views
0 votes
0 answers

Merge rows based on value (pandas to excel - xlsxwriter)

I'm attempting to use xlsxwriter to output ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,020 points
2,623 views
0 votes
1 answer

Excel MATCH function is not working on an array but works once directed to the matched value

According to the definition here: support.microsoft.com/en-us/office/… if you do ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,720 points
851 views
0 votes
1 answer

How do I get it to select a single row based on the value?

Try this: Sub MoveBasedOnValue2() Dim ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,720 points
553 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,107 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,538 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
828 views
0 votes
1 answer
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,020 points
1,006 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP