Array Value - Formula

[rcreek09]

I want to create a very basic table of contents sheet at the beginning of my workbook. Count the sheets, loop thru each sheet recording the tab name in an array. Go to the TOC sheet and write that tab names in column A. That's all; very simple. NOT.


I'm sure there's a better way to write to the TOC sheet, but for now my problem is that I'm not getting anything in my tabnames array!!! Have tried tabnames(ai).value=, tabnames(ai).formula=, etc. I keep getting "TOC" written 15 times on the TOC sheet.


Also, how to dim the array for an unknown number of tabs???


Any help is most appreciated. I'm sure it's something simple, but I'm in a downward mental spiral by now.


Thanks,

Deb


Sub tableofcontents()


Dim i As Integer, ai As Integer

Dim tabnames(1 To 1000) As String

i = Worksheets.Count


For i = 2 To Worksheets.Count

ai = i - 1

Sheets(i).Activate


tabnames(ai) = "=RIGHT(CELL(""filename"",R1C1),LEN(CELL(""filename"",R1C1))-FIND(""]"",CELL(""filename"",R1C1)))"


Next i


For i = 1 To Worksheets.Count - 1

Sheets("TOC").Activate

Cells(i, 1).Value = tabnames(i)

Next i

End Sub

 

[OleMiss2010]

I've had a similar issue before. I used a cell in the worksheet to name the worksheet and the reference, so (assuming the worksheet name matches Range("C5"), your TOC sheet is named "TOC", and you want the TOC to start in Range("A2") and continue from there):

[pre]

Dim CellCount As integer
Dim ws As WorkSheet, SelectionChanged As Boolean

Sheets("TOC").Select
Range("A1").Select

CellCount = 1
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "TOC" Then
If ws.Range("C5") <> "" And ws.Range("C5") <> "<>" Then
CellCount = CellCount + 1
.Hyperlinks.Add Anchor:=.Cells(CellCount, "A"), Address:="", _
SubAddress:="'" & ws.Name & "'!C5", _
TextToDisplay:=ws.Range("C5").Value
End If
End If
Next ws

[/pre]

 

[rcreek09]

Ok, have a workaround that actually works, but would really like to get it right.


This gets the job done, but. . .


Range("x2").Formula = "=RIGHT(CELL(""filename"",R1C1),LEN(CELL(""filename"",R1C1))-FIND(""]"",CELL(""filename"",R1C1)))"

tabnames(ai) = Range("x2").Value


It's using a 1000 sized array when not needed.

I'm putting the value in a cell, then from the cell into the array. I know that's not necessary.


Thanks!

 

[prasaddn]

Self deleting.. kindly ignore..

 

[Hui]

RCreek09


I think you'll find all your answers below


This is a simple TOC but doesn't use Arrays

===========================================

[pre]

Sub tableofcontents()

Dim i As Integer

For i = 2 To Worksheets.Count
Worksheets("TOC").Cells(i, 1).Value = Sheets(i).Name
Next

End Sub

This is more complex and uses Arrays

====================================

Sub tableofcontents()

Dim i As Integer
Dim tabnames() As Variant

ReDim Preserve tabnames(2 To Worksheets.Count, 1 To 1)

For i = 2 To Worksheets.Count
tabnames(i, 1) = Sheets(i).Name
Next

Worksheets("TOC").Range(Cells(2, 1), Cells(UBound(tabnames()), 1)) = tabnames()

End Sub

[/pre]

 

[rcreek09]

Excellent. It never occurred to me to use sheets.name instead of the ridiculous but practical formula that I use within the worksheet.


If you would care to elaborate on the ReDim statement, I would be very interested. If not, I'm just tickled to have an efficient answer.


Thanks so much.

Deb