LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 168
Default Help with Index formula

On Dec 19, 8:33*am, XR8 Sprintless wrote:
Hi,

I'm currently trying to automate the addition of worksheets into a
spreadsheet and have a formula

=INDEX(Tables!$E$3:$E$28,$Q$3)

Which is located on the Home sheet and references the tables index and
returns the value dependent on the value in Q3 on the home sheet for the
sheet where the results are in row E of the tables sheet. There a
multiple sheets which have a table of results in the tables sheet and if
I need the result from another sheet the formula might be

=INDEX(Tables!$J$3:$J$28,$Q$3) for the sheet where the results are in
row J of Tables.

In trying to automate this i tried the following but it fails at the
activecell.formula = "INDEX(Tables!...) entry.

I'm only just starting to learn VBA so my coding is messy!
Is it possible to do this at all?

Here is the full macro so far.

Sub newplayer()
'
' New Player Macro
'

'
Dim sname As String

'Create new sheet with name of contestant unhide then hide the template
sheet
* * *sname = InputBox(Prompt:="Enter contestants name")
* * *Sheets("Sheet Template").Visible = True
* * *Sheets("Sheet Template").Copy After:=Worksheets(Worksheets.Count)
* * *ActiveWindow.ActiveSheet.Name = sname
* * *Sheets("Sheet Template").Visible = False

'Copy the sheet name to Cell A1
* * *Range("A1").Select
* * *ActiveCell.FormulaR1C1 = sname

'Goto the tables sheet and find the next blank column after row E
* * *Sheets("Tables").Select
* * *Dim r As Range
* * *If [F2].Formula = "" Then
* * *Set r = [F2]
* * *Else
* * *Set r = Range("E2").End(xlToRight).Offset(0, 1)
* * *End If

* * 'Put the sheet name in the first column the copy from the template
row E then fill the next 26 rows with the formula

* * *r.Select
* * *ActiveCell.Value = sname
* * *Range("E3").Select
* * *Selection.Copy
* * *r(2).Select
* * *ActiveSheet.Paste
* * *r(2).Select
* * *Selection.Copy
* * *For x = 1 To 25
* * *ActiveCell.Offset(1, 0).Select
* * *ActiveSheet.Paste
* * *Next x

* * *'Copy the formula from E29 to the column then add a formula to copy
results from the spreadsheet to the table and fill the next 26 rows
* * *Range("e29").Select
* * *Selection.Copy
* * *r(28).Select
* * *ActiveSheet.Paste
* * *r(30).Select
* * *ActiveCell.Value = sname
* * *r(31).Select
* * *ActiveCell.Formula = "='" & sname & "'!N3"
* * *Selection.Copy
* * *For x = 1 To 25
* * *ActiveCell.Offset(1, 0).Select
* * *ActiveSheet.Paste
* * *Next x

* * *'Copy another set of 26 results from the table
* * *r(58).Select
* * *ActiveCell.Value = sname
* * *r(59).Select
* * *ActiveCell.Formula = "='" & sname & "'!O3"
* * *Selection.Copy
* * *For x = 1 To 25
* * *ActiveCell.Offset(1, 0).Select
* * *ActiveSheet.Paste
* * *Next x
* * *Range("e86").Select
* * *Selection.Copy
* * *r(85).Select
* * *ActiveSheet.Paste

* * *'get rid of the Templates column on the Tables sheet so it doesn't
figure in the results as not needed once first player entered
* * *If Range("e2") = "Template" Then
* * *Columns("E:E").Select
* * *Selection.Delete Shift:=xlToLeft
* * *Else
* * *End If

* * *'Go to the home sheet where we sort the results adding each players
weekly result from column r2 onwards
* * *Sheets("Home").Select
* * *Dim z As Range
* * *If [R2].Formula = "" Then
* * *Set z = [R2]

* * *Else
* * *Set z = Range("Q2").End(xlToRight).Offset(0, 1)
* * *End If
* * *z.Select

* * *'Label the column
* * *ActiveCell.Value = sname

* * *'get the result from the tables column depending on the value in Q3
on the home sheet
* * *z(2).Select

* * *'This line is where it fails. Everything works perfectly up to here....

* * *ActiveCell.Formula = "=INDEX(Tables!r(2):r(27),$Q$3)"

* * *z(3).Select
* * *ActiveCell.Value = "=INDEX(Tables!r(59):r(84),$Q$3)"
* * *z(4).Select
* * *ActiveCell.Value = "=Tables!r(28)"
* * *z(5).Select
* * *ActiveCell.Value = "=Tables!r(85)"

* * *End Sub


A cursory look suggests you are doing extra work. Perhaps a look in
the vba help index for REPLACE would be helpful. And you need to learn
to write code that does NOT select. If all else fails,

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index Formula Help Ken Excel Discussion (Misc queries) 3 September 2nd 09 08:56 PM
Index Formula Cow Girl Excel Worksheet Functions 9 June 30th 09 06:45 PM
If or index formula help Belinda7237 Excel Worksheet Functions 2 December 17th 08 07:19 PM
Index formula help Luke Excel Discussion (Misc queries) 1 December 14th 05 09:34 AM
Sum and Index formula? Luke Excel Discussion (Misc queries) 2 April 6th 05 12:58 PM


All times are GMT +1. The time now is 03:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"