Help with Index formula
 

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

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."

Help with Index formula
 

On 20/12/2010 4:52 AM, Don Guillett Excel MVP wrote:

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."
Thanks

I have cleaned up the code and realised that as I already had a template
column where I was copying some formulas from it was very easy to add
the index formula in there and copy it which worked.
I looked at replace but don't think it met my requirements. I have
changed the select statements to activate instead.