Hi I need some help on Vlookup in VBA. I don't know why, but I have a
error with the function.

Dim CoutUnitaire(10) As Double
Dim i As Integer


Worksheets("Mold base").Activate
Range("c17").Activate

For i = 1 To 10

CoutUnitaire(i)
Application.WorksheetFunction.VLookup(ActiveCell.O ffset(i, 0).Value
Worksheets("ListePrix").Range("A:d"), 4, False) ' I want to selec
Column A To D completly
Next i
MsgBox CoutUnitaire(1)
End Sub

gracia

--
Message posted from http://www.ExcelForum.com

The error occurs because I have no data in some cells at this moment
But I know that in some weeks i will fill up more cells. I don't wan
to change my programmation.

mucho gracia

--
Message posted from http://www.ExcelForum.com

Hi
try
Dim vres
For i = 1 To 10
vres=Application.VLookup(ActiveCell.Offset(i, 0).Value,
Worksheets("ListePrix").Range("A:d"), 4, False)
if iserror(vres) then
vres=0
CoutUnitaire(i) = vres
Next i
MsgBox CoutUnitaire(1)
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany


Hi I need some help on Vlookup in VBA. I don't know why, but I have
an
error with the function.

Dim CoutUnitaire(10) As Double
Dim i As Integer


Worksheets("Mold base").Activate
Range("c17").Activate

For i = 1 To 10

CoutUnitaire(i) =
Application.WorksheetFunction.VLookup(ActiveCell.O ffset(i, 0).Value,
Worksheets("ListePrix").Range("A:d"), 4, False) ' I want to select
Column A To D completly
Next i
MsgBox CoutUnitaire(1)
End Sub

gracias


---
Message posted from http://www.ExcelForum.com/

Dim CoutUnitaire(10) As Variant
Dim i As Integer


Worksheets("Mold base").Activate
Range("c17").Activate

For i = 1 To 10

CoutUnitaire(i) = _
Application.VLookup(ActiveCell.Offset(i, 0).Value, _
Worksheets("ListePrix").Range("A:d"), 4, False)
Next i

for i = 1 to 10
if iserror(CoutUnitaire(i)) then
msgbox "Value was not found"
else
msgbox "Value found with results of " & CoutUnitaire(i)
End if
Next

End Sub


If you don't use WorksheetFunction as a qualifier to Vlookup, it doesn't
return a trappable error - it returns an error equivalent to those found in
Cells (#N/A as an example).

which will be shown like this:

? CVErr(xlErrNA)
Error 2042

You can examine these in your coutUnitaire array which I have Dimmed as
Variant so it can hold the error value.

--
Regards,
Tom Ogilvy


"zapatista66 " wrote in message
...
The error occurs because I have no data in some cells at this moment.
But I know that in some weeks i will fill up more cells. I don't want
to change my programmation.

mucho gracias


---
Message posted from http://www.ExcelForum.com/

Hi Tom
I think you just forgot to delte one 'For i 0 1 to 10' statement :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Tom Ogilvy wrote:
Dim CoutUnitaire(10) As Variant
Dim i As Integer


Worksheets("Mold base").Activate
Range("c17").Activate

For i = 1 To 10

CoutUnitaire(i) = _
Application.VLookup(ActiveCell.Offset(i, 0).Value, _
Worksheets("ListePrix").Range("A:d"), 4, False)
Next i

for i = 1 to 10
if iserror(CoutUnitaire(i)) then
msgbox "Value was not found"
else
msgbox "Value found with results of " & CoutUnitaire(i)
End if
Next

End Sub


If you don't use WorksheetFunction as a qualifier to Vlookup, it
doesn't return a trappable error - it returns an error equivalent to
those found in Cells (#N/A as an example).

which will be shown like this:

? CVErr(xlErrNA)
Error 2042

You can examine these in your coutUnitaire array which I have Dimmed
as Variant so it can hold the error value.


"zapatista66 " wrote in
message ...
The error occurs because I have no data in some cells at this
moment.
But I know that in some weeks i will fill up more cells. I don't
want
to change my programmation.

mucho gracias


---
Message posted from http://www.ExcelForum.com/

thanks guys you help me so much !
I wish that I'm not disturb you too much with my simple questio

--
Message posted from http://www.ExcelForum.com

No, I intentionally put two loops in.
Compiles OK for me - did you see a syntax error?

--
Regards,
Tom Ogilvy


"Frank Kabel" wrote in message
...
Hi Tom
I think you just forgot to delte one 'For i 0 1 to 10' statement :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Tom Ogilvy wrote:
Dim CoutUnitaire(10) As Variant
Dim i As Integer


Worksheets("Mold base").Activate
Range("c17").Activate

For i = 1 To 10

CoutUnitaire(i) = _
Application.VLookup(ActiveCell.Offset(i, 0).Value, _
Worksheets("ListePrix").Range("A:d"), 4, False)
Next i

for i = 1 to 10
if iserror(CoutUnitaire(i)) then
msgbox "Value was not found"
else
msgbox "Value found with results of " & CoutUnitaire(i)
End if
Next

End Sub


If you don't use WorksheetFunction as a qualifier to Vlookup, it
doesn't return a trappable error - it returns an error equivalent to
those found in Cells (#N/A as an example).

which will be shown like this:

? CVErr(xlErrNA)
Error 2042

You can examine these in your coutUnitaire array which I have Dimmed
as Variant so it can hold the error value.


"zapatista66 " wrote in
message ...
The error occurs because I have no data in some cells at this
moment.
But I know that in some weeks i will fill up more cells. I don't
want
to change my programmation.

mucho gracias


---
Message posted from http://www.ExcelForum.com/

Hi Tom
sorry for that - missed your 'next i' in the middle

--
Regards
Frank Kabel
Frankfurt, Germany


Tom Ogilvy wrote:
No, I intentionally put two loops in.
Compiles OK for me - did you see a syntax error?


"Frank Kabel" wrote in message
...
Hi Tom
I think you just forgot to delte one 'For i 0 1 to 10' statement :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Tom Ogilvy wrote:
Dim CoutUnitaire(10) As Variant
Dim i As Integer


Worksheets("Mold base").Activate
Range("c17").Activate

For i = 1 To 10

CoutUnitaire(i) = _
Application.VLookup(ActiveCell.Offset(i, 0).Value, _
Worksheets("ListePrix").Range("A:d"), 4, False)
Next i

for i = 1 to 10
if iserror(CoutUnitaire(i)) then
msgbox "Value was not found"
else
msgbox "Value found with results of " & CoutUnitaire(i)
End if
Next

End Sub


If you don't use WorksheetFunction as a qualifier to Vlookup, it
doesn't return a trappable error - it returns an error equivalent
to
those found in Cells (#N/A as an example).

which will be shown like this:

? CVErr(xlErrNA)
Error 2042

You can examine these in your coutUnitaire array which I have
Dimmed
as Variant so it can hold the error value.


"zapatista66 " wrote
in
message ...
The error occurs because I have no data in some cells at this
moment. But I know that in some weeks i will fill up more cells. I
don't want to change my programmation.

mucho gracias


---
Message posted from http://www.ExcelForum.com/