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]

I don't see why you're encountering problems, it works fine for me.

ActiveCell.FormulaR1C1 = _
    "=IF(AND(Table" & n & "[@Debits]="""",Table" & n & "[@Credits]=""""),"""",IF(Table" & n & "[@Debits]<>"""",R[-1]C-Table" & n & "[@Debits],R[-1]C+TTable" & n & "[@Credits]))"

edit:- if the formula is showing an error in the sheet then you might need to use Formula2R1C1 instead of FormulaR1C1 in order to resolve the problem. This is something that came up in another thread recently and may be the cause of the problem here depending on your excel version.

 

[MeisterConrad]

I don't see why you're encountering problems, it works fine for me.

ActiveCell.FormulaR1C1 = _
    "=IF(AND(Table" & n & "[@Debits]="""",Table" & n & "[@Credits]=""""),"""",IF(Table" & n & "[@Debits]<>"""",R[-1]C-Table" & n & "[@Debits],R[-1]C+TTable" & n & "[@Credits]))"

edit:- if the formula is showing an error in the sheet then you might need to use Formula2R1C1 instead of FormulaR1C1 in order to resolve the problem. This is something that came up in another thread recently and may be the cause of the problem here depending on your excel version.

Y'know what? I had " & 4 & ", when it should have been " & n & ". I guess it's official, now. I'm an idiot. Though I wouldn't've seen it without your code for comparison. Thanks!
But I did notice that yours has [@Debits], where mine has [[#This Row],[Debits]]. What's the difference? What does @ do? What does it mean? curious.
Here's a different one I'm having trouble with that doesn't have any typos in it, but I'm wondering if it needs brackets anywhere, or if I need to list the Sheet that AccountNameList is on. Do I need it to have [[Sheet2][AccountNameList(n-3)]]?

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

Thanks for the help.

 

[jasonb75]

I did notice that yours has [@Debits], where mine has [[#This Row],[Debits]]. What's the difference?

There is no difference, it's just much shorter and (in my opinion) easier to write. Both methods do the same thing. If I enter a formula in vba using [#This Row] then it automatically changes to [@ when the formula is entered into the sheet anyway. This may vary by excel version but I would think unlikely.

What trouble are you having with the second line in your reply? There is nothing that stands out as being wrong. What does AccountNameList refer to?
Depending on what it actually is and the value of n-3, you could likely be looking at runtime errors.

 

[MeisterConrad]

At that point in the code, n is still = 3, so n-3=0; 0 should be the first item in the list titled AccountNameList. This list is the column adjacent to the LedgerLocList from earlier. the error keeps popping up at AccountNameList, so I assumed I'm not following protocol somehow - missing quotes or brackets or something. I don't get it. - Or should it be n-2? Then it would refer to AccountNameList(1). But I thought I remembered that 0 is the first item in a list. Am I mistaken?

 

[jasonb75]

So AccountNameList is a named range rather than a table name?

Range("D9:N9").Value = "Click here to data-sort this " & Range("AccountNameList")(n - 2) & " Ledger."

But I thought I remembered that 0 is the first item in a list. Am I mistaken?

In vba, ranges start at 1, arrays start at 0 by default but can be declared to start at 1.
Worksheet formulas (INDEX function) are different in that 0 can be used to return an entire row / column of a range / array but the first item in the list will always be 1.

 

[MeisterConrad]

Yes - AccountNameList is a named range. It is also occupies a column in a Table, but it is its own named range.
I will try the n-2=1 tonight when I get home from work.
Thanks for your help. I really appreciate it.

 

[MeisterConrad]

Yes - AccountNameList is a named range. It is also occupies a column in a Table, but it is its own named range.
I will try the n-2=1 tonight when I get home from work.
Thanks for your help. I really appreciate it.

So, your solution worked! Yay!... except here's the thing: what I need is something more like:

Range("D9:N9").Value = "=CONCATENATE("Click here to data-sort this ", & Range("AccountNameList")(n - 2) & ," Ledger.")

...So that the cell is populated with a formula; not the actual data.
But I don't think you can put code into a formula. So, how do I reference the cells in that named range, one at a time, and in succession so that the cell reference bumps down on that namedrangeList every time the loop cycles?
Did I say that right?

 

[jasonb75]

I get what you mean now. Noting that the formula is being applied to multiple columns in each loop, should the exact same formula be entered into all columns?

This is how you would write the same line so that it is entered into the cell as a formula, although it may still need a bit of fine tuning.

Range("D9:N9").Formula2 = "=""Click here to data-sort this ""&" & Range("AccountNameList")(n - 2).Address & "&"" Ledger."""

 

[MeisterConrad]

Yeah, I see what you mean. That is quite meta. -And the .Address part is pretty slick. However, like you said, it needs fine tuning. I've tried a few different variations, but none seem to hit the mark. Thought that maybe it was getting hung up by the ""s around "AccountNameList", so I substituted a variable (ANList) in its place. Then did the same thing for the (n-2); b = n-2. When I swapped in these variables, I got different error messages that seemed to have been cured by the swap. The variations I've tried are below. The last one is the closest I've gotten (I think). Interestingly, when I put Range("D9:N9").Formula, the error that pops up is syntax error. But if I put Range("D9:N9").Formula2, the error that pops up is Expected list separator or ).

' Range("D9:N9").Formula = "="Click here to data-sort this "&" & Range("AccountNameList")(n - 2).Address & "&" Ledger.""
' Range("D9:N9").Formula = "=""Click here to data-sort this "" & Range.ANList(n - 2).Address & "" Ledger."""
' Range("D9:N9").Formula = "=""Click here to data-sort this "" & Range(ANList)(n - 2).Address & "" Ledger."""
' Range("D9:N9").Formula = "=CONCATENATE("Click here to data-sort this "," & Range("AccountNameList")(n - 2).Address & "," Ledger.")"
' Range ("D9:N9).Formula2 = " = CONCATENATE("Click here to data-sort this ", " & Range(ANList)(b).Address & ", " Ledger")""
Range ("D9:N9).Formula2 = " = CONCATENATE("Click here to data-sort this ", " & Range.ANList(b).Address & ", " Ledger")"

I'm swimming with unknowns and possible fixes. I don't know which direction to turn. It feels so close, yet so far away.