Move columns...

[Oogie]

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.

Before:
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

[Tom Ogilvy]

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.
>
> Before:
> 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
>
>

[Oogie]

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

[Tom Ogilvy]

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.
> >
> > Before:
> > 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
> >
> >