Saravanan, Here is code I found to work. Sorry I am late with this; if you
already found an answer, that's ok. You will see that I had to declare the
variable c...you probably already handle that elsewhere, and that
firstaddress was not needed. This macro MUST run from the "download_data"
sheet since the output sheet won't find "Text39:" string. Workbook was
received in Outline mode which made c=Nothing early in the process. Also,
your code was testing 500 rows in Column D (there is no Text39: in this
column). If your actual workbook looks like the one you sent to me, run the
..Find on the entire Column M. Then extrapolate this from the "download_data"
worksheet to the output sheet you are creating. Hope this helps.

Sub saravanan()

With Worksheets(1).Range("M:M")
Set c = .Find("Text39:", LookIn:=xlValues)
Do
c.Activate
' Check that we don't have a bad error that needs to be handled
If Err.Number = 0 Then
ActiveCell.Offset(0, -4).Value = c.Cells(-1, -3).Value
ActiveCell.Offset(0, -3).Value = c.Cells(-1, -2).Value
c.Value = ActiveCell.Offset(0, 1).Value
Set c = .FindNext(c)
Else
'Saravanan, create an error handler if you don't have one elsewhere
MsgBox "Deal with error " & Err.Number & ": " & Err.Description
End If
Loop Until c Is Nothing
End With

--
--Being powerful is like being a lady; if you have to tell people you are,
you aren''t. ~~Margaret Thatcher


"saravanan" wrote:

Hi Mary

I have writen a macro

which is not working properly

With Worksheets(1).Range("d1:d500")
Set c = .Find("text39:", LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Set c = ActiveCell
Selection.Delete Shift:=xlToLeft
'c.Activate
ActiveSheet.Cells(c.Row, 3).Select
MsgBox (c.Value)
ActiveCell.FormulaR1C1 = "=R[-1]C"
Set c = .FindNext(c)
c.Activate
Loop While Not c Is Nothing And c.Address < firstaddres
End If
End With
End Sub

this will help to understand what i am looking for.

"saravanan" wrote:

i want create loop for the below.

I have excel sheets contain data as below

HW INF
text39: 50

MS UP
text39: 40

the macro should move value the infront of text39: to the text39 cell and
copy the of HW INF in the same row

can any only help me.

regards

saravanan