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

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

Private Sub CommandButton1_Click()
' start


Dim hld(80, 10)


Workbooks.Open Filename:= _
  "K:\quotes.xls", ReadOnly:=True


 For i = 1 To 10
 For j = 1 To 80


On Error GoTo novalue
x = Worksheets("sheet1").Cells(j, i)
 hld(j, i) = x
 
 Next j
 Next i
   ThisWorkbook.Saved = False
  ActiveWindow.Close False
  
' now write the values


For i = 1 To 10
For j = 1 To 80
Worksheets("straddles").Cells(j, i) = hld(j, i)
Next j
Next i


GoTo 13
novalue:
hld(j, i) = 0
13 '
End Sub

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

Quote from gmccreedy

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

Code

Private Sub CommandButton1_Click()
     ' start
     
    Dim hld(80, 10)
     
    Workbooks.Open Filename:= _
    "K:\quotes.xls", ReadOnly:=True
     
    For i = 1 To 10
        For j = 1 To 80
            If IsError(Worksheets("sheet1").Cells(j, i)) Then
            Worksheets("sheet1").Cells(j, i) = 0
            Else
            hld(j, i) = Worksheets("sheet1").Cells(j, i)
            '
            Worksheets("straddles").Cells(j, i) = hld(j, i)
        Next j
    Next i
    ThisWorkbook.Saved = False
    ActiveWindow.Close False
End Sub

Display More

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.