-1

enter image description here

enter image description here

In the input, there is the client's name and bill number. And underneath it, there is code. For example, in row 3, there is client name "5145" and Bill num "Shrin". Under that, there are two rows which are bill details of client 5145. To analyze the data, I need to paste the client name and bill num in columns M and N. Can anyone please write a small VBA code so that I can get the output as per the screenshot.

braX
  • 11,506
  • 5
  • 20
  • 33
Arvind
  • 1
  • 1

1 Answers1

0

You might need to check that my columns are correct as I can't see your columns, my code assumes the name is always a number and the bill is always text so if that isn't correct you may need to switch them. I've also assumed that there are always two and only two rows beneath the header row.

Sub mBillName()

    
    Dim lCount, lRow, lName As Long
    Dim lBill As String
    
    Range("A1048576").Select
    Selection.End(xlUp).Select
    lRow = ActiveCell.Row
    
    For lCount = 2 To lRow
    
    If Range("B" & lCount).Value = "Vendor" Then
        lName = Range("B" & lCount).Value
        lBill = Range("C" & lCount).Value
        
        Range("M" & lCount + 1).Value = lName
        Range("M" & lCount + 2).Value = lName
        Range("N" & lCount + 1).Value = lBill
        Range("N" & lCount + 2).Value = lBill
    
    Else
        
    End If
End Sub
Stuart
  • 144
  • 5
  • You should try to direct new contributors to good or best practice (e.g. fully qualifying range references, avoid Select/Activate, correct declarations, last Row findings); think you might amend your code by learning from numerous posts at SO - c.f. recent [comment](https://stackoverflow.com/questions/73495551/move-entire-column-at-the-end-of-another-column/73500086#comment129818576_73500086) – T.M. Aug 28 '22 at 17:51