VBA Vlookup to return value LEFT of table

[RobbieC]

I posted this up a few minutes ago, but for some reason the post fails when you click on it...I didn't put anything spurious in there...very strange

I'm basically able to do a VLOOKUP in VBA using:

filetype = Application.WorksheetFunction.VLookup(filename, Sheets("FilesInProject").Range("B2:D1000"), 1, False)

but I now also need to find the corresponding value in column A...

How can I do this?

 

[offthelip]

Have a look at this thread which shows how to use VBA to do a Vlookup, but do it about 800 time faster and to my thinking easier. You never need to use vlookup in VBA
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

Also it works for any column left or righrt

 

[RobbieC]

My brain hurts... I've had a look at that code and I can't work out where to go next

Dim i As Integer
Dim filename_to_match As String
Dim filetype As String
Dim lastrow As Long
Dim lastrowB As Long


lastrow = Sheets("FilesInProject").Range("H" & Rows.Count).End(xlUp).Row
lastrowB = Sheets("FilesInProject").Range("B" & Rows.Count).End(xlUp).Row

filename_to_match = Sheets("FilesInProject").Cells(1, 8).Value 'cell H1

so Ineed to match the value of H1 'filename_to_match' with the same value in column B and then return the value in column A on the same row

confused :S

 

[sheetspread]

The scripting dictionary in VBA is more efficient than vlookup, but with only 1000 rows, you can try:

Excel 2010
	A	B
1	Number	Name
2	3	B
3	8	Q
4	7	O
5	2	N
6	1	C
7	3	Z
8	3	A
9	9	S
10	2	D
11	1	F
12
13
14	O	7
Sheet7

Cell Formulas
Range	Formula
B14	=VLOOKUP(A14,CHOOSE({1,2},$B$2:$B$11,$A$2:$A$11),2,0)

 

[offthelip]

I think I have understood what you atrying to do , try this:

Dim i As IntegerDim filename_to_match As String
Dim filetype As String
Dim lastrow As Long
Dim lastrowB As Long




lastrow = Sheets("FilesInProject").Range("H" & Rows.Count).End(xlUp).Row




lastrowB = Sheets("FilesInProject").Range("B" & Rows.Count).End(xlUp).Row
' load all of the data from file in projenct into a varaint array
inarr = Worksheets("Filesinproject").Range(Cells(1, 1), Cells(lastrowB, 8))


filename_to_match = Sheets("FilesInProject").Cells(1, 8).Value 'cell H1


' loop down column B looking ofr a match
For i = 1 To lastrowB
 If filename_to_match = inarr(i, 2) Then ' column B in the variant array
  ' we found the match
   filetype = inarr(i, 1) ' this is column A
  Exit For
 End If
Next i

Note , totally untested

 

[RobbieC]

offthelip, you are a star! Works perfectly mate!

thanks very much