Solved

Code to Increment/Decrement Reference Values in Formulas

Posted on 2010-08-26
14
2,154 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

733 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