I'm having an odd issue.
I'm searching through a range searching for any cell that has a strikethrough, if a strikethrough is detected, then that entire row that contains data will get copied/pasted to another worksheet of the same workbook.
I'm also searching all cells above the cell that had the strikethrough, looking for the first cell with an interior.color = rgb(0,0,0) and once found it will place that data on the other worksheet as well.
Here's my code.
Private Sub CommandButton1_Click()
Dim ipWS As Worksheet, compWS As Worksheet
Dim compDest As Range, rrCell As Range
Dim alastRow As Long
Set ipWS = ThisWorkbook.Worksheets("In Processing")
Set compWS = ThisWorkbook.Worksheets("Completed")
Set compDest = compWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
alastRow = ipWS.Cells(Rows.Count, "A").End(xlUp).Row
Dim rackRng As Range
Dim cellRng As Range
Application.FindFormat.Interior.Color = RGB(0, 0, 0)
For Each rrCell In ipWS.Range("A1:A" & alastRow).Cells
If rrCell.Font.Strikethrough = True Then
Set cellRng = ipWS.Range(rrCell, rrCell.End(xlToRight))
cellRng.Copy compDest.Offset(0, 1)
'Application.CutCopyMode = False
Set rackRng = ipWS.Range(rrCell, rrCell.End(xlUp)).Find("*", , , , , xlPrevious, , , SearchFormat:=True)
rackRng.Copy compDest
'Application.CutCopyMode = False
ipWS.Range(rrCell, rrCell.End(xlToRight)).EntireRow.Delete
Set compDest = compDest.Offset(1, 0)
End If
Next rrCell
With compWS.Range("A:P")
.Font.Strikethrough = False
.ColumnWidth = 25
.Font.Size = 14
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlVAlignCenter
End With
End Sub
the code works just fine if I have < 2 cells with a strikethrough. Once I have > 2 cells with a strikethrough it starts recognizing every other cell with a strikethrough and leaves those on the original worksheet.
If I hit the button again, then the ones that were skipped then move to the destination worksheet.
Here's some pics
The 2nd picture is the result of the first time I click the button. It recognizes the first cell with strikethrough, then skips the next one, then grabs the 3rd one. But like I said before, if I press the button again, then the one that was skipped will then go to the worksheet. I have no idea why it's not grabbing every condition the first time
Am I doing something wrong here?
Application.cutcopymode = false is commented out because that didn't seem to work either.
I've tried about every single thing I know how. I've tried calling out both worksheets at every opportunity but that didn't work.
Can someone please help remove me from my madness.
Thanks for the tip in the right direction @Siddharth Rout.
Private Sub CommandButton1_Click()
Dim ipWS As Worksheet, compWS As Worksheet
Dim compDest As Range, rrCell As Range
Dim i As Integer
Dim alastRow As Long
Set ipWS = ThisWorkbook.Worksheets("In Processing")
Set compWS = ThisWorkbook.Worksheets("Completed")
alastRow = ipWS.Cells(Rows.Count, 1).End(xlUp).Row
Dim rackRng As Range
Dim cellRng As Range
Set compDest = compWS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Application.FindFormat.Interior.Color = RGB(0, 0, 0)
For i = alastRow To 1 Step -1
If Range(Cells(i, 1), Cells(i, 1)).Font.Strikethrough = True Then
Set rackRng = ipWS.Range(Cells(i, 1), Cells(i, 1).End(xlUp)).Find("*", , , , , xlPrevious, , , SearchFormat:=True)
rackRng.Copy compDest
Application.CutCopyMode = False
Range(Cells(i, 1), Cells(i, Columns.Count).End(xlToLeft)).Copy compDest.Offset(0, 1)
Application.CutCopyMode = False
Set compDest = compDest.Offset(1, 0)
Range(Cells(i, 1), Cells(i, 1)).EntireRow.Delete
End If
Next i
With compWS.Range("A:P")
.Font.Strikethrough = False
.ColumnWidth = 25
.Font.Size = 14
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlVAlignCenter
End With
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments