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

Might I ask you first if the data you are showing is in 3 columns and 4 rows?

It looks like the "text in front of" (50) is in the column to the right of
text39:

And it looks as though the HW INF is one column up and one row to the left
of text39:

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


"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

Hi Mary

It is report download taken from Access. It has many rows 700+ and columns
25 nos

i have HW MS download in two different column and row and text39: in
another colum and value 50 it in the same row

I want macro move value 50 in right of text 39 to the cell of text39 and
copy the HW , MS next row same column

Is their any option can send you the download, so that you can understand
better.

your help will be highly appreciated

god bless you.

regards

saravanan



"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

Hi Mary

Do loop should go on doing the same till al the TEXT39: is deleted and
value on right moved to cell of text39.

regards

saravanan


"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

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

Saravanan,

If you send a file, would you please not send 700 rows, but just select out
enough so I can see the pattern of what the data ordinarily looks like, say,
50-100 rows? I have an account at yahoo ) that I
use for such things...so I don't get spammed, you know.

I am not sure why you are doing the delete-shift xlToLeft---that is, you
could just cut the value to the text39: cell. But when I see the data it may
become clearer. I will get back with you as soon as I can.

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


"saravanan" wrote:

Hi Mary

It is report download taken from Access. It has many rows 700+ and columns
25 nos

i have HW MS download in two different column and row and text39: in
another colum and value 50 it in the same row

I want macro move value 50 in right of text 39 to the cell of text39 and
copy the HW , MS next row same column

Is their any option can send you the download, so that you can understand
better.

your help will be highly appreciated

god bless you.

regards

saravanan



"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

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