Help with Vlookup in VBA
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 |
Help with Vlookup in VBA
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 |
Help with Vlookup in VBA
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/ |
Help with Vlookup in VBA
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/ |
Help with Vlookup in VBA
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/ |
Help with Vlookup in VBA
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 |
Help with Vlookup in VBA
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/ |
Help with Vlookup in VBA
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/ |
All times are GMT +1. The time now is 07:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com