Solved

Code to Increment/Decrement Reference Values in Formulas

Posted on 2010-08-26
14
2,025 Views
Last Modified: 2012-05-10
Experts,
I have a fairly large spreadsheet that, at times I need to add additional rows, or the formula needs to change, which causes the code to have rewritten.  When it was just a few rows, it was pretty easy enough, but now it's at the point where some code would really be helpful.

Each Row starts at C and ends at AU:
The best way I've found, with about 45 columns is to use the R1C1 method, although there may be one more efficient.  Since the formula is the same, except with the cell it refers to.  I can just copy the formula across the columns as a text string and then use Replace to make them all formulas. [The code is below with the anchor cells at C4 or R4C3, and C6, or R6C3

These are actually done in sets of six rows as there are three rows empty for other calculations.
Example:
Rows 8-13
Rows 17 - 22

Each sheet has a variable number of sets, because the number of projects associated with each is different.

What would be the best way to write some code that would loop through the rows and decrement the (R[-?]) down by one as it moves down to the next row, with the relative addressing?  Or, using absolute addressing, increment the column by 1, as it moves across the columns (C4:AU4) =  [R4C3:R4C47].

Thanks,

Cook


Cell 1 Start: A1 Notation



=SUMIFS(Data_PI[Encumbered],Data_PI[Accounting Period],C4,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],C6)



R1C1 Notation



=SUMIFS(Data_PI[Encumbered],Data_PI[Accounting Period],R4C3,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],R6C3)



----------------------------------------------------------------------

First Row Cell to Last Row Cell

Relative Formula for a single row: C8:AU8



=SUMIFS(Data_PI[Encumbered],Data_PI[Accounting Period],R[-4]C,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],R[-2]C)



Next Row



=SUMIFS(Data_PI[Encumbered],Data_PI[Accounting Period],R[-5]C,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],R[-3]C)

---------------------------------------------------------------------------



Absolute Formula at Cell 1 (C8) to Last Row Cell (AU8)



=SUMIFS(Data_PI[Encumbered],Data_PI[Accounting Period],R4C3,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],R6C47)

Next Row:

Same as above

Open in new window

0
Comment
Question by:Cook09
  • 8
  • 5
14 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33536788
It might be possible to deduct your exact needs from your description, but I can't right now, a sample workbook would help enormously :-)
0
 
LVL 6

Expert Comment

by:steverice
ID: 33536987
ActiveCell.offset(0,1) moves the cursor one cell to the right
ActiveCell.offset(1,0) moves the cursor one cell down
(Use negative numbers to move up and left)

I'm not sure if this is what you're trying to achieve but i hope it helps.

 
0
 

Author Comment

by:Cook09
ID: 33537886
I hope this example helps. It provides the layout, what the numbers could look like and formulas in a couple of sets with two blank sets, although there are some that have several more than that.

Cook

PI-Sandbox.xlsm
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33537980
Have you tried to use mixed references?

=C4*C4

If you insert a row becomes
=C5*C5

But if you use
=C$4*C$4 it remains good when you insert a row, and you can still copy to the other columns.

If I understand well, this is your only issue?
0
 

Author Comment

by:Cook09
ID: 33537999
Yes, I have.
The problem is with the Table references.  I tried the = C4*C4, and yes it will become C5, but the Table references also change rendering it useless.

Cook
0
 

Author Comment

by:Cook09
ID: 33538003
Or I should say, i have tried them both ways to get it work, but it still comes down to the Table references.

Cook
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33538074
I think only somebody with Excel 2010 can help you, I'm still stuck to good old 2003.
And I still don't see your issue.

You copy C$4 down, it will still point to C$4, whereas if you copy C4 down it becomes C5 ...
I can't imagine Table references are not static when you copy them down.

Do you see the difference between C4, $C4 and C$4?
If yes, I apoligize and leave this to people with Excel 2010



0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Cook09
ID: 33541402
This is more of an Excel 2007/2010 issue, as that is what I use.  However, most are still not fully up to speed on the Table features and their unique ways of writing code using them.  

One would think that the Table syntax would remain static, but they don't.  Yes, the C4 will change to C5.

However, I just remember, as we correspond,  a statement from Patrick that maybe the $ could also be used for Table statements (another topic) to keep them absolute, but I have never read about that from MS or anyone else.  Maybe he's on to something.  I also haven't tried it, as it just came to mind.

 I'll  be out for a little while and try that when I can.  However, the concept of using VBA to generate code is still a topic I'm interested in knowing more about.  

