Creating VBA Vlookup for 3 columns

[arnoudholtzer]

Hi ALL,

Can anyone help me out creating a vlookup in macro? Two reasons that i need it to be macro instead of normal formula.
- I need to have empty fields in the exceltable itself and where i got 3 columns that need to be autofilled by vlookup if one of the fields is filled in manually.
- Also i need to get rid of an itterations error as the following formula =if.error(Vlookup(C5,'Sheet2'!A:C;2;False),"") in an example in cell B5 looks up in it's own cell if there's an error and then fills nothing instead of n/a or 0
I don't know if i can add my example excel sheet to this message as i do not see an upload button here... Therefor i just pasted the 2 tables since it's a basic example.

Below is an example of sheet1:

Ordernr	Number x	Number y	Description
1​	3232222​	2233.123.12344	Apple
2​	4331311​	2233.133.12345	Banana
3​	1123123​	vlookupmacro	vlookupmacro
4​	=if.error(Vlookup(C5,'Sheet2'!A:C;2;False),"")	1233.113.15555	vlookupmacro
5​	vlookupmacro	vlookupmacro	Berry
6​	vlookupmacro	vlookupmacro	vlookupmacro
7​	vlookupmacro	vlookupmacro	vlookupmacro

Below is an example of the datatable to lookup above missing data by vlookup.

Number x	Number y	Description
3232222​	2233.123.12344	Apple
4331311​	2233.133.12345	Banana
1123123​	1243.133.14222	Strawberry
3256666​	1233.113.15555	Plum
1423665​	1233.133.14447	Berry

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Creating VBA Vlookup for 3 columns
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[arnoudholtzer]

Hi,

Sorry, didn't know that.
Here's the link: Also posted it here

 

[arnoudholtzer]

would be nice to also get help with my question, instead of help how to use the strange way to attach a file to the post and getting commented on posting it elsewhere as well due to the following reasons:
1. i really want to find a solution for this
2. due to getting questions like why i post this on another forum and inability to post attachment excel example file or unclear how to which brings a delay in someone actually sending a response to my question,
instead of forumrules that should have been made more foolproof / not a problem in the first place.

 

[arnoudholtzer]

Hi all,

I've found a script that helps me in my way, but not entirely.
Also created a testfile:
link to test file
Right now i got 2 problems with the vlookup macro:
1. i need to have the macro input the result instead of the formula into the cells.
2. i get an error trying to automate the script to trigger the macro on change in column C, instead of a button.

attached the examplefile with the macro i got so far. (see also below)

Sub Autpen()
Dim lrData As Long

lrData = Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row

With Sheets("Sheet1")
.Range("D2:D" & .Range("C" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(C2, 'Sheet2'!C$2:E$" & lrData & ",3, FALSE) , """")"
End With

End Sub

Private Sub Worksheet_Calculate()
'Updateby Extendoffice
Dim Xrg As Range
Set Xrg = Range("C2:C29")
If Not Intersect(Xrg, Range("C2:C29")) Is Nothing Then
Autpen
End If
End Sub

 

[arnoudholtzer]

A workable sollution was given by "PCI" On this forum:

Creating VBA Vlookup for 3 columns

Hi ALL, Can anyone help me out creating a vlookup in macro? Two reasons that i need it to be macro instead of normal formula. - I need to have empty fields in the exceltable itself and where i got 3 columns that need to be autofilled by vlookup if one of the fields is filled in manually. -...

www.excelforum.com