VBA vlookup question

jws125

New Member
Joined
Jun 6, 2015
Messages
3
Hello,

I'm new to vba and I'm trying to automate a few tasks. I have one master excel workbook with multiple sheets. What I'm trying to do is a vlookup using the identifier in cell A1 on a second workbook and return the information on cell B8 of the appropriate sheet. The macro opens the new workbook but I can't get the vlookup to work. Any help in identifying what I'm doing wrong would help.



Sub tbil()
Dim id1 As String
Dim id2 As String


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Reports").Select
Cells.Select
Selection.Delete Shift:=xlUp
Dim startingwb As String
startingwb = ActiveWorkbook.Name
MsgBox ("Open T-BILL file")
ws2 = Application.GetOpenFilename
Workbooks.OpenText Filename:=ws2
Dim opentextfile As String
Dim opentextfile1 As String
opentextfile = ActiveWorkbook.Name

Dim tbill2 As String
Sheets("reports").Select
With ws2
tbill2 = Application.WorksheetFunction.VLookup("A1", ws2.Range("A1:B50").Value, 2, False)
On Error GoTo 0
Range("tbill2").Copy
Range("B8").PasteSpecial.xlPasteValues
End With




End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hello JWS,

I am a little confused about the workbook and sheet name that has the lookup value, the workbook and sheet name of the sheet where you cleared all the cells and why you cleared, the workbook and sheet name that has B8 as the cell for your result. The workbook that has the lookup range I think is ws2/Reports/A1:B50.



But, test this and see if you can use it to update your code:

Code:
Sub tbil()Dim id1 As String
Dim book1 As Workbook
Dim book2 As Workbook


Set book1 = Workbooks("Book1")
Set book2 = Workbooks("ws2")


lookFor = book1.Sheets("Reports").Range("A1")
Set srchRange = book2.Sheets("Reports").Range("A1:B50")


ActiveSheet.Range("B8").Value = _
         Application.WorksheetFunction.VLookup(lookFor, _
         book2.Sheets("Reports").Range(srchRange.Address), 2, False)
         
End Sub
 
Upvote 0

Forum statistics

Threads
1,206,920
Messages
6,075,575
Members
446,147
Latest member
homedecortips

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top