How do you add a fixed number to a variable cell reference?

What I'd like to do is add 16 to the cell reference. What makes it tricky is I don't know where the cell will be until the macro runs. The macro will add rows and I'm using the "find" method to figure out the row to start adding the 16.

When I get there I want to add 16 to the cell address for each cell in the row.

For example, right now in "B370" I have: "=B147". I want to change it to be "=B153" and need to do the same thing for "C370".

How would you do this?

Like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Originally Posted by jolivanes

Sorry about that. Where does it say in your first post that you needed the value?

Please explain what you want to achieve. In your first post you mention inserting rows and in your last post you want values.
Do you want to add 16 to the address in the active cell, whichever the active cell might be?

"When I get there I want to add 16 to the cell address for each cell in the row."
Can you go a little deeper into the meaning of "for each cell in the row"

Sure. I'll try to do a better job explaining. I was trying to be careful with my terminology, but may have been off. In my first post I asked for help with the "cell reference" by which I mean that the cell refers to a value in another cell. So if you double click on the active cell right, which is "B370" I have: "=B147". I want to change it to be "=B153" and need to do the same thing for "C370".

I just quickly put together a quick example in excel with gibberish, but shows the basic formatting for what I'm trying to do that may help explain it.

If you look at the excel sheet I already have a macro where I ask for how many additional tenants and you can input any number. It then copies the format for the existing tenants like on the top part and will have any number there. I have a summary table at the bottom that needs to incorporate values from all the new tenants that are inserted based on the number the user enters. (assume 1 in this case)

So if you look at the example sheet I need to insert information about "tenant 3" between "tenant 2" and "total". All the tenants refer to names from up earlier in the sheet. So in this example I would need to insert a new row into row 26 and then set it equal to =$B$10 + 6 or $B$16.

On my actual sheet instead of adding 6 I'd need to add 16 to the value that referencing something earlier on the sheet.

My macro put the active cell on the equivalent of "B25" on my example sheet so I'm trying to work off of that.

Don't quote whole posts please. Just extra clutter we don't need and want. Refer to a posters name or post number if you want to reference.

The attached workbook is my take on it if I understand it right. If not, we'll start over.
The code does it all without having to change references to other cell values.
Copy the code into a copy of your original workbook and try it. Your original needs to have the same setup of course.

The output is what I'm looking for, but I'm not sure if the method would work. There are a lot of complicated formulas and ones that look at a big range. If I manually go through and add new tenants I have to go into the formula and change it so the array is expanded for the new ones.

I've attached an updated sheet with more detail. Take a look at this with what I wrote before and let me know if you have any questions.

First things first. You never answered if the suggestion from Post #6 worked. And if it did not solve your problem, what was wrong with it?

The output is what I'm looking for, but I'm not sure if the method would work. There are a lot of complicated formulas and ones that look at a big range and use a lookup formula.

When I manually go through and add new tenants I do 2 things for this chart.
1. Double click on the cell and manually "add 16" to the formula for the cell reference for the rows so each one is 16 more than the previous one. This is for the labels and what I was asking about in the OP.

2. Double click on the cells with the data and manually expand the range of the array. For example, part of the formula is this:

Formula:

Please Login or Register  to view this content.

From expanding number of tenants in the previous step it's not going to start with 168 and end with 324. So I look for the start and end of the area with the info. So I need to be able to find where the new start to the table is and change the $D$168 to wherever that is and the $NK$324 to whatever that is now. The start value is the start of the table and the end value is the end of the table.

Formula:

Please Login or Register  to view this content.

I've attached an updated sheet with more detail. Take a look at this with what I wrote before and let me know if you have any questions.

What if you want to change the formula in a cell?

For example

Formula:

Please Login or Register  to view this content.

You want change to:

Formula:

Please Login or Register  to view this content.

Re-enter the new formulae with code through references you know.
The current cell references, $D$168, $NK$324, are based on some cell value reference.
Use these same cell value references and add whatever amount to it.

It is easy enough to add formulae to a sheet but so far I have not seen a concrete mentioning of what it need to be and references of how the addresses of these formulae came to be.

Where I am with my code right now what I'd need to do is:

1. Insert new rows for each tenant
2. In the first column add 16 to the reference for what's currently in the cell 1 up from the active cell for each tenant. For example, right now starting with 3 cells above the active cell I have:

=$B$115
=$B$131
=$B$147

These reference information about the tenant that will change and when I add new tenants it adds them to this table (Let's call this Table A) so the table at the end (Let's call this Table C) needs to refer to the new location. So in the new rows it should show if I had 2 tenants, for example

=$B$163
=$B$179

I don't want to just paste the value that's currently in cell $B$163, since you should be able to change it later and the value in the table should change.

There is something similar in column C with the same kind of spacing.

After that there is a complex formula with multiple references to the information in the 2nd table (Let's call it Table B" that draws from Table A. Table B moves according to how many new tenants are added, but the reference to the start and end of the table continues to work since it has the 2 $ and is fixed. What I need to do in this Table C is add "16" to the reference for the end of the table.

For example, let's say the formula starts like this:

Formula:

Please Login or Register  to view this content.

The $D$216 is the start of the table and the $NK$372 is the end of the table. What I need to do is add 16 for each new tenant so that the formula will search the expanded table that includes the new tenants. So normally what I do is look and if there are 2 new tenants I will highlight the cells with the formula and change "372" to "404".

So with VBA could you just highlight all the data in Table C (besides columns B and C) and replace the 372 with 16*number of tenants?

Does that make sense?

This changes the formulae in Column C from C2 to C82 every 16 Rows.

Please Login or Register  to view this content.

Do this for all your formulae. Once when it is done in code all you do is run the macro.
No more highlighting changing for hours on end.

Thanks! Couple questions on your code.

1. What kind of variable is 1r? I have it set so you have to declare variables. Is it an integer or something else?
2. Is the basic logic that you count how many rows there are in the "NK" column? I don't want the total number of rows in that column, just the number in table B. I can search for the start since there is a unique string at the start of the table. The end of the table would be want the 1st line of your code counts, correct?
3. Inside the loop I'd just change the reference to the table to :

Please Login or Register  to view this content.

Seems like we're getting close!

lr is Long.
No, lr is the last used cell in Column NK, or whatever Column you use in the declaration.
I don't recall it having been a table so you might need to adjust for that.

I think I've almost got it to work using some of your code and logic.

The only issue I'm having is getting the formula to work. With the formula how do you handle the quotes with the variable?

Formula:

Please Login or Register  to view this content.

I'm getting an error message saying "type mismatch" or "syntax error" so I must be doing something wrong. I have the whole formula in quotes, but what's the proper syntax with the "&" as well as the lr variable"? I've been playing with different combinations and can't get it to work. I checked and "lr" shows the right row number.

I am sorry but I will be without internet right away for the next few days (travelling) so someone else should be able to join and help you with this.

Safe travels! I really appreciate your help so far.

This is just basic syntax at this point so hopefully someone else will be able to help. I'm also going to keep trying to figure it out myself. I feel like I'm so close to being over the finish line here.

Re: With the formula how do you handle the quotes with the variable?
Which quotations?

You can use it like this also:

Please Login or Register  to view this content.

I use RC because it makes it so much easier.