Databases
StackOverflow2013
Postsdbo
POVBA Array Manipulation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA Array Manipulation
    primarykey
    data
    text
    <p>I found a macro (courtesy of Jerry Beaucaire) that splits up one worksheet into many based on unique values in a given column. This works great. However...</p> <p>The client has supplied a differently formatted worksheet which needs some gentle massaging to get into the format we need.</p> <p>First, let me show you a snippet of JB's code:</p> <pre><code>MyArr = Application.WorksheetFunction.Transpose _ (ws.Columns(iCol).SpecialCells(xlCellTypeConstants)) </code></pre> <p>From what I can tell (and I'm a total VB newbie, so what do I know..??), this populates an array with the selected row values</p> <p>And this:</p> <pre><code>For Itm = 2 To UBound(MyArr) ...(code removed) ws.Range("A" &amp; TitleRow &amp; ":A" &amp; LR).EntireRow.Copy _ Sheets(MyArr(Itm) &amp; "").Range("A1") ...(code removed) Next Itm </code></pre> <p>...seems to do the copying.</p> <p>Alright. ...fine so far.</p> <p>The problem is that I need to add a step to the process. This will be tricky to explain. Please bear with me...</p> <p>Title row is row 1</p> <p>Data starts in row 2</p> <p>Each row has 9 columns:</p> <p>colA: identifier</p> <p>colB-colD: x,y,z values (for top of item)</p> <p>colE-colG: x,y,z values (for bottom of item)</p> <p>colH and colI: can be ignored</p> <p>These x,y and z values are used to define points that are used to plot lines in a 3D modelling program. Each row in the worksheet actually defines a line (well... a start point and an end point - "top" and "bottom") Unfortunately, the data(worksheet) we have received defines two sets of data for each line - both having the same start point, but with different end points. Put another way, starting with rows 3 and 4, the data in columns B-D is the same for both rows. This applies to rows 5 &amp; 6, 7 &amp; 8, etc.</p> <p>Since all we need are a set of data POINTS, we can safely use the values from cols E-G.<br> HOWEVER... and this is where I need help... We need the first row of the newly created worksheet to start with the values from row 2, cols B-D. (ie. we can use the end points as our coordinates, but we still need the first start point) All the rest is fine the way it is.</p> <p>For example:</p> <p>Source Data:</p> <pre> | A | B | C | D | E | F | G | 1 | id | x-top | y-top | z-top | x-bottom | y-bottom | z-bottom | 2 | H1 | 101.2 | 0.525 | 54.25 | 110.25 | 0.625 | 56.75 | 3 | H1 | 110.25| 0.625 | 56.75 | 121.35 | 2.125 | 62.65 | 4 | H1 | 110.25| 0.625 | 56.75 | 134.85 | 3.725 | 64.125 | B,C,D same as row 3 5 | H1 | 134.85| 3.725 | 64.125| 141.25 | 4.225 | 66.75 | 6 | H1 | 134.85| 3.725 | 64.125| 148.85 | 5.355 | 69.85 | B,C,D same as row 5 </pre> <p>What I need:</p> <pre> | A | B | C | D | E | F | G | 1 | id | x-top | y-top | z-top | x-bottom | y-bottom | z-bottom | 2 | H1 | | | | 101.2 | 0.525 | 54.25 | 3 | H1 | 101.2 | 0.525 | 54.25 | 110.25 | 0.625 | 56.75 | 4 | H1 | 110.25| 0.625 | 56.75 | 121.35 | 2.125 | 62.65 | 5 | H1 | 110.25| 0.625 | 56.75 | 134.85 | 3.725 | 64.125 | 6 | H1 | 134.85| 3.725 | 64.125| 141.25 | 4.225 | 66.75 | 7 | H1 | 134.85| 3.725 | 64.125| 148.85 | 5.355 | 69.85 | </pre> <p>So... What's the best way to do this? Can I add to the existing macro to perform this operation? If so, better to modify the array? ...better to modify the Copy routine? ...and how??</p> <p>Thanks in advance for your help and please don't suggest doing it manually. There are 70,000+ rows to parse!</p> <p>If you need more info, let me know!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBart
    1. USPrpl_Ppl_Etr
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan you confirm that all you need is to insert a row after the header row and shift the values from B-D to E-G?
      singulars
      1. POVBA Array Manipulation
      1. USRachel Hettinger
    2. CO"From what I can tell (and I'm a total VB newbie, so what do I know..??)" Set a breakpoint on that line, set a watch on that array, and step through your code in debug mode. Then you will know.
      singulars
      1. POVBA Array Manipulation
      1. USJean-François Corbett
    3. COYes, Rachel, I need to insert a data row after the header row. The data that would then be in row 3 (the first data row with values - having moved from row 2 due to the insertion) columns B-D, should be copied to row 2 (the new, blank row) columns E-G. Since VB is still Greek to me, I'm just not sure if it would be easier to do this when the arrays are built, after they are built (but before the new worksheets are created) or after the data has been added to the new worksheet. ...oh, right... AND how to do it. ;)
      singulars
      1. POVBA Array Manipulation
      1. USPrpl_Ppl_Etr
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload