vba - Vlookup Error handling

[Mallesh23]

Hi Team

I want to make my Vlookup dynamic here, if Closed workbooks name has space in it.then

.FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & wbk.Name & "]Sheet1'!R1C1:R10C2,2,0)" 'Run this Code.
'.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & wbk.Name & "]Sheet1!R1C1:R10C2,2,0)" if no space Run this Code.

how to check error and skip it.

Option Explicit
Sub Vlookup_with_space_and_without_Space()
Dim wbk As Workbook
Set wbk = Workbooks.Open(Mac.Range("b5").Value)

With Sheet2.Range("b2:b10")
.FormulaR1C1 = "=VLOOKUP(RC[-1],[" & wbk.Name & "]Sheet1!R1C1:R10C2,2,0)" 'If workbooks dont have space in it.
.FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & wbk.Name & "]Sheet1'!R1C1:R10C2,2,0)" ' If workbooks name Contain space in it.
.Value = .Value
End With

Thanks
mg

End Sub

 

[Akuini]

Try this:

With Sheet2.Range("b2:b10")
    If InStr(wBk.Name, " ") = 0 Then
    .FormulaR1C1 = "=VLOOKUP(RC[-1],[" & wBk.Name & "]Sheet1!R1C1:R10C2,2,0)" 'If workbooks dont have space in it.
    Else
    .FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & wBk.Name & "]Sheet1'!R1C1:R10C2,2,0)" ' If workbooks name Contain space in it.
    End If
        .Value = .Value
End With

 

[Mallesh23]

HI Akuini,

Nice Tricks, it worked Thank you so much.?


Regards,
mg

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.