-4

Please see image below. What I would like to do is copy the value in cell C3 into column B into rows 4-13. Then copy vehicle number in cell C16 into B17 and so on. Basically this displays all the trips a vehicle has made and the data for different vehicles is separated by blank rows.

Please help.

Check image below:

enter image description here

Mohammad Sadiqur Rahman
  • 5,379
  • 7
  • 31
  • 45
Ellsworth Slon
  • 3
  • 3
  • 1
    This can be done by using a formula of `=TEXT(IF(LEFT(A3,7)="VEHICLE",C3,B2),"#")` in cell B3, and then copying it down through as many rows of data you have. – YowE3K Mar 12 '17 at 07:27

1 Answers1

1

given your data structure you could try this:

Option Explicit

Sub main()
    Dim vehicleRng As Range, cell As Range

    With Range("A2", Cells(Rows.count, 1).End(xlUp))
        .AutoFilter field:=1, Criteria1:="VEHICLE"
        Set vehicleRng = .Resize(.Rows.count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
    End With
    ActiveSheet.AutoFilterMode = False

    For Each cell In vehicleRng
        With cell
            Range(cell.Offset(1), cell.End(xlDown).Offset(-1)).Offset(, 1).Value = cell.Offset(, 2)
        End With
    Next
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • Thank you user3598756. The code above solved the issue pretty well. The vehicle number does run into the next cluster of data. For example it would go from row 4-16 and then would have the new vehicle number in row 17. But this doesnt matter as i will be deleting any blank rows and rows that have "vehicle" in it and only leave the raw data. Thank you again for providing a solution. – Ellsworth Slon Mar 12 '17 at 18:48