If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to return an array of values that are not zero/Null
Dear Excel experts
I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#2
|
|||
|
|||
How to return an array of values that are not zero/Null
Use the code below. It needs to go into the code module for your "Sheet Z".
To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#3
|
|||
|
|||
How to return an array of values that are not zero/Null
Jlatham,
Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#4
|
|||
|
|||
How to return an array of values that are not zero/Null
One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to update from the first, but I'm not that person. One problem is that your second sheet has to know, somehow, how many entries are on the first sheet - about the only way to do that with functions is to fill lots of empty rows with the formulas in anticipation of future entries on the source sheet. Then the formulas have to 'close ranks' - i.e., not pick up entries except when there is a value to be brought over without skipping rows. Worksheet functions can't add other functions to a sheet, and they cannot do things like cause a sheet to be filtered or hide/display rows/columns. So for me, the easy path was to code up the solution - and since I'm a coder by nature, that's the route I took. Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ I've had a project in progress for some time to write an "intro to Excel VBA programming" and while I've made good progress with it, it keeps getting pushed to the back burner and so I haven't got it to a point I feel comfortable sticking it up on the internet as another free source of learning (that would hopefully actually be useful also). "apache007" wrote: Jlatham, Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#5
|
|||
|
|||
How to return an array of values that are not zero/Null
Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot. Please let me know if that ever published. "JLatham" wrote: One of the people in here that's a lot sharper than I am with Excel worksheet functions might be able to come up with formulas for the second sheet to update from the first, but I'm not that person. One problem is that your second sheet has to know, somehow, how many entries are on the first sheet - about the only way to do that with functions is to fill lots of empty rows with the formulas in anticipation of future entries on the source sheet. Then the formulas have to 'close ranks' - i.e., not pick up entries except when there is a value to be brought over without skipping rows. Worksheet functions can't add other functions to a sheet, and they cannot do things like cause a sheet to be filtered or hide/display rows/columns. So for me, the easy path was to code up the solution - and since I'm a coder by nature, that's the route I took. Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ I've had a project in progress for some time to write an "intro to Excel VBA programming" and while I've made good progress with it, it keeps getting pushed to the back burner and so I haven't got it to a point I feel comfortable sticking it up on the internet as another free source of learning (that would hopefully actually be useful also). "apache007" wrote: Jlatham, Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#6
|
|||
|
|||
How to return an array of values that are not zero/Null
I am NOT 'done' with the intro to VBA, but I think that perhaps there's
something of use in it even now. Either there is, or I've written almost 50 pages of useless gibberish! You're welcome to take a peek at it. The most current version of the book may be downloaded, free of charge, from: http://www.jlathamsite.com/LearningPage.htm Look for the link to the .pdf document just below the heading "Introduction to VBA Programming". I recommend right-clicking the link and choosing "Save Target As" to get a copy of it onto your system. My Excel-MVP-persona email address is in the book, and you're welcome to use it to provide me any feedback for it. Just in case, the email addy is (remove spaces) Help From @ jlathamsite. com If the links on the page I provided give you problems, let me know, I just revised that LearningPage.htm today and haven't tested them all, but the link to the .pdf file did work properly for me. "apache007" wrote: Thanks, Jlatham. I will start digging those sites on my free time. If you are done with Intro to Excel VBA, that will help out a lot. Please let me know if that ever published. "JLatham" wrote: One of the people in here that's a lot sharper than I am with Excel worksheet functions might be able to come up with formulas for the second sheet to update from the first, but I'm not that person. One problem is that your second sheet has to know, somehow, how many entries are on the first sheet - about the only way to do that with functions is to fill lots of empty rows with the formulas in anticipation of future entries on the source sheet. Then the formulas have to 'close ranks' - i.e., not pick up entries except when there is a value to be brought over without skipping rows. Worksheet functions can't add other functions to a sheet, and they cannot do things like cause a sheet to be filtered or hide/display rows/columns. So for me, the easy path was to code up the solution - and since I'm a coder by nature, that's the route I took. Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ I've had a project in progress for some time to write an "intro to Excel VBA programming" and while I've made good progress with it, it keeps getting pushed to the back burner and so I haven't got it to a point I feel comfortable sticking it up on the internet as another free source of learning (that would hopefully actually be useful also). "apache007" wrote: Jlatham, Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#7
|
|||
|
|||
How to return an array of values that are not zero/Null
JLatham,
Thanks for the reference. I will get start on it. I need a little more help on your coding. Could you add the code on my example, so that it will display the same result WITH EXCEPTION that SPECIFIC COLOUMN on sheet X contains text equal to something ??? Thanks in advance. "JLatham" wrote: I am NOT 'done' with the intro to VBA, but I think that perhaps there's something of use in it even now. Either there is, or I've written almost 50 pages of useless gibberish! You're welcome to take a peek at it. The most current version of the book may be downloaded, free of charge, from: http://www.jlathamsite.com/LearningPage.htm Look for the link to the .pdf document just below the heading "Introduction to VBA Programming". I recommend right-clicking the link and choosing "Save Target As" to get a copy of it onto your system. My Excel-MVP-persona email address is in the book, and you're welcome to use it to provide me any feedback for it. Just in case, the email addy is (remove spaces) Help From @ jlathamsite. com If the links on the page I provided give you problems, let me know, I just revised that LearningPage.htm today and haven't tested them all, but the link to the .pdf file did work properly for me. "apache007" wrote: Thanks, Jlatham. I will start digging those sites on my free time. If you are done with Intro to Excel VBA, that will help out a lot. Please let me know if that ever published. "JLatham" wrote: One of the people in here that's a lot sharper than I am with Excel worksheet functions might be able to come up with formulas for the second sheet to update from the first, but I'm not that person. One problem is that your second sheet has to know, somehow, how many entries are on the first sheet - about the only way to do that with functions is to fill lots of empty rows with the formulas in anticipation of future entries on the source sheet. Then the formulas have to 'close ranks' - i.e., not pick up entries except when there is a value to be brought over without skipping rows. Worksheet functions can't add other functions to a sheet, and they cannot do things like cause a sheet to be filtered or hide/display rows/columns. So for me, the easy path was to code up the solution - and since I'm a coder by nature, that's the route I took. Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ I've had a project in progress for some time to write an "intro to Excel VBA programming" and while I've made good progress with it, it keeps getting pushed to the back burner and so I haven't got it to a point I feel comfortable sticking it up on the internet as another free source of learning (that would hopefully actually be useful also). "apache007" wrote: Jlatham, Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#8
|
|||
|
|||
How to return an array of values that are not zero/Null
Could you phrase your request a little differently, I'm not understanding it
right now. Do you mean you want things to work the way they do now except that if something is in a particular column then don't do it, or do you mean that a certain value must be in a particular column for it to do the move? The change is going to be in this section: For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next and what we have to do is put another test in one way or another. Keep in mind that our xSheetClassList is now made up of the used cells in column A on sheetX. So when we use .OFFSET(row, COL) we modify the COL parameter to identify the column to examine, where an offset of 0 = column A itself, 1 would refer to column B, 2 = column C, etc. Here's how it could look if we only do the move only when a particular value is in column B (and it's for a text entry test) For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0,1) = "MUST BE THIS VALUE" Then If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If End If Next Here's how it could look if we only do the move only when a particular value is NOT in column B (and it's for a numeric entry test) For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0,1) 3.14159 Then If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If End If Next Hope one of those helps you get it done they way it needs to be done. "apache007" wrote: JLatham, Thanks for the reference. I will get start on it. I need a little more help on your coding. Could you add the code on my example, so that it will display the same result WITH EXCEPTION that SPECIFIC COLOUMN on sheet X contains text equal to something ??? Thanks in advance. "JLatham" wrote: I am NOT 'done' with the intro to VBA, but I think that perhaps there's something of use in it even now. Either there is, or I've written almost 50 pages of useless gibberish! You're welcome to take a peek at it. The most current version of the book may be downloaded, free of charge, from: http://www.jlathamsite.com/LearningPage.htm Look for the link to the .pdf document just below the heading "Introduction to VBA Programming". I recommend right-clicking the link and choosing "Save Target As" to get a copy of it onto your system. My Excel-MVP-persona email address is in the book, and you're welcome to use it to provide me any feedback for it. Just in case, the email addy is (remove spaces) Help From @ jlathamsite. com If the links on the page I provided give you problems, let me know, I just revised that LearningPage.htm today and haven't tested them all, but the link to the .pdf file did work properly for me. "apache007" wrote: Thanks, Jlatham. I will start digging those sites on my free time. If you are done with Intro to Excel VBA, that will help out a lot. Please let me know if that ever published. "JLatham" wrote: One of the people in here that's a lot sharper than I am with Excel worksheet functions might be able to come up with formulas for the second sheet to update from the first, but I'm not that person. One problem is that your second sheet has to know, somehow, how many entries are on the first sheet - about the only way to do that with functions is to fill lots of empty rows with the formulas in anticipation of future entries on the source sheet. Then the formulas have to 'close ranks' - i.e., not pick up entries except when there is a value to be brought over without skipping rows. Worksheet functions can't add other functions to a sheet, and they cannot do things like cause a sheet to be filtered or hide/display rows/columns. So for me, the easy path was to code up the solution - and since I'm a coder by nature, that's the route I took. Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ I've had a project in progress for some time to write an "intro to Excel VBA programming" and while I've made good progress with it, it keeps getting pushed to the back burner and so I haven't got it to a point I feel comfortable sticking it up on the internet as another free source of learning (that would hopefully actually be useful also). "apache007" wrote: Jlatham, Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#9
|
|||
|
|||
How to return an array of values that are not zero/Null
JLatham,
Is there an easier way to contact you? Do you have MSN/Yahoo msgr?? "JLatham" wrote: Could you phrase your request a little differently, I'm not understanding it right now. Do you mean you want things to work the way they do now except that if something is in a particular column then don't do it, or do you mean that a certain value must be in a particular column for it to do the move? The change is going to be in this section: For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next and what we have to do is put another test in one way or another. Keep in mind that our xSheetClassList is now made up of the used cells in column A on sheetX. So when we use .OFFSET(row, COL) we modify the COL parameter to identify the column to examine, where an offset of 0 = column A itself, 1 would refer to column B, 2 = column C, etc. Here's how it could look if we only do the move only when a particular value is in column B (and it's for a text entry test) For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0,1) = "MUST BE THIS VALUE" Then If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If End If Next Here's how it could look if we only do the move only when a particular value is NOT in column B (and it's for a numeric entry test) For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0,1) 3.14159 Then If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If End If Next Hope one of those helps you get it done they way it needs to be done. "apache007" wrote: JLatham, Thanks for the reference. I will get start on it. I need a little more help on your coding. Could you add the code on my example, so that it will display the same result WITH EXCEPTION that SPECIFIC COLOUMN on sheet X contains text equal to something ??? Thanks in advance. "JLatham" wrote: I am NOT 'done' with the intro to VBA, but I think that perhaps there's something of use in it even now. Either there is, or I've written almost 50 pages of useless gibberish! You're welcome to take a peek at it. The most current version of the book may be downloaded, free of charge, from: http://www.jlathamsite.com/LearningPage.htm Look for the link to the .pdf document just below the heading "Introduction to VBA Programming". I recommend right-clicking the link and choosing "Save Target As" to get a copy of it onto your system. My Excel-MVP-persona email address is in the book, and you're welcome to use it to provide me any feedback for it. Just in case, the email addy is (remove spaces) Help From @ jlathamsite. com If the links on the page I provided give you problems, let me know, I just revised that LearningPage.htm today and haven't tested them all, but the link to the .pdf file did work properly for me. "apache007" wrote: Thanks, Jlatham. I will start digging those sites on my free time. If you are done with Intro to Excel VBA, that will help out a lot. Please let me know if that ever published. "JLatham" wrote: One of the people in here that's a lot sharper than I am with Excel worksheet functions might be able to come up with formulas for the second sheet to update from the first, but I'm not that person. One problem is that your second sheet has to know, somehow, how many entries are on the first sheet - about the only way to do that with functions is to fill lots of empty rows with the formulas in anticipation of future entries on the source sheet. Then the formulas have to 'close ranks' - i.e., not pick up entries except when there is a value to be brought over without skipping rows. Worksheet functions can't add other functions to a sheet, and they cannot do things like cause a sheet to be filtered or hide/display rows/columns. So for me, the easy path was to code up the solution - and since I'm a coder by nature, that's the route I took. Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ I've had a project in progress for some time to write an "intro to Excel VBA programming" and while I've made good progress with it, it keeps getting pushed to the back burner and so I haven't got it to a point I feel comfortable sticking it up on the internet as another free source of learning (that would hopefully actually be useful also). "apache007" wrote: Jlatham, Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
#10
|
|||
|
|||
How to return an array of values that are not zero/Null
Best way is probably via email - remove spaces:
Help From @ jlathamsite. com will get it to me. Just be sure to remind me of your apache007 id here so I'll have a clue as to what the email is about. "JLatham" wrote: I am NOT 'done' with the intro to VBA, but I think that perhaps there's something of use in it even now. Either there is, or I've written almost 50 pages of useless gibberish! You're welcome to take a peek at it. The most current version of the book may be downloaded, free of charge, from: http://www.jlathamsite.com/LearningPage.htm Look for the link to the .pdf document just below the heading "Introduction to VBA Programming". I recommend right-clicking the link and choosing "Save Target As" to get a copy of it onto your system. My Excel-MVP-persona email address is in the book, and you're welcome to use it to provide me any feedback for it. Just in case, the email addy is (remove spaces) Help From @ jlathamsite. com If the links on the page I provided give you problems, let me know, I just revised that LearningPage.htm today and haven't tested them all, but the link to the .pdf file did work properly for me. "apache007" wrote: Thanks, Jlatham. I will start digging those sites on my free time. If you are done with Intro to Excel VBA, that will help out a lot. Please let me know if that ever published. "JLatham" wrote: One of the people in here that's a lot sharper than I am with Excel worksheet functions might be able to come up with formulas for the second sheet to update from the first, but I'm not that person. One problem is that your second sheet has to know, somehow, how many entries are on the first sheet - about the only way to do that with functions is to fill lots of empty rows with the formulas in anticipation of future entries on the source sheet. Then the formulas have to 'close ranks' - i.e., not pick up entries except when there is a value to be brought over without skipping rows. Worksheet functions can't add other functions to a sheet, and they cannot do things like cause a sheet to be filtered or hide/display rows/columns. So for me, the easy path was to code up the solution - and since I'm a coder by nature, that's the route I took. Learning VBA there are a number of site around the net to help. http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.the-excel-advisor.com/exc...-tutorial.html http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm http://www.exceltip.com/excel_links.html there are other sites that provide usefull information about specific issues. http://www.contextures.com/ http://www.cpearson.com/ http://www.j-walk.com/ http://www.mcgimpsey.com/ http://www.rondebruin.nl/ http://www.mrexcel.com http://www.mvps.org/ I've had a project in progress for some time to write an "intro to Excel VBA programming" and while I've made good progress with it, it keeps getting pushed to the back burner and so I haven't got it to a point I feel comfortable sticking it up on the internet as another free source of learning (that would hopefully actually be useful also). "apache007" wrote: Jlatham, Thanks for the advice. It works perfectly. Boy...I do not expect I will need to do it through programming. Is there a way doing that using just on Excel's feature??? And is there any good website to brush up my programming skill on VB specializing on EXCEL??? "JLatham" wrote: Use the code below. It needs to go into the code module for your "Sheet Z". To get it the Open the workbook, select Sheet Z and right-click on its name tab. Select the [View Code] option from the list that appears. Copy the code below and paste it into the module that appeared. Modify any of the Const values that need to be modified. Close the VB Editor. After that, each time you select Sheet Z, the list on it will be updated from the list on Sheet X, so it will always be current based on the contents of Sheet X. There's also a line of code that is currently commented out that you can turn into an active statement once you are sure things are working properly. When you do that, it will update SheetZ much faster. Here's the code, hope it helps: Private Sub Worksheet_Activate() 'auto update from SheetX ' 'change these constants to 'match worksheet names and 'columns/rows used ' Const sheetXName = "SheetX" Const xClassColumn = "A" Const xClassStudentsCol = "B" Const xFirstClassRow = 2 'these have to do with 'this sheet (sheetZ) Const zClassColumn = "A" Const zFirstClassRow = 2 'end of user defined constants Dim xSheet As Worksheet Dim xSheetClassList As Range Dim anyxSheetClass As Range Dim offsetToStudentCount As Integer Dim zRow As Long Dim zBaseCell As Range ' 'once you are sure it is working, 'remove the apostrophe from the beginning of 'the next instruction to improve speed 'Application.ScreenUpdating = False 'start by erasing old entries on SheetZ 'and rebuilding the labels in row 1 Cells.ClearContents Range("A1") = "Class" Range("B1") = "No. Students" Set xSheet = ThisWorkbook.Worksheets(sheetXName) If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _ xFirstClassRow Then 'no classes on SheetX, do nothing Set xSheet = Nothing Exit Sub End If Set xSheetClassList = xSheet.Range(xClassColumn & _ xFirstClassRow & ":" & _ xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address) offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _ Range(xClassColumn & 1).Column Set zBaseCell = Range(zClassColumn & zFirstClassRow) zRow = 0 For Each anyxSheetClass In xSheetClassList If anyxSheetClass.Offset(0, _ offsetToStudentCount) 0 Then zBaseCell.Offset(zRow, 0) = anyxSheetClass zBaseCell.Offset(zRow, offsetToStudentCount) = _ anyxSheetClass.Offset(0, offsetToStudentCount) zRow = zRow + 1 End If Next 'housekeeping Set xSheetClassList = Nothing Set xSheet = Nothing Set zBaseCell = Nothing End Sub "apache007" wrote: Dear Excel experts I have a workbook with Sheet X and Z. On Sheet X, there are these coloums Class Number of Students Male Female AA 10 3 7 YY ZZ 0 NN OO 20 5 15 (More data at a later date) How do I have Excel 2003 fill data on Sheet Z, so that it will Print out those Classes that have number of students (ignore zeros and null)? Result: Class Number of Students AA 10 OO 20 Please remember that Sheet Z will be added with more data as time goes by and I want SHeet Z be able to keep spitting out Classes that have students. Thank you for your advice. |
Thread Tools | |
Display Modes | |
|
|