My VBA code is breaking and I have no idea how to make it work

Good afternoon,

This is my first ever VBA code, so I apologize in advance if the error I am getting it's because I am beginner.

Lemme try to explain what I want, and then I'll post the VBA code I've made.

I am building an Excel Roster management for my WOW guild. In the first spreadsheet I write the main information about the players, the second spreadsheet is all about players alternative characters, third sheet is a warning system and the fourth sheet is a specific group of people. What I want is, if in the first spreadsheet a player is set as a player for "Eclipse" group, his name goes directly to the fourth sheet.

First spreadsheet, where you can see the "Eclipse" (red arrow) under the Raid Team, that should be the "if" thing. If it has "Eclipse", then the name of the player (blue arrow) is automatically inserted into a specific spreadsheet

See [Troubleshoot1] image.

And this is the fourth spreadsheet, where I want the information to be inserted.

See [Troubleshoot2] image.

The code I'm using is:

Sub EclipseRaidTeamMacro()


Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range


Set StatusCol = Planilha2.Range("D2:D75")


For Each Status In StatusCol


If Planilha4.Range("A2") = "" Then
Set PasteCell = Planilha4.Range("A2")
Else
Set PasteCell = Sheet4.Range("A1").End(xlDown).Offset(1, 0)
End If


If Status = "Eclipse" Then PastelCell.Resize(1, 14).Value = Status.Offset(0, -14).Resize(1, 14).Value




Next Status


End Sub

Bare in mind that my Excel is set to portuguese, so whenever you read "Planilha" it means "Spreadsheet"

Thank you all in advance!

Quote from cytop

If Status = "Eclipse" Then PastelCell.Resize(1, 14) ...

That presumed typo, PastelCell rather than PasteCell, is the first thing that stands out when reading the code ...

Hello cytop,

Thanks for pointing out the typo, but even after correcting it, the macro still doesn't work. When I click "Depurate" on VBA, it highlights he following bit of the code:

As you can see, the typo has been corrected, still the macro doesn't work. I am assuming the "Resize" values are breaking the code, since when I was reading about VBA, this part was the one which I had more dificulties about.

Once again, thanks for the quick reply and for trying to help me out!

Quote from rory

You are iterating cells (Status) in column D then trying to refer to a column 14 to the left of that, which clearly doesn't exist.

Hey rory, thanks for replying,

I see, I've tried to change some numbers, but like I've said in my first reply, the all the resizes aspects of the VBA coding is what messes up with my head (like I've said, it's my first ever VBA code). It's been really hard for me to understand. Do you know any good websites that explains how those values work and/or what each parameters refers to?

Quote from cytop

Like I said, I stopped at the first obvious error - and it is difficult to determine an issue just by reading code when that code is syntactically correct ... the next issue is likely the Offset -14 when assigning StatusCell - but that is a guess and I have no more guesses left.

Hint:

The next time you look for help (on any forum) then also include:

• The actual error number and description (Some errors, 1004 for example, have multiple different causes/descriptions.
• The line it appears on.
• The code (as text, not a picture) but preferably the workbook with a sample of the data and any confidential/personal data obscured.
• If necessary a comment on how to cause the error to occur.

You make it easy to test/debug an issue and you'll usually get faster and more accurate responses that'll solve it first time of asking.

Once again, thanks for the reply cytop,

I thought I've pasted the entire code with the code option, my bad.

Indeed the error is the 1004, and the line is 17, col 1.

I'll try to post the code down below again. I've tried to change the numbers like you and rory said (the -14 on StatusCell) - but it's still being a problem, apparently I can't figure out what those numbers are refering to. I've tried row, column; column, row, but nothing works.

Hopefully I'll do it right this time, nonetheless, if you can't find any answers with the information, I appreciate all the help and comments you've made!

Sub EclipseRaidTeamMacro()


Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range


Set StatusCol = Planilha2.Range("D2:D75")


For Each Status In StatusCol


If Planilha4.Range("A2") = "" Then
Set PasteCell = Planilha4.Range("A2")
Else
Set PasteCell = Sheet4.Range("A1").End(xlDown).Offset(1, 0)
End If


If Status = "Eclipse" Then PasteCell.Resize(0, -3).Value = Status.Offset(0, -3).Resize(0, -3).Value




Next Status


End Sub

Quote from rory

Offset allows you to refer to a range a given number of rows and/or columns from a starting range. The numbers for the row offset can be negative, zero, or positive, as long as the resulting range exists. So Range("D1").Offset(0, -3) is fine because it offsets 3 columns to the left, which puts you in column A.

Resize sets a new size for the range in question. Each number argument (number of rows and columns) must be positive because you cannot have a range that is 0 or a negative number of cells high/wide. That is different from the worksheet function OFFSET which allows you to specify negative numbers there too to extend the range up and/or to the left. So you cannot use Resize(0, -3).

It's not clear from your sample why you are using Resize at all.

Thank you so much rory!

With your excelente explanation I was able to clean up the code I did previously.

I've removed the "resizes" and indeed it worked! The code is allowing me to find all the "Eclipse" team's members and paste their names in Sheet4, just as intended!

The only issue that's happening, is that unfortunately, it's not pasting the names in sequence. So the first name it's pasted at A2, as intended. But the following one is placed 7 cells below, at A9, with the following name placed at A10. Not sure why that's happening, as I wrote the code to put the names one cell down (I guess).

This was the cleanup I did, by the way:

For Each Status In StatusCol


If Planilha4.Range("A2") = "" Then
Set PasteCell = Planilha4.Range("A2")
Else
Set PasteCell = Planilha4.Range("A2").End(xlDown).Offset(-1, 0)
End If


If Status = "Eclipse" Then PasteCell.Value = Status.Offset(0, -3)

Quote from rory

It's generally better to go up when finding the next cell (unless there is data below where you are working, or you are using a table):

Code

Set PasteCell = Planilha4.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Yeah, I am using a table with headers, apparently that's the issue.