search value, copy and paste to a column

**Rick85** · 04-13-2010, 09:53 PM

Hi there!

First of all i want to thank this great community for helping so many people like me that feel lost and dizzy in the programming world of excell VBA

So i call for help
I need a simple macro that searches the rows of a table (A1:C10) looking for a value that start with X letter (example: X100) when found copy and paste to column G of the same row, then go search the next row for X values again to copy and paste in column G.
If the row has no X values it means the X value is the same of the one found in the previous row, so i want to copy the X value that is in the previous row and paste it in column G present row

I am attaching a excell file that has an example to make it more clear what i want to do:

Was wondering if someone can help me out to do a code to acomplish this

Regards
Rick

**rylo** · 04-14-2010, 01:15 AM

Hi

Here goes.

Sub aaa()
  lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  For Each ce In Range("A1:C" & lastrow)
    Select Case Left(ce.Value, 1)
      Case "X"
        Cells(ce.Row, "G").Value = ce.Value
      Case "Y"
        Cells(ce.Row, "H").Value = ce.Value
      Case "Z"
        Cells(ce.Row, "I").Value = ce.Value
    End Select
  Next ce
  
  For Each ce In Range("G1:I" & lastrow)
    If IsEmpty(ce) Then
      ce.Value = ce.Offset(-1, 0).Value
    End If
  
  Next ce
End Sub

rylo