John,

This discussion should have been titled "Transport Table Text to Row on
other Sheet"

I managed to solve the thing I wanted to do with RefEdit in the post titled
"Concatenate anyone?"

I've transported most of my data now, you've saved me a tremendous amount of
time. The final thing I have to do now is copy over the conditional data. I
have a some tables with the following headings/formats:

Question | Mandatory | Valid Values
_______________________________
Text | Y or N | Blank or Text

I have to copy the text over only where the Mandatory value = Y and I have
to include any Valid Values that apply. Please don't spend any time on this,
I can use a wee macro Bob Philips sent me to do this.

Anyways
Many thanks for your help John, its more than appreciated.

Dylan

"John Bundy" remove X''''s wrote in message
...
I'll respond to each of these starting with a series of *****, hopefully it
won't get too messy :)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"dd" wrote:

John,

I've replied inline to discuss several areas of the code, to see if I
understand it right.

Sub main()
Dim myRow As Integer '**this is for the current row to be used on sheet 1
Dim myString As String '**this is where the data below attribute is stored
Dim newRow As Integer '** this is for the current row on sheet 2

newRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

'**change to newRow = 2, this tells where to start pasting in sheet 2

'Where Sheet2 is the target and Rows count identifies the number of rows
to
be populated with data.

'***This searches sheet2 column A from the bottom up and finds the last-
'***entered value, that way for each attribute, myString is in a new row

'I notice the End(xlUp) value is -4162 and the (Rows.Count... Value is
65536
' This may need changed, because the text is pasted into a populated
worksheet. In order to get the
'data to match, i.e. one record to each row, the data needs to start in
(2,
5)

'**changing where this is pasted is not a problem, I had assumed an empty-
'**I just started in cell(1,5) if you know the first is 2,5 and the second
is -
'** 3,5 then above and below set newRow=2 and erase the rest of the stuff-
'** below we will set to paste at 2,5, actually newRow,5

myRow = 1
'Points to the first row

'**On sheet 1, this is where data collection begins

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
'For all the cells in Column B...
If Cells(i, 2) = "Attribute" Then
myRow = i + 1
Do Until Cells(myRow, 2) = ""
'If the Cell in the second column is Attribute then myRow = the cells
below,
until a blank cell is reached.

If myString = "" Then myString = Cells(myRow, 2) Else myString = myString
&
", " & Cells(myRow, 2)

'Mystring = one or more cells

myRow = myRow + 1
'Moves to the next row

Loop
'Unsure what this does, but I notice if I remove it I get an "If without
End
If block" error statement

'**The Do until line above tells the program to repeat a certain process
until-
'**X is reached, in this case "", the first run only checks cell myrow,2
where -
'**myRow= the first row after attribute is found myrow=myrow+1 sets us up-
'** to check the next row for data and the loop starts the process of adding
it-
'** to myString over again, Do's always have loops

'**erase this Sheets("Sheet2").Cells(newRow, 5) = myString***
'Cope mystring into Sheet2, a new row, Column5. John, this pastes the text
into Column 5 on the last
'populated row of the Sheet, downwards. How do I start at the second row
of
column 5?

'**change this line to cells(newRow,5)=myString

'I tried changing it to Cells(2, 5) but this pastes, only the final entry
into (2, 5).

newRow = newRow + 1
'newrow = next row

End If
myString = ""
'Clear myString

Next

End Sub

'Regards
'Dylan