Basic VBA If Else confusion

[Margate]

Hello world,

So below is my attempt at an If else statement but needless to say 1. It is not working and 2. My VBA needs some help!
I have tried other ways to get this to work but keep on getting error messages. I know that it is a bit of a mess but my VBA is just not that strong. I think it can be done all in one with the correct arrangement of else if's but I may be wrong?

Would anyone mind re-arranging it or guiding me in order to get it working:

Please Login or Register  to view this content.

Thank you in advance for any help

[Zer0Cool]

I think it can be done

What can be done? You never stated what the purpose of the code was, only that its not working. Itd be like giving someone bad instructions for building a mystery device and then asking them how it should actually be done. We cant say because we can only try and guess at what you want to do based on the code you gave us which doesn't work and has no context (we have no sample file to see what its looking at or why).

[MrShorty]

What do you mean that it does not work? I get no error messages and it seems to work just fine in a new, blank workbook (well, I have to put something into K69 and a few cells below to see it work).

I note that all Range() objects are unqualified (nothing in front), which means they will refer to the active worksheet. Is it possible that some of your errors are because you need this to act on something other than the active worksheet? In any case, I could see value in qualifying every range object [Thisworkbook.worksheets(1).range(...)] just so there is not ambiguity. But that should not be necessary to make the procedures run as is.

[Margate]

Apologies, I have added a spreadsheet. The code is part of a far bigger macro and this is just a section of it. When I run it I am met with the following error message:

Compile Error
Expected End Sub

[Margate]

In my spreadsheet, there I need to sort the comments and bring them to the top of the list. If there is no comments then both the Job # and comment sections need to be deleted. If there is just one comment then everything below that comment needs to be deleted. Ultimately I can only end up with a maximum of 2 comments.

Hope this makes sense.

[MrShorty]

As written, you have two End Sub statements at the end of the module, and no End Sub statements before Sub emptycell(). If you put an End Sub before Sub emptycell(), so that VBA can correctly see that you have two Sub procedures, and get rid of the extra End Sub at the bottom, then it should compile. From there, it looks like it might work with no additional modification.

Please Login or Register  to view this content.

[Margate]

Brilliant. That works perfectly, thank you very much for your time and help. I will mark this thread as solved.

Regards,

Margate