Cook
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33542470
I had posted something (example of a macro to change increment) but there was a server error.
I'll try again. I still don't understand your issue though :-)

Sub Increment()
Dim mb As Workbook
Dim ms As Worksheet
Dim fStr As String
Dim r As Long
Set mb = ThisWorkbook
Set ms = mb.Worksheets("Sheet1")
r = InputBox("Please enter row where relative references need to be decremented by one row")
For i = 3 To 47
    fStr = ms.Cells(r, i).FormulaR1C1
    fStr = Replace(Replace(fStr, "R[-6]", "R[-7]"), "R[-4]", "R[-5]")
    ms.Cells(r, i).FormulaR1C1 = fStr
Next i
End Sub
0
 

Author Comment

by:Cook09
ID: 33569818
I'm sorry not to have responded prior to now, but my 10 wk old granddaughter contracted meningitis last Friday, so I took off to help with the situation.  Just got back into the office, but still not fully functional (mentally).

Within the next couple of hours I should have more feedback for you.

However, in the code below, you can see how the Excel Table Headings changed when I used the Fill Handle to copy the Formula from E18 to F18.  The E18 to F18 works as expected.  But, the Table Headings changed, throwing off the entire formula.

In the R1C1 notation, the same thing happens, although the R[-14]C and R[-12]C is constant between them both.  Which means if I copied E18 in R1C1 as a text string to all of the cells within that row of the Table (or all cells that had formulas) on row 18

#SUMIFS(Data_PI[Income],Data_PI[Accounting Period],R[-14]C,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],R[-12]C,Data_PI[Project],R16C2)

and then did a replace of # for =, then everything would align properly.  Although, as I move to another row, it changes (See code below).

If I move down to E27 the R[-14]C becomes R[-23]C and R[-21]C, or the 9 rows decrements the R[#(-9)]C by nine or one row = a decrement of -1.  

Example:
1. Copy / Paste into the cells for a particular row within the Table or CurrentRegion
2. Select the entire row within the Table/CurrentRegion (Ctrl+Shift+->)
3. Replace a -14  for a -23
4. Replace -12 to  -21
5. Move down 9 rows and repeat to end of the Table(variable).
6, At end of current Formula, start with the next Formula at R[-15] and R[-13] and perform same procedure.
Because these could go down a couple of hundred rows, to manually do the above gets to be very tedious and should be something that VBA should be able to assist with.  If you notice the spreadsheet that I posted there are six different SumIFs per section.  Each section references a unique project number (B16, B25, etc.).

My goal is to automate this so it doesn't take me anywhere from 2-6 hours, depending upon the number of researcher's [LAB_NAME] involved.  If R1C1 is not the way to go, I'm open to anything that will reduce my insert formula's time.

I hope this clarifies what I'm looking for, but I will test what you have provided.

Cook



'Code in E18 -- Notice the Table Headings (A1 mode)

=SUMIFS(Data_PI[Income],Data_PI[Accounting Period],E4,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],E6,Data_PI[Project],$B$16)



'Code in F18 - Again,notice the Table Headings (A1 mode)

=SUMIFS(Data_PI[Transfers&Adj],Data_PI[Free Balance],F4,Data_PI[DeptID],PI_Name,Data_PI[Balance Sept 1st],F6,Data_PI[LAB_NAME],$B$16)

-----------------------------------------------------------------------------------------



'Cell E18 in R1C1 Notation

=SUMIFS(Data_PI[Income],Data_PI[Accounting Period],R[-14]C,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],R[-12]C,Data_PI[Project],R16C2)



'Cell F18 in R1C1 Notation--The Table Headings also change

=SUMIFS(Data_PI[Transfers&Adj],Data_PI[Free Balance],R[-14]C,Data_PI[DeptID],PI_Name,Data_PI[Balance Sept 1st],R[-12]C,Data_PI[LAB_NAME],R16C2)

---------------------------------------------------------------------------------------------

'Cell E27 in A1 (Table Headings still change as above, so they are not shown)

=SUMIFS(Data_PI[Income],Data_PI[Accounting Period],E4,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],E6,Data_PI[Project],$B$25)



'Cell E27 in R1C1

