Databases
StackOverflow2013
Postsdbo
POExcel VBA how to get address delta of cell(s) not used between two separate non-congruent adjoining cells
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA how to get address delta of cell(s) not used between two separate non-congruent adjoining cells
    primarykey
    data
    text
    <p>I'm going to struggle in finding a way to describe what I'm trying to accomplish...</p> <p>I have a matrix of data that I'm updating by adding 'n' number of rows based on a unique key (date) from another worksheet. I've already implemented the copying of the key over to the matrix. Now, I want to copy the last used row, which contains formulas, over to the empty rows associated with updated 'n' key(s).</p> <p>So, my question is, how do I use VBA to determine the number of rows that are empty between what was last used and what I just pasted?</p> <p>I can brute force it IE I can get the address of the row I just copied data to and separate the letter from the number coordinate. Do the same for the last used row of the data that was last entered, subtract the numbers and get my delta. However, that seems to really be brute forcing that is quite unnecessary.</p> <pre><code> Date | col1 | col2 | col3 | col4 | col5 | Total | </code></pre> <hr> <pre><code> 2-Jan | 0 | 1 | 1 | 0 | 0 | 2 | 3-Jan | 2 | 0 | 4 | 1 | 0 | 7 | 5-Jan | 0 | 0 | 0 | 1 | 1 | 2 | 8-Jan | X 10-Jan | 13-Jan | Y </code></pre> <p>So looking at the matrix above, Jan 8 through Jan 13 are new inserts. I'm trying to determine the cells from X to Y. Once I've figured that out, I will paste the formulas that are in col1-total into the rows correlating with X through Y.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJim
    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. COIt seems to me it should be pretty simple, but maybe I'm missing something... You know what row you started with (`8-Jan`) when you started inserting, and you know how many rows you inserted (because you did so). All you need is the column for the `Total`, and you can use a range to copy the formula. For instance, if `Date` is `A1`, and `Total` is `G1`, you know when inserting the new rows that you're starting with `A5` and adding 3 rows, so you'll need to copy the formula from `G4` into `G5..G7`. There isn't any calculation involved, other than adding the number of rows added to starting row.
      singulars
      1. POExcel VBA how to get address delta of cell(s) not used between two separate non-congruent adjoining cells
      1. USKen White
    2. COThe row I know I started with is not 8-Jan but 5-Jan. There's a part I didn't mention because I didn't think it was really important to be known. The way the dates get added is not as straight forward as one would think. They get added from a source where there are multiple dates of the same day. When they get copied into this table, they get sorted and uniqued thus the source there could be twenty dates that then get truncated down to just three (in other words, the source could have three 8-Jans and six 10-Jans, etc). So I don't have a source of a # of new rows.
      singulars
      1. POExcel VBA how to get address delta of cell(s) not used between two separate non-congruent adjoining cells
      1. USJim
    3. COWhat I know is that I have the next empty row in col1 and the last used row in the date column. what I'm wondering is if there's a way to get a delta of the rows in between. Very poorly worded but hopefully my point is made?
      singulars
      1. POExcel VBA how to get address delta of cell(s) not used between two separate non-congruent adjoining cells
      1. USJim
 

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