Table Formula Reference using a counter?

MeisterConrad

New Member
Joined
Jan 17, 2017
Messages
42
Office Version
  1. 2007
I have a Macro that add a bunch of worksheets, and creates a table, whose name is numbered using a counter (n) on each of those sheets. I want to have the code input formulas into two columns of the table that refer to places in the table that was just created.

VBA Code:
Dim c As Range
Dim n As Integer

Dim lo As ListObject
 n = 3

For Each c In Sheets("Sheet1").Range("LedgerLocList")
Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value

      n = n + 1
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$D$10:$N$12"), , xlYes).Name = _Table" & n
        Set lo = Range("Table" & n).ListObject

 Input Formulae for Balance Columns
   Range("M12").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(Table4[[#This Row],[Debits]]="""",Table4[[#This Row],[Credits]]=""""),"""",IF(Table4[[#This Row],[Debits]]<>"""",R[-1]C-Table4[[#This Row],[Debits]],R[-1]C+Table4[[#This Row],[Credits]]))"
    Range("N12").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(Table4[[#This Row],[Credits]]="""",Table4[[#This Row],[Debits]]=""""),"""",IF(Table4[[#This Row],[Date Cleared]]="""",R[-1]C,IF(Table4[[#This Row],[Debits]]<>"""",R[-1]C-Table4[[#This Row],[Debits]],R[-1]C+Table4[[#This Row],[Credits]])))"

I tried using "Table" & n instead of "Table4", but that won't translate somehow. Should I replace all the instances of "Table4" with "lo" (the ListObject)? what would be the correct syntax? I need to have this name/reference dilemma solved in order for subsequent Sheets to be created correctly. What should I do?
 
Forget vba for a second, could you post the formula that you want in D9, I think that I have enough info from the other posts to write the line of code you need from that.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Have you got what you need now, or did you misread my last reply?

What i meant was that if you post the formula that you want in the cell (using a1, b1 etc instead of the name range) then i can convert that to vba with the name that you need as a variable.

It might be a good idea if you post it for a couple of different cells in the range, along with the actual range of the name that is being used so that i can see how they relate.

One final question, something that i didn't think of earlier. Is the formula going into the the sheet where the named range is found? If they are on different sheets then the sheet name will be needed as well.
 
Upvote 0
Thanks for the follow-through; I DID misread your last reply.

I was kind of thinking the same thing as you. I came up with:

VBA Code:
Dim b As Range
b = Sheets("AccountInfoSheet").Range("E4:E23")
       n = n + 1
' So at this point, n=4, which makes n-3=1.  This makes (or is supposed to make) the next line refer to the first item in the range E4:E23, 
' and insert the cell address for that item into the formula.  
' The formula gets input into the merged cell D9:N9.
Range ("D9:N9).Formula = " = CONCATENATE("Click here to data-sort this ", "&Range(b)(n-3).Address&", " Ledger")"

So, the loop of which this is a small part, includes the creation of a new worksheet, onto which is built the LedgerTables. The first of these LedgerTables happens to be Table4; that's why the counter n is initially set to 3. The named range "AccountInfoSheet" is on its own worksheet, occupying cells E4 through E23. So, coincidentally, the Table# and the row# in column E happen to be the same throughout the loopcycles. I tried using column E & the number n in the range, but something was wrong with it and I can't remember now what that was. Anyways, that's why I went with the variable b in the code above.

At any rate, here's a couple versions of the formula:
first loopcycle:
= CONCATENATE("Click here to data-sort this ",E4, " Ledger.")
2nd loopcycle:
= CONCATENATE("Click here to data-sort this ",E5, " Ledger.")
3rd: ...E6
and so on...E23.

I'm not stuck on keeping it a named range; cell address would work for me. Thanks again.
 
Upvote 0
The named range "AccountInfoSheet" is on its own worksheet, occupying cells E4 through E23.
If that is not the sheet where the formulas is going then that will explain why it is not working correctly. When we use .Address in vba, it only gives E4, E5, etc. Not AccountinfoSheet!E4, etc.

Taking a slightly different approach, see if this one works. I haven't had chance to test it so hopefully I have the double quotes in the right places.
VBA Code:
Range ("D9:N9).Formula = "=""Click here to data-sort this ""&INDEX(AccountNameList," & (n-3) &")&"" Ledger"""
 
Upvote 0
Solution
So, I tried your suggestion, and ran into another syntax error.
And in trying to figure out what the error might be, it hit me.
Guess what I did.
I made a couple of dummy entries into cells on a different sheet to hold the two pieces of text.
Then, on the sheet that the loop creates, I entered:
VBA Code:
Range("A2").Formula = "=AccountInfoSheet!E" & n

Then, in the D9:N9 range, I simply concatenated the three various cells.

VBA Code:
Range("D9:N9").FormulaR1C1 = "=CONCATENATE(TablesAndListsSheet!R[45]C[7],Account" & (n - 3) & "Sheet!R[-7]C[-3],TablesAndListsSheet!R[46]C[7])"

Voila! It works!
It may be the long way around, but this whole code is being written to make all the entries so I don't have to.
I wouldn't've been able to discover this solution without our exchanges back and forth; it has really challenged me to look at different approaches to problems.
Thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top