=SUMIFS(Data_PI[Income],Data_PI[Accounting Period],R[-23]C,Data_PI[LAB_NAME],PI_Name,Data_PI[Fiscal  Year],R[-21]C,Data_PI[Project],R25C2)

Open in new window

0
 

Author Comment

by:Cook09
ID: 33572602
calacuccia,

The VBA that you wrote seems to work with a single row.  If I wanted the code to loop back to the next starting column and adjust the R factor an additional -1 ("[-6]",  "[-8]"...) and perform this looping five times, so that at the end it was substituting ("[-6]", "[-11]"...), how would that look?

Would it possibly look something like:
x = -6   'set for specific row
y = (value set in loop -- [-1 to -5] )

("[x]", "[x + y]"...

y = y - 1


Cook
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 500 total points
ID: 33575758
First of all, all the best to your Gran Daughter, I hope she is well by now.
I see that I will need Excel 2010, before I'll be able to fully understand this table stuff, but I'm not sure I want any of it :-)

To answer your VBA question:

You could simply nest it in a second (j) loop and use the value for j in the string formula.
If you have also the -5 to be incremented, there is a second example below.

For j = 7 to 11
   For i = 3 To 47
       fStr = ms.Cells(r, i).FormulaR1C1
       fStr = Replace(Replace(fStr, "R[-6]", "R[-" & j & "]"), "R[-4]", "R[-5]")
       ms.Cells(r, i).FormulaR1C1 = fStr
   Next i
Next j

For j = 7 to 11
   For i = 3 To 47
       fStr = ms.Cells(r, i).FormulaR1C1
       fStr = Replace(Replace(fStr, "R[-6]", "R[-" & j & "]"), "R[-4]", "R[-" & j-2 & "]")
       ms.Cells(r, i).FormulaR1C1 = fStr
   Next i
Next j


0
 

Author Comment

by:Cook09
ID: 33591877
calacuccia,

Below is the code that used in combination with yours.  I'll initially take a more moderated approach and manually step through it until I'm more comfortable with it.

I took what you did and put some automatic calculations into the mix.  For instance, regardless of the row, the code will calculate what the variable replacement value should be given a fixed reference point(s).

There was actually a third item that also needed replacing with each block, the Project reference in column B, so I added that in.

If you happen to know an easier or more efficient way of doing this I'd be most appreciative.

One quick question:  Is there a way to use wild-cards in an R1C1 Formula to make changes, or is that another question?  

While this works fine, to replace a R33C2 with a R50C2, if a wild-card of R*C2 or R??C2 would make it a little easier.  I'm thinking down the road when this will have to be modified.  In fact, even now.  If there was an entire section that needed some changes, I could modify any row or section and just use the wild-card to change the other values.

Yes, my granddaughter came home the other day, she's doing fine.  Thanks for asking.

Cook

Sub ReplaceR1Values()

Dim mb As Workbook

Dim ms As Worksheet

Dim sFmla As String

Dim iR As Integer, iC As Integer, j As Integer

Dim iAp As Integer, iFy As Integer

Set mb = ThisWorkbook

Set ms = mb.Worksheets("Bishop")



'Replace the Formula prefix with String/Text prefix. Minimize real-time issues



Range(Selection, Selection.End(xlToRight)).Select

    Selection.Replace What:="=", Replacement:="##", LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

        

iR = InputBox("Please enter row where relative references need to be decremented by one row")



For j = iR To (iR + 6)                'iR is for the Row Number in R1C1

    iAp = 4 - j                                 'Accounting Period is on row 4

    iFy = 6 - j                                  ' Fiscal Year is on row 6

   For iC = 3 To 47                      'iC is for the Column Number in R1C1

       sFmla = ms.Cells(j, i).FormulaR1C1

      sFmla = Replace(Replace(Replace(sFmla, "R[-6]", "R[" & iFy & "]"), "R[-4]", "R[" & iAp & "]"), "R4C2", "R" & iR - 1 & "C2")

     ms.Cells(j, i).FormulaR1C1 = sFmla

   Next iC

Next j



'Replace the String prefix with Formula prefix



Range(Selection, Selection.End(xlToRight)).Select

    Selection.Replace What:="##", Replacement:="=", LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False



End Sub

Open in new window

0
 

Author Closing Comment

by:Cook09
ID: 33641813
You provided me with enough information that it made it easy enough to finish out what was not provided.

Thanks,

Cook
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now