Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I bring up the select cells dialog ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I bring up the select cells dialog ?
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. "John Bundy" remove X''s wrote in message ... 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. -- -John Please rate when your question is answered to help us and others know what is helpful. "dd" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I bring up the select cells dialog ?
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. "John Bundy" remove X''''s wrote in message ... 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 Please rate when your question is answered to help us and others know what is helpful. "dd" wrote: 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. "John Bundy" remove X''s wrote in message ... 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. -- -John Please rate when your question is answered to help us and others know what is helpful. "dd" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I bring up the select cells dialog ?
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 -- -John Please rate when your question is answered to help us and others know what is helpful. "DD" wrote: 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. "John Bundy" remove X''''s wrote in message ... 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 Please rate when your question is answered to help us and others know what is helpful. "dd" wrote: 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. "John Bundy" remove X''s wrote in message ... 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. -- -John Please rate when your question is answered to help us and others know what is helpful. "dd" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I bring up the select cells dialog ?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I bring up the select cells dialog ?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I bring up the select cells dialog ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select a range with a dialog box | Excel Programming | |||
Dialog box for user to select a folder | Excel Programming | |||
Code for bring up the file-open dialog box | Excel Programming | |||
When I select "format cells", the format dialog box does not disp. | Excel Worksheet Functions | |||
bring up dialog box from command button? | Excel Programming |