VBA Find based on variable

[steveh8204]

I am putting a VBA function together but am struggling with one component of it. Basically after assigning a team name to a variable (team_a) I need to find it in a table and then create a new variable based on that result and the adjacent 9 cells (the row).

For example if 'team_a' was equal to "Oxford" the returned range would be a new variable '(team_a_output') which would equal range B37:J37.

Thanks in advance for any help.

 

[cooper645]

Do either of the following work for your needs??

This one assigns the range to a VBA array which you can then use further in your code or write to sheet etc

Sub Team_Variable()

Dim team_a As Variant, team_a_output(1 To 9) As Variant
Dim cell As Range, i As Integer

team_a = "Oxford"

For Each cell In Range("B32:B41")
    If cell.Value = team_a Then
        For i = 1 To 9
            team_a_output(i) = cell.Offset(0, i - 1).Value
        Next i
    End If
Next cell

End Sub

This one simply assigns the new range to a variable as per your request.

Sub Team_Variable_2()

Dim team_a As String, team_a_output As Variant, cell As Range

team_a = "Oxford"

For Each cell In Range("B32:B41")
    If cell.Value = team_a Then Set team_a_output = Range(cell.Address)
Next cell

Set team_a_output = team_a_output.Resize(, 9)

End Sub

Hope This Helps

 

[steveh8204]

Do either of the following work for your needs??

This one assigns the range to a VBA array which you can then use further in your code or write to sheet etc

Sub Team_Variable()

Dim team_a As Variant, team_a_output(1 To 9) As Variant
Dim cell As Range, i As Integer

team_a = "Oxford"

For Each cell In Range("B32:B41")
    If cell.Value = team_a Then
        For i = 1 To 9
            team_a_output(i) = cell.Offset(0, i - 1).Value
        Next i
    End If
Next cell

End Sub

This one simply assigns the new range to a variable as per your request.

Sub Team_Variable_2()

Dim team_a As String, team_a_output As Variant, cell As Range

team_a = "Oxford"

For Each cell In Range("B32:B41")
    If cell.Value = team_a Then Set team_a_output = Range(cell.Address)
Next cell

Set team_a_output = team_a_output.Resize(, 9)

End Sub

Hope This Helps

Thanks mate, I will have a look in the morning and let you know how I get on ?

 

[steveh8204]

Do either of the following work for your needs??

This one assigns the range to a VBA array which you can then use further in your code or write to sheet etc

Sub Team_Variable()

Dim team_a As Variant, team_a_output(1 To 9) As Variant
Dim cell As Range, i As Integer

team_a = "Oxford"

For Each cell In Range("B32:B41")
    If cell.Value = team_a Then
        For i = 1 To 9
            team_a_output(i) = cell.Offset(0, i - 1).Value
        Next i
    End If
Next cell

End Sub

This one simply assigns the new range to a variable as per your request.

Sub Team_Variable_2()

Dim team_a As String, team_a_output As Variant, cell As Range

team_a = "Oxford"

For Each cell In Range("B32:B41")
    If cell.Value = team_a Then Set team_a_output = Range(cell.Address)
Next cell

Set team_a_output = team_a_output.Resize(, 9)

End Sub

Hope This Helps

Almost inserted this code into my sub, a bit tricky but that says more about my code than yours

Only bit I'm struggling with is putting the value of the variable into a range. I thought the following might suffice but it doesnt:

Range("B3:Z3").Value = team_a_output

Did try a quick google but most answers seemed to be relating to declaring the values in a range to a variable which is the opposite.

 

[cooper645]

Depending on which option you have used will depend on which code to use.

If you went for the first VBA Array option then the following should do the job:

For i = LBound(team_a_output) To UBound(team_a_output)
    Cells(3, i + 1).Value = team_a_output(i)
Next i

If you opted for the second code option then the following should meet with your needs:

team_a_output.Copy Range("B3")

Or without the copy paste command:

Range("B3:J3").Value = team_a_output.Value

 

[steveh8204]

Depending on which option you have used will depend on which code to use.

If you went for the first VBA Array option then the following should do the job:

For i = LBound(team_a_output) To UBound(team_a_output)
    Cells(3, i + 1).Value = team_a_output(i)
Next i

If you opted for the second code option then the following should meet with your needs:

team_a_output.Copy Range("B3")

Or without the copy paste command:

Range("B3:J3").Value = team_a_output.Value

Sorry should have stipulated that. It was the second one. Thanks, thats done the job perfect, your help with this has been extremely appreciated.

 

[cooper645]

Most welcome.