vlookup with iserror

Hi, all.

I have the following code which will take a string and search for it in
another worksheet.

once found, it will retrieve the next columns numeric figure.

Function ReplaceValue(ByVal AccStr As String) As Double

'This function will replace the column with the amount found in the
another excel worksheetdata

Dim Amt As Variant

ReplaceValue = 0

Dim Table As Range
Set Table = ThisWorkbook.Worksheets("Sheet").Range("A1:B15000")

Amt = WorksheetFunction.VLookup(AccStr, Table, 2, False)

If IsError(Amt) Then
ReplaceValue = 0
Else
ReplaceValue = Amt
End If

End Function

If found, it will retrieve the figure. Not found, the code is suppose
to returns 0. but the cell is showing '#VALUE!'. Anyone have any
advise?

Thanks.

Function ReplaceValue(ByVal AccStr As String) As Double

'This function will replace the column with the amount found in the another
excel worksheetdata

Dim Amt As Variant

ReplaceValue = 0

Dim Table As Range
Set Table = ThisWorkbook.Worksheets("Sheet3").Range("A1:B15000")

Amt = Application.VLookup(AccStr, Table, 2, False)

If IsError(Amt) Then
ReplaceValue = 0
Else
ReplaceValue = Amt
End If

End Function



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hi, all.
>
> I have the following code which will take a string and search for it in
> another worksheet.
>
> once found, it will retrieve the next columns numeric figure.
>
> Function ReplaceValue(ByVal AccStr As String) As Double
>
> 'This function will replace the column with the amount found in the
> another excel worksheetdata
>
> Dim Amt As Variant
>
> ReplaceValue = 0
>
> Dim Table As Range
> Set Table = ThisWorkbook.Worksheets("Sheet").Range("A1:B15000")
>
> Amt = WorksheetFunction.VLookup(AccStr, Table, 2, False)
>
> If IsError(Amt) Then
> ReplaceValue = 0
> Else
> ReplaceValue = Amt
> End If
>
> End Function
>
> If found, it will retrieve the figure. Not found, the code is suppose
> to returns 0. but the cell is showing '#VALUE!'. Anyone have any
> advise?
>
> Thanks.
>

Try changing this:
Amt = WorksheetFunction.VLookup(AccStr, Table, 2, False)
to
Amt = application.VLookup(AccStr, Table, 2, False)

worksheetfunction.vlookup() causes a run time error that you have to program
around.

application.vlookup() returns an error that you can check.

If you really wanted to use worksheetfunction.vlookup(), you could use something
like:

On Error Resume Next
Amt = WorksheetFunction.VLookup(AccStr, Table, 2, False)
If Err.Number <> 0 Then
amt = 0
err.clear
end if

ReplaceValue = Amt





[email protected] wrote:
>
> Hi, all.
>
> I have the following code which will take a string and search for it in
> another worksheet.
>
> once found, it will retrieve the next columns numeric figure.
>
> Function ReplaceValue(ByVal AccStr As String) As Double
>
> 'This function will replace the column with the amount found in the
> another excel worksheetdata
>
> Dim Amt As Variant
>
> ReplaceValue = 0
>
> Dim Table As Range
> Set Table = ThisWorkbook.Worksheets("Sheet").Range("A1:B15000")
>
> Amt = WorksheetFunction.VLookup(AccStr, Table, 2, False)
>
> If IsError(Amt) Then
> ReplaceValue = 0
> Else
> ReplaceValue = Amt
> End If
>
> End Function
>
> If found, it will retrieve the figure. Not found, the code is suppose
> to returns 0. but the cell is showing '#VALUE!'. Anyone have any
> advise?
>
> Thanks.

--

Dave Peterson

It might help if you identified the sheet. Why not just use the vlookup
function.

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> Hi, all.
>
> I have the following code which will take a string and search for it in
> another worksheet.
>
> once found, it will retrieve the next columns numeric figure.
>
> Function ReplaceValue(ByVal AccStr As String) As Double
>
> 'This function will replace the column with the amount found in the
> another excel worksheetdata
>
> Dim Amt As Variant
>
> ReplaceValue = 0
>
> Dim Table As Range
> Set Table = ThisWorkbook.Worksheets("Sheet").Range("A1:B15000")
>
> Amt = WorksheetFunction.VLookup(AccStr, Table, 2, False)
>
> If IsError(Amt) Then
> ReplaceValue = 0
> Else
> ReplaceValue = Amt
> End If
>
> End Function
>
> If found, it will retrieve the figure. Not found, the code is suppose
> to returns 0. but the cell is showing '#VALUE!'. Anyone have any
> advise?
>
> Thanks.
>

hi, Bob. your solution works. thanks.

Bob Phillips wrote:
> Function ReplaceValue(ByVal AccStr As String) As Double
>
> 'This function will replace the column with the amount found in the another
> excel worksheetdata
>
> Dim Amt As Variant
>
> ReplaceValue = 0
>
> Dim Table As Range
> Set Table = ThisWorkbook.Worksheets("Sheet3").Range("A1:B15000")
>
> Amt = Application.VLookup(AccStr, Table, 2, False)
>
> If IsError(Amt) Then
> ReplaceValue = 0
> Else
> ReplaceValue = Amt
> End If
>
> End Function
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi, all.
> >
> > I have the following code which will take a string and search for it in
> > another worksheet.
> >
> > once found, it will retrieve the next columns numeric figure.
> >
> > Function ReplaceValue(ByVal AccStr As String) As Double
> >
> > 'This function will replace the column with the amount found in the
> > another excel worksheetdata
> >
> > Dim Amt As Variant
> >
> > ReplaceValue = 0
> >
> > Dim Table As Range
> > Set Table = ThisWorkbook.Worksheets("Sheet").Range("A1:B15000")
> >
> > Amt = WorksheetFunction.VLookup(AccStr, Table, 2, False)
> >
> > If IsError(Amt) Then
> > ReplaceValue = 0
> > Else
> > ReplaceValue = Amt
> > End If
> >
> > End Function
> >
> > If found, it will retrieve the figure. Not found, the code is suppose
> > to returns 0. but the cell is showing '#VALUE!'. Anyone have any
> > advise?
> >
> > Thanks.
> >