Vlookup VBA by select range from another workbook

[edo.gv6]

Hi Guys,

I am Rere and I newbie in this forum, please teach and guide me.

I have some issue to transform the formula "=IF(BU7="","",VLOOKUP(BU7,'\\192.168.10.26\Data\CoE\[COEKursTemplate.xlsx]Sheet1'!$H$4:$I$8,2,0))" to VBA Excel, I try to combine "ThisWorkbook.Path" and "Application.WorksheetFunction.VLookup" however still not meet the correct code.

This is my last code :

Private Sub bt_rate_Click()
Dim ratedate As Range
Dim Rate As Range

ratedate = Worksheets("IQ").Range("ratedate")
Rate = Worksheets("IQ").Range("rate")

Dim vlookrange As Range
vlookrange = Worksheets("Sheet1").Range("H4:I8")

Dim urlfile As String
urlfile = ThisWorkbook.Path & "\\192.168.10.26\Data\CoE\COEKursTemplate.xlsx" & vlookrange

Rate = Application.WorksheetFunction.VLookup(ratedate, urlfile, 2, 0)

End Sub

Please correct me where I am wrong

Thank you....

[mancubus]

welcome to VBAX.

please use code tags (via # button) when pasting your code.

try below code. if it does not work, post your workbooks (go advanced, manage attachments, select files, upload).

notice how object variables are assigned to objects.



Sub vbax_53514_VLookUpVBA()

Dim wb As Workbook
Dim RngRatedate As Range, RngRate As Range

With Worksheets("IQ")
Set RngRatedate = .Range("ratedate")
Set RngRate = .Range("rate")
End With

On Error Resume Next
Set wb = Workbooks("COEKursTemplate.xlsx")
On Error GoTo 0

If wb Is Nothing Then 'wotkbook is not open
Set wb = Workbooks.Open("\\192.168.10.26\Data\CoE\COEKursTemplate.xlsx")
End If

RngRate.Value = Application.WorksheetFunction.VLookup(RngRatedate.Value, wb.Worksheets("Sheet1").Range("H4:I8"), 2, False)

wb.Close False 'close network file without saving. delete this line if you want workbook open

End Sub

[edo.gv6]

Hi Mancubus,

Wow it's helpful and works...:rofl::rotlaugh::rotlaugh::friends:

However I just want to confirm about the process, whether the process vlookup will opened the file although just in sec time??

Regards,

Rere

[mancubus]

you are welcome.

that's why we seek ways to automate tasks which are done manually. :)

please mark the thread as solved from thread tools for future reference.

[edo.gv6]

Hi Mancubus,

If I want to add condition where range "ratedate" empty then msgbox appear and when msgbox clicked the process stop it is correct to add

On Error Resume Next

On Error GoTo 0

[edo.gv6]

Hi Mancubus,

Done.....and works by add

With Worksheets("IQ")
Set Ratedate = .Range("ratedate")
If Ratedate.Value = 0 Then
MsgBox "Date Rate is Empty..", , "WARNING.....!!!!!!"
Exit Sub
End If
Set Rate = .Range("rate")
End With

Thank You

[mancubus]

welcome. glad you solved it.


but...
i cant see the code tags.
use them.
they are free.... :D