R1C1 keep reference cell fixed in copy

[ctosborne89]

Hello,

I am having trouble with a script that is designed to normalize days from a starting value. Say column A rows 1:10 contain values 730-740. Column B should subtract A1 from each cell and then copy down to give values since value 1. (so Column B would read 0, 1, 2 ... 10.)

I understand the notation of R1C1, and I know how I could subtract the cells simply with a fixed reference to R1C1.

Please Login or Register  to view this content.

But I am having trouble with the fact that this script needs to be started at any starting point in the sheet. So maybe the 730-740 data in Column A doesn't start at Row 1. Maybe it starts at Row 50, or just row {ROWX}.

I need the resulting code to be

Please Login or Register  to view this content.

.

I can, of course, get this to work for the one cell, but it needs to keep referencing R{ROWX}C[-1] when I autofill rows ROWX+1 through ROWX+9.

I hope this explains the issue. I need to autofill rows to subtract themselves

Please see attachment and ask questions.

[JosephP]

concatenate the row

Please Login or Register  to view this content.

[ctosborne89]

Thanks for your help!

I tried that, but I'm having the same issue with autofill. I want B10 to be (A10-A10) ,and B11 to be (A11-A10), but doing what you said autofills B11 to (A11-A11). Is there a way to lock in that row_num?

Thanks and sorry for not knowing a whole lot about VBA.

C

[JosephP]

row_num will not change unless you change it inside your loop which you should not do. it would save a lot of guessing if you posted all the code you are using ;-)

[ctosborne89]

Yeah. It's an enormous macro that someone wrote that I'm now just coming into. Here is a section that I am trying to work with.

Please Login or Register  to view this content.

It is not within a loop. I know it may be way messy and complicated. Whoever made it just used the Record Macro, which is probably the noob way of doing anything.

Thanks again.

[JosephP]

what exactly is row_num? is it a string with [] round a number?

you don't need to fill either

Please Login or Register  to view this content.

can replace what you have posted

[ctosborne89]

Copied and pasted that into what I had, but by what you posted, "row_num" now references A1, or A2, or A3, depending on where the formula is being input. row_num needs to always reference A1, though "A1" moves around relatively.

Functionally, this is working exactly like RC[-1]-RC[-1] - Zeroes across the board.

[JosephP]

what exactly is row_num? is it a string with [] round a number?

have you actually assigned a value to row_num? I reckon not, in which case you are just getting RC[-1] and you should use

Please Login or Register  to view this content.

[ctosborne89]

excellent, excellent, excellent.

Thanks so much.

[Cutter]

@ ctosborne89

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.