Code to find x, copy cell to right and paste

[pleasefreeus]

This is a small sample of the data I am looking at:
<table width="148" border="0" cellpadding="0" cellspacing="0"><col style="width: 53pt;" width="71"> <col style="width: 58pt;" width="77"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt; width: 53pt;" width="71" align="right" height="15">Pedido</td> <td class="xl24" style="width: 58pt;" width="77" align="right">Item</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">Recebimen</td> <td class="xl24" align="right">1004</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">Nota:</td> <td class="xl24" align="right">49280</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">6200156</td> <td class="xl24" align="right">40X0100</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">Recebimen</td> <td class="xl24" align="right">1005</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">Nota:</td> <td class="xl24" align="right">108109</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">6200767</td> <td class="xl24" align="right">C53034X</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">6200767</td> <td class="xl24" align="right">C930X72</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl24" style="height: 11.25pt;" align="right" height="15">6200912</td> <td class="xl24" align="right">C930H2K</td> </tr> </tbody></table>
I need code that will find the rows with Recebimen in column c, copy the cell in the same row in column d and paste it in column a.

If it is possible I really need it to be pasted in column a for all the rows until the next Recebimen is found. This data has thousands of rows and the number of rows changes each time I run it.

I am using Excel 2003.

I thank you in advance for any help with this.

 

[Rick Rothstein]

Does this macro do what you want?

Sub FillColumnAwithItemNumbers()
  Dim LastRow As Long, RecebimenCells As String, Area As Range, Cell As Range
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Application.ScreenUpdating = False
  With Columns("C")
    .Replace "Recebimen", "", xlWhole
    With .SpecialCells(xlCellTypeBlanks)
      RecebimenCells = .Address
      .Replace "", "Recebimen", xlWhole
    End With
  End With
  On Error Resume Next
  For Each Cell In Range(RecebimenCells)
    Cells(Cell.Row, "A").Value = Cell.Offset(, 1).Value
  Next
  For Each Area In Columns("A").Resize(LastRow).SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area(1).Offset(-1).Value
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[pleasefreeus]

This did not seem to do anything. Any ideas??

 

[Rick Rothstein]

Is the word "Recebimen" in Column C a text entry or is it the result of a formula?

Are you running the macro with the sheet containing your data as the active sheet?

 

[pleasefreeus]

It is text and it is in the active sheet.

 

[Rick Rothstein]

Hmm! Then I don't see why that code is not working... you described the same set up that I did for my test code. Let's try something different. Give this macro a try and tell me if it works or not...

Sub FillColumnAwithItemNumbers()
  Dim LastRow As Long, FirstAddress As String, C As Range, Area As Range
  Application.ScreenUpdating = False
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  With Range("C2:C" & LastRow)
    Set C = .Find("Recebimen", , xlFormulas, xlWhole, , , False)
    If Not C Is Nothing Then
      FirstAddress = C.Address
      Do
        Cells(C.Row, "A").Value = Cells(C.Row, "D").Value
        Set C = .FindNext(C)
      Loop While Not C Is Nothing And C.Address <> FirstAddress
    End If
  End With
  On Error Resume Next
  For Each Area In Columns("A").Resize(LastRow).SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area(1).Offset(-1).Value
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

 

[pleasefreeus]

Yes!!! That worked. You have saved me so much time.

Thank you! Thank you! Thank you!

 

[Rick Rothstein]

Great news! And you are quite welcome, of course. I'm still at a loss why my first macro didn't work though, but at least you now have a solution. And, in thinking about it, this macro is probably a little "better" constructed than the first one I posted anyway, so you may have lucked out in the end by having that problem.

 

[pleasefreeus]

Hi,

I wanted to change it so that it would also copy the value in D in the row below to column B.


I changed the code (see below), but it only copies the value in the row below for the first row. It does not keep pasting it in Column B until the next Recebimen is found?

Dim FinalRow As Long, FirstAddress As String, C As Range, Area As Range
Application.ScreenUpdating = False
FinalRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
With Range("C2:C" & FinalRow)
Set C = .Find("Recebimen", , xlFormulas, xlWhole, , , False)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
Cells(C.Row, "A").Value = Cells(C.Row, "D").Value
Cells(C.Row, "B").Value = Cells(C.Row + 1, "D").Value
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
On Error Resume Next
For Each Area In Columns("A").Resize(FinalRow).SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
On Error GoTo 0
Application.ScreenUpdating = True

This is what I end up with:

<table width="256" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt; width: 48pt;" width="64" align="right" height="15">Receiving Nbr</td> <td class="xl22" style="width: 48pt;" width="64" align="right">Note</td> <td class="xl22" style="width: 48pt;" width="64" align="right">Pedido</td> <td class="xl22" style="width: 48pt;" width="64" align="right">Item</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1004</td> <td class="xl22" align="right">49280</td> <td class="xl22" align="right">Recebimen</td> <td class="xl22" align="right">1004</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1004</td> <td class="xl22" align="right">
</td> <td class="xl22" align="right">Nota:</td> <td class="xl22" align="right">49280</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1004</td> <td class="xl22" align="right">
</td> <td class="xl22" align="right">6200156</td> <td class="xl22" align="right">40X0100</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1005</td> <td class="xl22" align="right">108109</td> <td class="xl22" align="right">Recebimen</td> <td class="xl22" align="right">1005</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1005</td> <td class="xl22" align="right">
</td> <td class="xl22" align="right">Nota:</td> <td class="xl22" align="right">108109</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1005</td> <td class="xl22" align="right">
</td> <td class="xl22" align="right">6200767</td> <td class="xl22" align="right">C53034X</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1005</td> <td class="xl22" align="right">
</td> <td class="xl22" align="right">6200767</td> <td class="xl22" align="right">C930X72</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl22" style="height: 11.25pt;" align="right" height="15">1005</td> <td class="xl22" align="right">
</td> <td class="xl22" align="right">6200912</td> <td class="xl22" align="right">C930H2K</td> </tr> </tbody></table>

Can you help me fix this as well?