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:

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

    Thank you all in advance!

  • 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!

  • 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.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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.

  • 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?


    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!


  • ... and line 17 (as posted) is an 'Else' statement which cannot error during run time. It can cause a compile error though but that means the code just does not run in the first place.


    Post a copy of your workbook that displays the error

  • 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.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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:


  • 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)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • 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.

  • If you have a table you should use the Listobject in your code rather than trying to figure out where the next row should go.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!