Hello All,
Below is some code written by Bernie Dietricka and adapted by someone else
here on this forum.
I have tred to change it myself to achieve the following.
I have two columns of teams, that play each other on a home and away basis.
I have the club badges in the column next to there club name.
The club names are in range D6:D13 for the home teams in both rounds.
The Away teams in the range G6:G13.
At present the badges are showing in the correct column for the away teams,
but not for the Home teams.
For the home teams the badges are staying in the same sequence as the away
team names.
Could you please help and rectify my error.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell
Set oPic = Me.Pictures(Replace(.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Offset(0, -2).Left + .Offset(0, -2).Width / 2 -
oPic.Width / 2
Set oPic2 = Me.Pictures(Replace(.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Offset(0, 1).Left + .Offset(0, 1).Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub

Thank you and best regards

Max

Max: What isn't working right?

"Max" wrote:

Hello All,
Below is some code written by Bernie Dietricka and adapted by someone else
here on this forum.
I have tred to change it myself to achieve the following.
I have two columns of teams, that play each other on a home and away basis.
I have the club badges in the column next to there club name.
The club names are in range D6:D13 for the home teams in both rounds.
The Away teams in the range G6:G13.
At present the badges are showing in the correct column for the away teams,
but not for the Home teams.
For the home teams the badges are staying in the same sequence as the away
team names.
Could you please help and rectify my error.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell
Set oPic = Me.Pictures(Replace(.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Offset(0, -2).Left + .Offset(0, -2).Width / 2 -
oPic.Width / 2
Set oPic2 = Me.Pictures(Replace(.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Offset(0, 1).Left + .Offset(0, 1).Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub

Thank you and best regards

Max

Hello Joel,
The badges in the Away team (oPic2) range (G6:G13) they are lining up
correctly.
The badges for the Home team (oPic2) range are not.

Thanks

Max

"Joel" wrote:

Max: What isn't working right?

"Max" wrote:

Hello All,
Below is some code written by Bernie Dietricka and adapted by someone else
here on this forum.
I have tred to change it myself to achieve the following.
I have two columns of teams, that play each other on a home and away basis.
I have the club badges in the column next to there club name.
The club names are in range D6:D13 for the home teams in both rounds.
The Away teams in the range G6:G13.
At present the badges are showing in the correct column for the away teams,
but not for the Home teams.
For the home teams the badges are staying in the same sequence as the away
team names.
Could you please help and rectify my error.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell
Set oPic = Me.Pictures(Replace(.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Offset(0, -2).Left + .Offset(0, -2).Width / 2 -
oPic.Width / 2
Set oPic2 = Me.Pictures(Replace(.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Offset(0, 1).Left + .Offset(0, 1).Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub

Thank you and best regards

Max

Are the away teams in Column D and the home teams in column G? Don't you
want different offset for home vs away? The away team the pictures left of
the team name and the home team to the right of the team name. I think the
code is ok. I made some changes to the code to make it easier to understand.
My code does the same as yours.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell.offset(0,-2)
'I added rcell infront of .text
Set oPic = Me.Pictures(Replace(rcell.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Left + .Width / 2 - oPic.Width / 2
end with
with rcell.offset(0,1)
'I added rcell infront of .text
Set oPic2 = Me.Pictures(Replace(rcell.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Left + .Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub


"Max" wrote:

Hello Joel,
The badges in the Away team (oPic2) range (G6:G13) they are lining up
correctly.
The badges for the Home team (oPic2) range are not.

Thanks

Max

"Joel" wrote:

Max: What isn't working right?

"Max" wrote:

Hello All,
Below is some code written by Bernie Dietricka and adapted by someone else
here on this forum.
I have tred to change it myself to achieve the following.
I have two columns of teams, that play each other on a home and away basis.
I have the club badges in the column next to there club name.
The club names are in range D6:D13 for the home teams in both rounds.
The Away teams in the range G6:G13.
At present the badges are showing in the correct column for the away teams,
but not for the Home teams.
For the home teams the badges are staying in the same sequence as the away
team names.
Could you please help and rectify my error.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell
Set oPic = Me.Pictures(Replace(.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Offset(0, -2).Left + .Offset(0, -2).Width / 2 -
oPic.Width / 2
Set oPic2 = Me.Pictures(Replace(.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Offset(0, 1).Left + .Offset(0, 1).Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub

Thank you and best regards

Max

Hello Joel,
Your code did the same as mine.
I have the layout Home Team, HT badge, HT score, then Away Team, AT badge,
At score.
With your I dea I can set it up the "right" way as you described it.
However, I had to split the ranges and place a rCell2 to correspond with the
oPic2 and this gave me exactly what I needed.

Thank you for your help and the idea.
Well done and thank you again.

Regards


Max


"Joel" wrote:

Are the away teams in Column D and the home teams in column G? Don't you
want different offset for home vs away? The away team the pictures left of
the team name and the home team to the right of the team name. I think the
code is ok. I made some changes to the code to make it easier to understand.
My code does the same as yours.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell.offset(0,-2)
'I added rcell infront of .text
Set oPic = Me.Pictures(Replace(rcell.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Left + .Width / 2 - oPic.Width / 2
end with
with rcell.offset(0,1)
'I added rcell infront of .text
Set oPic2 = Me.Pictures(Replace(rcell.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Left + .Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub


"Max" wrote:

Hello Joel,
The badges in the Away team (oPic2) range (G6:G13) they are lining up
correctly.
The badges for the Home team (oPic2) range are not.

Thanks

Max

"Joel" wrote:

Max: What isn't working right?

"Max" wrote:

Hello All,
Below is some code written by Bernie Dietricka and adapted by someone else
here on this forum.
I have tred to change it myself to achieve the following.
I have two columns of teams, that play each other on a home and away basis.
I have the club badges in the column next to there club name.
The club names are in range D6:D13 for the home teams in both rounds.
The Away teams in the range G6:G13.
At present the badges are showing in the correct column for the away teams,
but not for the Home teams.
For the home teams the badges are staying in the same sequence as the away
team names.
Could you please help and rectify my error.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell
Set oPic = Me.Pictures(Replace(.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Offset(0, -2).Left + .Offset(0, -2).Width / 2 -
oPic.Width / 2
Set oPic2 = Me.Pictures(Replace(.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Offset(0, 1).Left + .Offset(0, 1).Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub

Thank you and best regards

Max

That is exactly what I said. Maybe I should of been a little clearer. I
thought with my help you would figure the solution yourself. And that is
what you did. Glad to help. Sometimes it is better to lead a horse to water
than just to give him the water.

"Max" wrote:

Hello Joel,
Your code did the same as mine.
I have the layout Home Team, HT badge, HT score, then Away Team, AT badge,
At score.
With your I dea I can set it up the "right" way as you described it.
However, I had to split the ranges and place a rCell2 to correspond with the
oPic2 and this gave me exactly what I needed.

Thank you for your help and the idea.
Well done and thank you again.

Regards


Max


"Joel" wrote:

Are the away teams in Column D and the home teams in column G? Don't you
want different offset for home vs away? The away team the pictures left of
the team name and the home team to the right of the team name. I think the
code is ok. I made some changes to the code to make it easier to understand.
My code does the same as yours.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell.offset(0,-2)
'I added rcell infront of .text
Set oPic = Me.Pictures(Replace(rcell.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Left + .Width / 2 - oPic.Width / 2
end with
with rcell.offset(0,1)
'I added rcell infront of .text
Set oPic2 = Me.Pictures(Replace(rcell.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Left + .Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub


"Max" wrote:

Hello Joel,
The badges in the Away team (oPic2) range (G6:G13) they are lining up
correctly.
The badges for the Home team (oPic2) range are not.

Thanks

Max

"Joel" wrote:

Max: What isn't working right?

"Max" wrote:

Hello All,
Below is some code written by Bernie Dietricka and adapted by someone else
here on this forum.
I have tred to change it myself to achieve the following.
I have two columns of teams, that play each other on a home and away basis.
I have the club badges in the column next to there club name.
The club names are in range D6:D13 for the home teams in both rounds.
The Away teams in the range G6:G13.
At present the badges are showing in the correct column for the away teams,
but not for the Home teams.
For the home teams the badges are staying in the same sequence as the away
team names.
Could you please help and rectify my error.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell
Set oPic = Me.Pictures(Replace(.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Offset(0, -2).Left + .Offset(0, -2).Width / 2 -
oPic.Width / 2
Set oPic2 = Me.Pictures(Replace(.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Offset(0, 1).Left + .Offset(0, 1).Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub

Thank you and best regards

Max

Hello Joel,
No problem, either way I appreciate your help and guidance.

Thank you again

Max

"Joel" wrote:

That is exactly what I said. Maybe I should of been a little clearer. I
thought with my help you would figure the solution yourself. And that is
what you did. Glad to help. Sometimes it is better to lead a horse to water
than just to give him the water.

"Max" wrote:

Hello Joel,
Your code did the same as mine.
I have the layout Home Team, HT badge, HT score, then Away Team, AT badge,
At score.
With your I dea I can set it up the "right" way as you described it.
However, I had to split the ranges and place a rCell2 to correspond with the
oPic2 and this gave me exactly what I needed.

Thank you for your help and the idea.
Well done and thank you again.

Regards


Max


"Joel" wrote:

Are the away teams in Column D and the home teams in column G? Don't you
want different offset for home vs away? The away team the pictures left of
the team name and the home team to the right of the team name. I think the
code is ok. I made some changes to the code to make it easier to understand.
My code does the same as yours.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell.offset(0,-2)
'I added rcell infront of .text
Set oPic = Me.Pictures(Replace(rcell.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Left + .Width / 2 - oPic.Width / 2
end with
with rcell.offset(0,1)
'I added rcell infront of .text
Set oPic2 = Me.Pictures(Replace(rcell.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Left + .Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub


"Max" wrote:

Hello Joel,
The badges in the Away team (oPic2) range (G6:G13) they are lining up
correctly.
The badges for the Home team (oPic2) range are not.

Thanks

Max

"Joel" wrote:

Max: What isn't working right?

"Max" wrote:

Hello All,
Below is some code written by Bernie Dietricka and adapted by someone else
here on this forum.
I have tred to change it myself to achieve the following.
I have two columns of teams, that play each other on a home and away basis.
I have the club badges in the column next to there club name.
The club names are in range D6:D13 for the home teams in both rounds.
The Away teams in the range G6:G13.
At present the badges are showing in the correct column for the away teams,
but not for the Home teams.
For the home teams the badges are staying in the same sequence as the away
team names.
Could you please help and rectify my error.

Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim oPic2 As Picture
Dim rCell As Range
Me.Pictures.Visible = True
For Each rCell In Range("D6:D13, G6:G13").Cells
With rCell
Set oPic = Me.Pictures(Replace(.Text & "", " ", ""))
oPic.Visible = True
oPic.Top = .Top + .Height / 1.4 - oPic.Height / 1.4
oPic.Left = .Offset(0, -2).Left + .Offset(0, -2).Width / 2 -
oPic.Width / 2
Set oPic2 = Me.Pictures(Replace(.Text & "2", " ", ""))
oPic2.Visible = True
oPic2.Top = .Top + .Height / 1.4 - oPic2.Height / 1.4
oPic2.Left = .Offset(0, 1).Left + .Offset(0, 1).Width / 2 -
oPic2.Width / 2
End With
Next rCell
End Sub

Thank you and best regards

Max