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?
Reference Error Cells Without Error Being Returned
-
-
-
-
Re: Reading Cell Values That Dont Exist
try using If(iserror(cell),0,cell)
-
Re: Reading Cell Values That Dont Exist
Quote from gmccreedyWell I tried that method as shown below...
Code
Display MorePrivate 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
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.
Code
Display MorePrivate 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
I trumped down the two loops to one. It seemed redundant to loop the same variables twice.
-
Re: Reading Cell Values That Dont Exist
=if(iserr(your Refrence),0,"your Custom Function")
-
Re: Reading Cell Values That Dont Exist
Quote from gmccreedyhow about this...I am not really sure how this code is working, so I haven't tested it.
Code
Display MorePrivate 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
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!