|
VBA - Identify Begin/End of Cell Ranges
Group, I have a spreadsheet that changes weekly. The spreadsheet can have over a 1000 rows or just a few hundred. In column D, a cell may be blank or if there is an entry will have a text value with a suffix ..checksum, like regioncode.checksum or rds.checksum. The prefix before the (.) dot will always be lumped together once there is an entry. In otherword, rds.checksum will never appear again after cell D7 once its initial incidence (D3) is found, regioncode.checksum will never appear again after D20 once its first incidence (D11) is found below. I want to show on another worksheet called "Summary", the name of the variable (ie: rds.checksum) in cell A2, its starting row (3) in cell B2, its ending row (7) in cell C2 and so on for each group of .checksum entries. Any help here would be awesome. Cell:Contents D1: < Blank D2: < Blank D3: rds.checksum D4: rds.checksum D5: rds.checksum D6: rds.checksum D7: rds.checksum D8: < Blank D9: < Blank D10: < Blank D11: regioncode.checksum D12: regioncode.checksum D13: regioncode.checksum D14: regioncode.checksum D15: regioncode.checksum D16: regioncode.checksum D17: regioncode.checksum D18: regioncode.checksum D19 regioncode.checksum D20: regioncode.checksum D21: < Blank D22: < Blank D23: < Blank D24: < Blank etc........... Tony -- ajocius ------------------------------------------------------------------------ ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695 View this thread: http://www.excelforum.com/showthread...hreadid=470579 |
VBA - Identify Begin/End of Cell Ranges
will you have a list of the unique checksums in the summary sheet and you
want formulas to calculate the locations, or do you want a macro to build the entire summary sheet include building the list of unique checksums? -- Regards, Tom Ogilvy "ajocius" wrote in message ... Group, I have a spreadsheet that changes weekly. The spreadsheet can have over a 1000 rows or just a few hundred. In column D, a cell may be blank or if there is an entry will have a text value with a suffix checksum, like regioncode.checksum or rds.checksum. The prefix before the (.) dot will always be lumped together once there is an entry. In otherword, rds.checksum will never appear again after cell D7 once its initial incidence (D3) is found, regioncode.checksum will never appear again after D20 once its first incidence (D11) is found below. I want to show on another worksheet called "Summary", the name of the variable (ie: rds.checksum) in cell A2, its starting row (3) in cell B2, its ending row (7) in cell C2 and so on for each group of .checksum entries. Any help here would be awesome. Cell:Contents D1: < Blank D2: < Blank D3: rds.checksum D4: rds.checksum D5: rds.checksum D6: rds.checksum D7: rds.checksum D8: < Blank D9: < Blank D10: < Blank D11: regioncode.checksum D12: regioncode.checksum D13: regioncode.checksum D14: regioncode.checksum D15: regioncode.checksum D16: regioncode.checksum D17: regioncode.checksum D18: regioncode.checksum D19 regioncode.checksum D20: regioncode.checksum D21: < Blank D22: < Blank D23: < Blank D24: < Blank etc........... Tony -- ajocius ------------------------------------------------------------------------ ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695 View this thread: http://www.excelforum.com/showthread...hreadid=470579 |
VBA - Identify Begin/End of Cell Ranges
Try this. Change Sheet1 to suit your source sheet name.
Sub findvariable() rd = 2 For r = 2 To 1000 If Worksheets("Sheet1").Cells(r - 1, 4) = "" And Worksheets("Sheet1").Cells(r, 4) < "" Then Worksheets("Summary").Cells(rd, 1).Value = Worksheets("Sheet1").Cells(r, 4).Value Worksheets("Summary").Cells(rd, 2) = Worksheets("Sheet1").Cells(r, 4).Address End If If Worksheets("Sheet1").Cells(r - 1, 4) < "" And Worksheets("Sheet1").Cells(r, 4) = "" Then Worksheets("Summary").Cells(rd, 3) = Worksheets("Sheet1").Cells(r, 4).Address rd = rd + 1 End If Next r End Sub -- Ian -- "ajocius" wrote in message ... Group, I have a spreadsheet that changes weekly. The spreadsheet can have over a 1000 rows or just a few hundred. In column D, a cell may be blank or if there is an entry will have a text value with a suffix .checksum, like regioncode.checksum or rds.checksum. The prefix before the (.) dot will always be lumped together once there is an entry. In otherword, rds.checksum will never appear again after cell D7 once its initial incidence (D3) is found, regioncode.checksum will never appear again after D20 once its first incidence (D11) is found below. I want to show on another worksheet called "Summary", the name of the variable (ie: rds.checksum) in cell A2, its starting row (3) in cell B2, its ending row (7) in cell C2 and so on for each group of .checksum entries. Any help here would be awesome. Cell:Contents D1: < Blank D2: < Blank D3: rds.checksum D4: rds.checksum D5: rds.checksum D6: rds.checksum D7: rds.checksum D8: < Blank D9: < Blank D10: < Blank D11: regioncode.checksum D12: regioncode.checksum D13: regioncode.checksum D14: regioncode.checksum D15: regioncode.checksum D16: regioncode.checksum D17: regioncode.checksum D18: regioncode.checksum D19 regioncode.checksum D20: regioncode.checksum D21: < Blank D22: < Blank D23: < Blank D24: < Blank etc........... Tony -- ajocius ------------------------------------------------------------------------ ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695 View this thread: http://www.excelforum.com/showthread...hreadid=470579 |
VBA - Identify Begin/End of Cell Ranges
Tom, I just need a list built with the unique name, and beginning/ending rows. Everything else is done. What makes this problem so difficult for me is that the prefix to the .checksum can be different from Worksheet to Worksheet. Fortunately, the group of something.checksum will always be unique in the spreadsheet and will be listed consecutively. I hope that explains it. Tony -- ajocius ------------------------------------------------------------------------ ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695 View this thread: http://www.excelforum.com/showthread...hreadid=470579 |
VBA - Identify Begin/End of Cell Ranges
Probably the best way to describe this is to do a print screen of portion of the spreadsheet. In the screen print, I want to know th range from D1 through D19 Cal_Align_EE.checksum and though you can' see all of Cal_EE.checksum its range. The number of ranges fro spreadsheet to spreadsheet can vary from just a few to several dozen. Note that the names can change from spreadsheet to spreadsheet in th prefix. The suffix post decimal is always the same (checksum). Th result I'm searching for would look like below: A B C 1 Cal_EE_Align.checksum D1 D19 2 Cal_EE.checksum D23 D25 3 Something.checksum D45 D55 4 Somethingagain.checksum D132 D199 5 ..........etc Remember that the ranges will be different from spreadsheet t spreadsheet and the names in column A will be different fro spreadsheet to spreadsheet. The only common thing from checksum grou to checksum group is the .checksum suffix. Is this a little clearer. Many thanks if you can assist me on thi one. Ton +------------------------------------------------------------------- |Filename: PrintScreenOfProblem.gif |Download: http://www.excelforum.com/attachment.php?postid=3850 +------------------------------------------------------------------- -- ajociu ----------------------------------------------------------------------- ajocius's Profile: http://www.excelforum.com/member.php...fo&userid=1769 View this thread: http://www.excelforum.com/showthread.php?threadid=47057 |
VBA - Identify Begin/End of Cell Ranges
This article is about building a unique list of items and placing them in a
listbox. You can alter this to place them in a worksheet (and otherwise modify the code to ignore blank cells in you "AllCell" range). If you can't figure out how to use it and Ian suggestion doesn't help, then post back. -- Regards, Tom Ogilvy "ajocius" wrote in message ... Probably the best way to describe this is to do a print screen of a portion of the spreadsheet. In the screen print, I want to know the range from D1 through D19 Cal_Align_EE.checksum and though you can't see all of Cal_EE.checksum its range. The number of ranges from spreadsheet to spreadsheet can vary from just a few to several dozen. Note that the names can change from spreadsheet to spreadsheet in the prefix. The suffix post decimal is always the same (checksum). The result I'm searching for would look like below: A B C 1 Cal_EE_Align.checksum D1 D19 2 Cal_EE.checksum D23 D25 3 Something.checksum D45 D55 4 Somethingagain.checksum D132 D199 5 ..........etc Remember that the ranges will be different from spreadsheet to spreadsheet and the names in column A will be different from spreadsheet to spreadsheet. The only common thing from checksum group to checksum group is the .checksum suffix. Is this a little clearer. Many thanks if you can assist me on this one. Tony +-------------------------------------------------------------------+ |Filename: PrintScreenOfProblem.gif | |Download: http://www.excelforum.com/attachment.php?postid=3850 | +-------------------------------------------------------------------+ -- ajocius ------------------------------------------------------------------------ ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695 View this thread: http://www.excelforum.com/showthread...hreadid=470579 |
VBA - Identify Begin/End of Cell Ranges
Tom
Do you have a link for <<This article is about building a unique list Many thanks |
| All times are GMT +1. The time now is 01:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com