Code for copy data using VbA code in Excel

 Question:

 

How can I fix the below code for below snapshots?
I have tried below code

Dim Rng3 As Range
Dim Rng5 As Range

For Each Rng3 In CurCell_1


   For Each Rng5 In CurCell_3

          lLFs = VBA.Len(Rng3) - VBA.Len(VBA.Replace(Rng3, vbLf, ""))

     If lLFs > 0 Then

        Range("B27").Select
        Selection.Copy

        Sheets("AccountModule").Select
        Range("AY2").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

      End If
   Next
Next

but the same is not working.




 Answers:
 

1 down vote

I suggest you use a different kind of routine like this one: Sub separ8(src, trg) Dim r As Range, ar For Each r In src.Rows ar = Split(r.Cells(1, 2), Chr(10)) For Each el In ar trg.Value = r.Cells(1, 1) trg.Offset(0, 1) = el Set trg = trg.Offset(1) Next el Next r End Sub My sub assumes that your input data is in a range given by src and the results should be written into a range starting at trg. So, when there is data like this A B C 1 2 abc 123 456 789 3 xyz 1234 456789 78941 The procedure call separ8 [b2:c3],[b7] will get you this result: A B C 6 7 abc 123 8 abc 456 9 abc 789 10 xyz 1234 11 xyz 456789 12 xyz 78941