Change to a VBA code

[Marvo]

I have the following code in a workbook. When pressing "update" the data entered should populate rows on a another sheet to adjacent cells, as in columns E, F, G etc but instead populates E, G, I, etc, missing alternate columns out. Can anybody tell me where in the code its going wrong please? (I've little knowledge working with VBA. Thank you.

Sub Master_Update()
Dim a, b
Dim i As Long, irow As Long
Dim player As String
Application.ScreenUpdating = False

player = Range("Player")
a = Range("Shots")
ReDim b(1 To 18)

With Sheets("Master")
.Activate
irow = Application.Match(player, Range("B:B"), 0)
For i = 1 To 18
.Cells(irow, (i - 1) * 2 + 5) = a(i, 1)
b(i) = .Cells(irow, i + 61)
Next i
End With

With Sheets("Score Enter")
.Cells(3, "C").Resize(18, 1) = Application.Transpose(b)
.Activate
End With

Application.ScreenUpdating = True

End Sub
Sub Clear_Shots()
Range("Shots").ClearContents
Range("Points").ClearContents
End Sub

 

[MikeVol]

Hello @Marvo. Change this line

            .Cells(irow, (i - 1) * 2 + 5) = a(i, 1)

to

            .Cells(irow, i + 4) = a(i, 1)  ' E (5), F (6), G (7), ...

 

[Marvo]

I'm getting

command will stop the debugger?

It is working though. Does it matter?

 

[Marvo]

Sub Master_Update()
Dim a, b
Dim i As Long, irow As Long
Dim player As String
Application.ScreenUpdating = False

player = Range("Player")
a = Range("Shots")
ReDim b(1 To 18)

With Sheets("Master")
    .Activate
    irow = Application.Match(player, Range("B:B"), 0)
    For i = 1 To 18
        .Cells(irow, i + 4) = a(i, 1)  ' E (5), F (6), G (7), ...
        b(i) = .Cells(irow, i + 61)
    Next i
End With

With Sheets("Score Enter")
    .Cells(3, "C").Resize(18, 1) = Application.Transpose(b)
    .Activate
End With

Application.ScreenUpdating = True

End Sub
Sub Clear_Shots()
Range("Shots").ClearContents
Range("Points").ClearContents
End Sub

 

[MikeVol]

missing alternate columns out

What do you mean by this expression? More details!

 

[Marvo]

I've removed alternate columns from a previous workbook that I needed to separate. So the VBA code used to work to alternate columns. Now those columns have gone, the VBA was still looking at alternate columns. Your "FIX" has put that right but I did get the "This command with stop the debugger". I don't know what that entails or whether it will have a detrimental affect.

 

[Marvo]

Maybe a better explanation. The scores entered equal points, so the workbook used to have columns adjacent to each other, one for strokes then the next for working out points scored. I've separated the points scored columns to another place in the workbook.

On the sheet where you entered the scores, when you pressed update it used to show you the points scored but that's stopped to. Unfortunately I don't understand VBA so can't put it right. I'm sure its something simple. I can probably use lookup for this part now.

 

[MikeVol]

@Marvo, t's hard to help you without seeing what you've deleted/changed in your workbook. But that line in the code that we changed works correctly.
Now the code inserts data from column E, F, G, and so on to column V (column 22). Previously, the code inserted from column E, G, I, and so on (every other column) to column AM (column 39).
What you asked to change in the code, that's what I gave you, but what you did in your workbook is another question.

 

[Peter_SSs]

@Marvo
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags in post #4 for you this time. I think you will see the difference when comparing to post #1.

 

[Marvo]

Okay. I'll mark this as solved, then start a new thread when I have a bit more time. Thank you for your help, it's much appreciated.