Table Formula Reference using a counter?

[MeisterConrad]

I have a Macro that add a bunch of worksheets, and creates a table, whose name is numbered using a counter 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.

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?

 

[jasonb75]

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.

 

[MeisterConrad]

OK. Thanks for the help!

 

[jasonb75]

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.

 

[MeisterConrad]

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:

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.

 

[jasonb75]

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.

Range ("D9:N9).Formula = "=""Click here to data-sort this ""&INDEX(AccountNameList," & (n-3) &")&"" Ledger"""

 

[MeisterConrad]

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:

Range("A2").Formula = "=AccountInfoSheet!E" & n

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

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!