Using a Variable to create a Name.

[StevenD]

Public Team1 As String
Public Team2 As String
Public Team3 As String
Public Team4 As String
Public Team5 As String

Public Sub AddScores()
Dim TeamNumber As Integer
Dim TeamRow As Integer
TeamRow = 3
For TeamNumber = 1 To 5
    ## Team & TeamNumber = Sheets("tables").Range("a" & TeamRow)
    TeamRow = TeamRow + 1
Next TeamNumber
End Sub

It doesn't like the line that I have indicated with ##

Hopefully you can understand what I am trying to do.

 

[StevenD]

Hi HalfAce,

Thanks for your reply. Sorry its hard to explain.

As you can see I have Public Team1 as String etc.

I have a table with team names in.

I want Team1 to equal the 1st team in the table cell A3.
Then Team2 to equal the 2nd team in the table cell A4.

Because I have over 20 teams I wanted to automate this by incrementing the TeamNumber from 1 to 20 and the RowNumber.

Hope this explains a little better.

 

[HalfAce]

Hopefully you can understand what I am trying to do.

Nope. I'm not quite getting it.
I know it's not going to like the line you've indicated but I don't know what it is you're looking to do so it's kind of hard to make a suggestion.
Perhaps you can try this for your AddScores sub and see if it's close or gives you any ideas. Other than that I'd need more explanation.

Public Sub AddScores()
Dim TeamNumber As Integer
Dim TeamRow As Integer
TeamRow = 3
For TeamNumber = 1 To 5
    TeamName = "Team " & Sheets("tables").Range("a" & TeamRow)
    MsgBox TeamName
    TeamRow = TeamRow + 1
Next TeamNumber
End Sub

 

[HalfAce]

OK, hold on and let me catch up. I didn't realize you had seen that post I deleted when I posted this, so let me see if your second post helps me understand.

 

[tusharm]

Use an array.

Option Explicit

Dim Team(1 To 5) As String

Public Sub AddScores()
    Dim TeamNumber As Integer
    For TeamNumber = 1 To 5
        Team(TeamNumber) = Sheets("tables").Range("a" & TeamNumber + 2)
        Next TeamNumber
    End Sub

For more on array search XL VBA for 'array'. It will lead you to topics such as 'Declaring arrays' and 'Using arrays.' In addition, search the web or get a book for introductory material on the subject.

Public Team1 As String
Public Team2 As String
Public Team3 As String
Public Team4 As String
Public Team5 As String

Public Sub AddScores()
Dim TeamNumber As Integer
Dim TeamRow As Integer
TeamRow = 3
For TeamNumber = 1 To 5
    ## Team & TeamNumber = Sheets("tables").Range("a" & TeamRow)
    TeamRow = TeamRow + 1
Next TeamNumber
End Sub

It doesn't like the line that I have indicated with ##

Hopefully you can understand what I am trying to do.