Move columns...
 

I am working on a macro to reformat an exported report. So fa
everything has been pretty easy going. Now I am at the point where i
gets bit complex.

In column A I have multiple cell that start with the word “Name
(persons name)” the rest of that row is blank. The next row, column
is blank but the rest of the row is filled with numbers. What I wan
the macro to do is find where Ax starts with “Name:” then take Bx+
(the next row starting with B) and move those numbers to the same ro
as A.

Befo
A B C D
1 Name: John Doe
2 10 19.95 15
3 Name: Sue Smith
4 40 15 99
5 Totals 50 34.95 114

After:
A B C D
1 Name: John Doe 10 19.95 15
2
3 Name: Sue Smith 40 15 99
4
5 Totals 50 34.95 114


So, can anyone help me with this?

Thanks in advance!
Oogi

--
Oogi
-----------------------------------------------------------------------
Oogie's Profile: http://www.excelforum.com/member.php...fo&userid=3734
View this thread: http://www.excelforum.com/showthread.php?threadid=57044

Move columns...
 

If the cells for the continuation row (found in column A) are blank as you
decribe

Sub FixData()
Dim rng as Range, cell as Range
set rng = Columns(1).specialCells(xlconstants,xltextvalues)
for each cell in rng
cell.offset(1,1).Resize(1,100).copy Cell.offset(0,1)
cell.offset(1,1).Resize(1,100).Clearcontents
Next
End sub

--
Regards,
Tom Ogilvy


"Oogie" wrote:


I am working on a macro to reformat an exported report. So far
everything has been pretty easy going. Now I am at the point where it
gets bit complex.

In column A I have multiple cell that start with the word €œName:
(persons name)€� the rest of that row is blank. The next row, column A
is blank but the rest of the row is filled with numbers. What I want
the macro to do is find where Ax starts with €œName:€� then take Bx+1
(the next row starting with B) and move those numbers to the same row
as A.

Befo
A B C D
1 Name: John Doe
2 10 19.95 15
3 Name: Sue Smith
4 40 15 99
5 Totals 50 34.95 114

After:
A B C D
1 Name: John Doe 10 19.95 15
2
3 Name: Sue Smith 40 15 99
4
5 Totals 50 34.95 114


So, can anyone help me with this?

Thanks in advance!
Oogie


--
Oogie
------------------------------------------------------------------------
Oogie's Profile: http://www.excelforum.com/member.php...o&userid=37343
View this thread: http://www.excelforum.com/showthread...hreadid=570444

Move columns...
 

Tom,

Thank You for your reply! It works great with one exception, it is
removing the totals...would there be a way of saying if cell Ax
contains "total" to skip?

Thanks Again!
Oogie


--
Oogie
------------------------------------------------------------------------
Oogie's Profile: http://www.excelforum.com/member.php...o&userid=37343
View this thread: http://www.excelforum.com/showthread...hreadid=570444

Move columns...
 

Sub FixData()
Dim rng as Range, cell as Range
set rng = Columns(1).specialCells(xlconstants,xltextvalues)
for each cell in rng
if instr(1,cell.value,"total",vbTextcompare) = 0 then
cell.offset(1,1).Resize(1,100).copy Cell.offset(0,1)
cell.offset(1,1).Resize(1,100).Clearcontents
end if
Next
End sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

If the cells for the continuation row (found in column A) are blank as you
decribe

Sub FixData()
Dim rng as Range, cell as Range
set rng = Columns(1).specialCells(xlconstants,xltextvalues)
for each cell in rng
cell.offset(1,1).Resize(1,100).copy Cell.offset(0,1)
cell.offset(1,1).Resize(1,100).Clearcontents
Next
End sub

--
Regards,
Tom Ogilvy


"Oogie" wrote:


I am working on a macro to reformat an exported report. So far
everything has been pretty easy going. Now I am at the point where it
gets bit complex.

In column A I have multiple cell that start with the word €œName:
(persons name)€� the rest of that row is blank. The next row, column A
is blank but the rest of the row is filled with numbers. What I want
the macro to do is find where Ax starts with €œName:€� then take Bx+1
(the next row starting with B) and move those numbers to the same row
as A.

Befo
A B C D
1 Name: John Doe
2 10 19.95 15
3 Name: Sue Smith
4 40 15 99
5 Totals 50 34.95 114

After:
A B C D
1 Name: John Doe 10 19.95 15
2
3 Name: Sue Smith 40 15 99
4
5 Totals 50 34.95 114


So, can anyone help me with this?

Thanks in advance!
Oogie


--
Oogie
------------------------------------------------------------------------
Oogie's Profile: http://www.excelforum.com/member.php...o&userid=37343
View this thread: http://www.excelforum.com/showthread...hreadid=570444