Reference Error Cells Without Error Being Returned

  • I have a VB function in a worksheet that requires data from a workbook that is not under my control. The problem is that the workbook does not always have complete data. Often cells are filled with #VALUE, when this occurs I need a way to tell my VBA to assume a value of 0. I have tried using if(cell="#VALUE", 0,cell) but to no avail. any suggestions?

  • Re: Reading Cell Values That Dont Exist


    how about...

    Code
    on error goto novalue
    ' do something
    novalue:
    cell=0


    this assumes that the variable "cell" has meaning...

  • Re: Reading Cell Values That Dont Exist


    Quote from gmccreedy

    how about...

    Code
    on error goto novalue
    ' do something
    novalue:
    cell=0


    this assumes that the variable "cell" has meaning...


    Well I tried that method as shown below...



    It didn't work. It's still translating #VALUE! as #VALUE! and not as zero. Was that the method you had in mind?

  • Re: Reading Cell Values That Dont Exist


    not sure if that will work...you are taking it out of the loops when calling into the error statement. You either need to return it somehow...or put the goto location back into the loop.

  • Re: Reading Cell Values That Dont Exist


    how about this...I am not really sure how this code is working, so I haven't tested it.



    I trumped down the two loops to one. It seemed redundant to loop the same variables twice.

  • Re: Reading Cell Values That Dont Exist


    Quote from gmccreedy

    how about this...I am not really sure how this code is working, so I haven't tested it.



    I trumped down the two loops to one. It seemed redundant to loop the same variables twice.


    Thanks, the logic in that one worked.


    I only had two loops because it was taking data from one workbook and putting it into a different one. Only missing if anyone uses this code is a endif statement.

Participate now!

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