Use this version instead:
Sub put_um()
Set r = Range("D10")
For i = 1 To 3
v = ""
For j = 1 To 3
v = v & Cells(i, j).Value & Chr(10)
Next
r.Offset(i, 0).Value = v
Next
End Sub
--
Gary''s Student - gsnu200770
"Risky Dave" wrote:
> Hi,
>
> Thanks for the quick response.
>
> This is nearly what I am after, but is giving me:
>
> 1Data1
> 2Data1
> 3Data1
>
> 1Data2
> 2Data2
> 3Data2
>
> 1Data3
> 2Data3
> 2Data3
>
> ie. the columns concatenated into each cell, whereas I need the rows
> concatenated vertically into each cell.
>
> Thanks.
>
>
> Dave
>
> "Gary''s Student" wrote:
>
> > Sub put_um()
> > Set r = Range("D10")
> > For j = 1 To 3
> > v = ""
> > For i = 1 To 3
> > v = v & Cells(i, j).Value & Chr(10)
> > Next
> > r.Offset(j, 0).Value = v
> > Next
> > End Sub
> >
> > will take your data and put the results in D11, D12, and D13.
> > --
> > Gary''s Student - gsnu200770
> >
> >
> > "Risky Dave" wrote:
> >
> > > Hi,
> > >
> > > I have a large data set that is automatically formatted by a macro. As part
> > > of this macro I need to concatenate sets of three cells into one and format
> > > the output.
> > > Eg. I need to convert:
> > > A B C
> > > 1 1Data1 1Data2 1Data3
> > > 2 2Data1 2Data2 2Data3
> > > 3 3Data1 3Data2 3Data3
> > >
> > > To:
> > > A
> > > 1 1Data1
> > > 1Data2
> > > 1Data3
> > > 2 2Data1
> > > 2Data2
> > > 2Data3
> > > 3 3Data1
> > > 3Data2
> > > 3Data3
> > >
> > > The actual output cell can be pretty much anywhere - I can adjust this bit
> > > to fit in with the rest of the formatting.
> > >
> > > I can do this by setting up a separate sheet with loads of formulae, but as
> > > there are several hundreds of lines involved (I have no way of knowing
> > > exactly how many), each with about 100 cells, this soon makes the file size
> > > excessively large (especially as there's a load of other stuff going on in
> > > the overall workbook).
> > >
> > > Can anyone supply a piece of code that I can drop into the rest of the macro
> > > and will produce the output above?
> > >
> > > Although I don't know how many times this needs to be carried out (because I
> > > don't know the exact number of lines with data in them), I can set a high
> > > (eg. 500 or 1000) limit for the number of lines to be formatted - it doesn't
> > > matter if the macro attempts to format cells with no data in them.
> > >
> > > Hope this makes sense :-)
> > >
> > > If it makes any difference, this is in Office 2003.
> > >
> > > TIA Dave
|
Similar Threads
