mardi 23 juin 2020

Select last column in the first row which contains text and go to the next row and do the same

can someone please help me on this code which i have been having trouble to do for the past 2 days. What i need to do is find the last column in that specific row starting from Column C and onwards if there's any text which i have managed to find but it only does it for the first row as i can't seem to find the code to go to the next row and repeat the action; the order it should work in is by going to the last cell in that column of that row and go left and when it find text in a cell it will then insert a row below it and cut and paste the text into the below row in column B. This will continue on until all the cells from C and onwards don't contain any text and move on to the next Row repeating the above action.

Sub newcode_eachline()
Dim datacheck as Range
Dim valuecell As String
Dim cell As Range


Range("A1").Select


Do
Set datacheck = Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 2).End(xlToRight))

For Each cell In datacheck

If cell <> "" Then
ActiveCell.Select
valuecell = Cells(Columns.Count).End(xlToLeft).Select


  
ActiveCell.Offset(1, 0).EntireRow.Insert shift:=xlDown
ActiveCell.Select
Selection.Cut

ActiveCell.Offset(1, 0).End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste

    ActiveCell.Offset.End(xlToLeft).End(xlUp).Select
    
    
    
Else
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, 0) <> ""

End If



Next
Loop Until ActiveCell.Offset(0, 0).End(xlDown).Value = ""
End Sub

The "Do Set datacheck = Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 2).End(xlToRight)) For Each cell In Datacheck," doesn't seem to work properly and i can't seem to find a solution to check from C and all the way to the last column cell if there's any text in that range and the Valuecell = Cells(Columns.Count).End(xlToLeft).Select which only targets the first row.

Much appreciated if someone can help me out with this and let me know.

enter image description here

Aucun commentaire:

Enregistrer un commentaire