How do I bring up the select cells dialog ?

[dd]

It would be cool if I could record a macro and then edit out the selected
cells and replace with some code to make the select range dialog box to
appear, and when I have made the selection the macro resumes with my
selection?

Dylan Dawson
Scotland

 

[dd]

John,

I'm glad I got your attention, you helped me with (Copy specific data
over...), I was able to paste the rows into Sheet2 using your code and a
command button. I'm very grateful.

I've been trying to paste data from a table in Sheet1, below the heading
"Attribute". I've spent all this day in my work, looking at various messages
covering arrays and ranges, is there any way you can help with this?

I think it needs to do the following:

For each cell in worksheet
If cell value="Attribute"
Concatenate cells below, until blank cell is reached and copy contents to
Sheet 2, Column 4, Row 2

Next cell -> if cell value ="Attribute" copy to Sheet 2, Column 4, Row 3 and
so on.

PS I tried to add a RefEdit control, but it went wacko. I'll need to look
into it some more. I would probably use a tool that:
Takes the selected range, concatenates the contents, and pastes the value
into a selected cell in Sheet2.



Do you mean putting a userform in the middle of you program with a RefEdit
control? Try it out, insert userform and put a refedit control on and at the
point in the code you need it type Userform1.show. Post any other questions
you have from there.

 

[DD]

John,

What you say makes sense. Does checking every cell on the page affect memory or filesize (I notice the file is nearly 2mb). I have had some messages appearing on the INDEX page stating "not enough memory to display..."

"Attribute" appears on Column B on about 150 lines in Sheet1
Where "Attribute" appears, I would like every cell beneath it copied - up until a blank cell is reached.
I would like these cells concatenated into a single cell on Sheet2, Column 5. Can you separate them with ", " comma space?

Some background information to clarify what I am trying to do:

The worksheet Sheet1, where I'm taking the data from, was created like a word document. In print layout, each "page" in Sheet1 contains the exact same layout of title headings, column headings, and tables. The number of rows in the tables does vary.

I am working on creating a Sheet2 where each row of data represents a 'page' in print layout on Sheet1.

The reason I am doing this is so that I can use Sheet2 as an INDEX to feed data into a form which I have created on another sheet. This form looksup and displays the values in a similar format to the layout of the Print layout "pages" on Sheet1. The user can then use a listbox to scroll through the records and view the required information on a single sheet, rather than scrolling through an entire document of "pages" (approximately 2200 rows).

Using your previous code to copy over the Row Cell value to Sheet2 whenever the heading "Element" was found in the adjacent cell. I was able to adjust your code and copy the other heading values into the first three columns of Sheet2. Taking it to the next stage I want to concatenate and copy information below the table headings into the representing rows on Sheet2.

I could not understand how to alter the Array values in your previous code example to select any of the column values below "Attribute". On top of this I have the problem of making it stop when it reaches a blank cell and also concatenating and inserting a comma and a space between the values.

You can see that I am no expert in VBA, but I expect to be getting more of this type of work. There seems to be an infinite amount of variables that would seem to make it impossible for me to become expert at this. However, receiving your help and others' assistance in this newsgroup gives me enthusiasm and ideas for manipulating and presenting the information in the work that I receive.
Checking every cell in the sheet is a huge waste if you don't need to, can
"Attribute" be anywhere on the sheet or is it in a particular column(s)? And
to try to clarify, when I find "Attribute" you want every cell beneath it
copied over to another page, what do you want between each item? A "/" or ","
? With this info it should be no problem.

 

[John Bundy]

Here is the code as best as I can understand your issue, if you have a
question on what any of it does let me know and I can walk you through
changing it.

Sub main()
Dim myRow As Integer
Dim myString As String
Dim newRow As Integer

newRow = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1

For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, 2) = "Attribute" Then
myRow = i + 1
Do Until Cells(myRow, 2) = ""

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

Loop
Sheets("sheet2").Cells(newRow, 5) = myString
newRow = newRow + 1
End If
myString = ""


Next

End Sub

 

[dd]

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
Dim myString As String
Dim newRow As Integer

newRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
'Where Sheet2 is the target and Rows count identifies the number of rows to
be populated with data.
'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)

myRow = 1
'Points to the first row

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

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?
'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

 

[John Bundy]

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.

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

 

[dd]

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

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.

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