vlookup on temp file

[ksp]

Hi Folks, I need some help on the vlookup code.

Very often I have to open files from my mails for which I have to do a vlookup manually. Each time I have to reference the vlookup manually with the other file which is saved on my laptop.(staff details). Is there a way to run the macro directly on this temporary file that I open from the mail . I tried the below
Sub anywherelookup()

Dim y As Integer
Dim MyLookup As Worksheet

MyLookup = Worksheets("Sheet1").Range("a1:I27") ' this file at the "E:\Backup_27 Feb\Macro\staff details.xlsx" location

y = InputBox("Select to Column where data should be displayed in the temp file from the mail")

For x = 2 To 26
ActiveSheet.Cells(x, y).Value = Application.WorksheetFunction.VLookup(ActiveSheet.Cells(x, 1).Value, MyLookup, 9, False)
Next x

End Sub

Regards

Karan

[mikerickson]

You might try this:

Sub test()
Dim DataWorkbook As Workbook
Dim DataRange As Range
Dim PathToData As String
Dim DataWBName As String
Dim ResultRange As Range

PathToData = Application.GetOpenFilename
If PathToData = "False" Then Exit Sub: Rem cancel pressed
DataWBName = Mid(PathToData, InStrRev(PathToData, Application.PathSeparator) + 1)

On Error Resume Next
If Workbooks(DataWBName) <> DataWBName Then
Set DataWorkbook = Workbooks.Open(PathToData)
Else
Set DataWorkbook = Workbooks(DataWBName)
End If
On Error GoTo 0

Set DataRange = DataWorkbook.Sheets("Sheet1").Range("A1:I27")

ThisWorkbook.Activate

Do
On Error Resume Next
Set ResultRange = Application.InputBox("select the result column with the mouse", Type:=8)
On Error GoTo 0

If ResultRange Is Nothing Then Exit Sub: Rem cancel pressed

If ResultRange.Column = 1 Then MsgBox "Do not select a cell in column A"
Loop Until ResultRange.Column <> 1

With ResultRange.Cells(1, 1)
With Application.Intersect(.EntireColumn, .Parent.Range("2:26").EntireRow)
.FormulaR1C1 = "=VLOOKUP(RC1," & DataRange.Address(True, True, xlR1C1, True) & ",9,false)"
.Value = .Value
End With
End With
End Sub

[ksp]

Hi mikerickson,

Thanks, works perfectly...but since I am a relatively new to this, could I bother you to interpret the below code in a short paragraph so my understanding in better especially the last bit on the intersect bit.

Many thanks for taking out time on this.

Karan


You might try this:

Sub test()
Dim DataWorkbook As Workbook
Dim DataRange As Range
Dim PathToData As String
Dim DataWBName As String
Dim ResultRange As Range

PathToData = Application.GetOpenFilename
If PathToData = "False" Then Exit Sub: Rem cancel pressed
DataWBName = Mid(PathToData, InStrRev(PathToData, Application.PathSeparator) + 1)

On Error Resume Next
If Workbooks(DataWBName) <> DataWBName Then
Set DataWorkbook = Workbooks.Open(PathToData)
Else
Set DataWorkbook = Workbooks(DataWBName)
End If
On Error GoTo 0

Set DataRange = DataWorkbook.Sheets("Sheet1").Range("A1:I27")

ThisWorkbook.Activate

Do
On Error Resume Next
Set ResultRange = Application.InputBox("select the result column with the mouse", Type:=8)
On Error GoTo 0

If ResultRange Is Nothing Then Exit Sub: Rem cancel pressed

If ResultRange.Column = 1 Then MsgBox "Do not select a cell in column A"
Loop Until ResultRange.Column <> 1

With ResultRange.Cells(1, 1)
With Application.Intersect(.EntireColumn, .Parent.Range("2:26").EntireRow)
.FormulaR1C1 = "=VLOOKUP(RC1," & DataRange.Address(True, True, xlR1C1, True) & ",9,false)"
.Value = .Value
End With
End With
End Sub

[mikerickson]

The inputBox sets the ResultRange variable to one cell. We want to work with rows 2:26 of that cell's column


With ResultRange.Cells(1,1): Rem specify top left cell in case the user selects multi-column range

Rem Intersection of entire column of user's cell with range _
2:26 of its sheet is the return range we want to fill
With Application.Intersect(.EntireColumn, .Parent.Range("2:26").EntireRow)

Rem fill that range with a VLOOKUP formula
Rem search term from column 1 (column A) and the data rage from the dataworbook
.FormulaR1C1 = "=VLOOKUP(RC1," & DataRange.Address(True, True, xlR1C1, True) & ",9,false)"

Rem change those formulas into their values (equivalent to copy pastespecial/values)
.Value = .Value

End With
End With