Help with Index formula

X

XR8 Sprintless

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
 
D

Don Guillett Excel MVP

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 tocopy
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 tohere....

     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
Click to expand...

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

XR8 Sprintless

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."
Click to expand...
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with a VBA subroutine 0
Help me5 1
Move and Link 0
Macro to delete row if cells greater than certain value 1
Help me4 2
Assign a sound to a variable 19
Totals An Easier Way?? 5
Copy Code and a sort problem 2

Top