if cell in sheet is empty, cancel SQL and go to next Sub

  • Hi all,


    I have some code that connect to a database and pulls info into an excel sheet.
    I have only one problem, that is when there is no value in a cell (that cell is populated from a previous SQL statement and serves as input for the next), the code will fail as you can't put a NULL value in the SQL query.


    I tried by solving it that if the cell has no value, skip the rest of the code and go to the next Sub. It actually does not work and ends up in "conn.Close" as errror.
    Please see below code (I commented it out the code that does not work)



    The code is in a Userform in excel.


    Greetings.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    Have you tried stepping through the code to see what if the IF statement evaluates to True when you expect it to? If it does, it should proceed to End Sub, don't see how it can go to conn.Close.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    Hi,


    I did so, it just goes straight to the conn.Close and then the dubug window opens. If I stop the code at the IF statment and step through, it works.


    Greetings.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    So if the cell is empty it should run another macro (not go to end sub as I said before) and ignore the rest of the code above. Are you saying that happens when you step through? If so, the issue may not be related to blank cells, but the sql syntax. I don't know enough about that to be able to diagnose the problem, but I'm sure somebody else will be able to.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    That code can't run because of mismatched control structures. There's an Extra 'End If' in there. This is probably related to the commented out 'If' block earlier but it is your responsibility to ensure code you post at least compiles so that it can be tested.


    If your code refers to controls on a userform or named ranges/worksheets then you should upload a copy of your workbook so that it can be tested without having to go to the extra trouble of recreating your environment first.


    If the code jumps straight to the line you mention then check the actual value of FinalRow. It is probably < 6.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    Hi Cytop,


    My mistake.....


    I solved finally by just putting the IF statment higher:


    Now it really skips the code :)


    Greetings.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    At that point Branch will ALWAYS be empty...


    I've no idea what the procedure Find_Company_Relation_ID does or how it relates to your code, but something like this might work a little better

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    hi Cytop,


    Your solution work so much better :)


    I just wanted to add some text if accounts section is empty, but I do't get it to work:

    Code
    If branch = vbNullString Then
             
                 LastRow = Sheets("Results").Cells(60000, 1).End(xlUp).Row + 1
                    Sheets("Results").Cells(LastRow, 1).Value = "Client has no accounts"
                    Sheets("Results").Cells(LastRow, 1).Font.Size = 12
                    Sheets("Results").Cells(LastRow, 1).Font.Bold = True
                    Sheets("Results").Cells(LastRow, 1).Font.Color = vbRed
                 
                    OR_BOSS_find_company_relation_ID
            Else


    I know, code looks really silly, but it could do the trick. I tried to step through, but it just jumps to the "conn.Close" from "For j = 6 To Finalrow"
    Is there some magic that I missed?


    Greetings.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    Have you checked as I mentioned earlier..


    Quote from cytop;777644

    If the code jumps straight to the line you mention then check the actual value of FinalRow. It is probably < 6.

  • Re: if cell in sheet is empty, cancel SQL and go to next Sub


    Hi Cytop,


    It is working now correct :) (I first add the text I want to, if there is data, it will overwrite the text)


    BIG THANKS to you all.


    Greetings.

Participate now!

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