1

I have 2 different workbooks:

  • workbook-1
  • workbook-2

I want msgbox in workbook-1 referring to values in workbook-2.

For example:

In workbook-1:

| A |
| B |
| C |

In workbook-2:

| A | 10 | 5  | 6 | 4 |

| B | 5  | 4  | 2 | 1 |

| C | 5  | 10 | 5 | 6 |

When I give key (A) on workbook-1, it should refer to the values in workbook-2 and show as a msgbox.

Msg box:

| A  |
| 10 |
| 5  |
| 6  |
| 4  |

The code I have so far is:

Sub Search()

Msg = "CALL DETAILS" & vbCr

dim path as String

dim file as String

dim sheet as String

path = "C:\Users\naresh.g\Desktop\vlookup\"

file = "workbook2.xlsx"

sheet = "sheet2"

  If (path & file & sheet) <> "" Then

'i am getting error here as subscript out of range error code 9

'sometime i am getting object require

    For Each cell In Sheets("sheet2").Range("B2:B" & Sheets("sheet2").Cells(Rows.Count, "B").End(xlUp).Row)
        If LCase(cell.Value) = LCase(Selection.Value) Or InStr(1, LCase(cell.Value), _
            LCase(Selection.Value)) > 0 Then
                Msg = Msg & vbCr & cell.Offset(0, 1) & " / " & vbCr & cell.Offset(0, 2) & " / " & cell.Value
        End If
    Next 
    i = MsgBox(Msg, vbInformation)
End Sub 
  • 1
    What exactly is your problem/question? – Twisty Impersonator Sep 29 '18 at 11:36
  • Hi, i want to pick the matching values from closed workbook. – Naresh G Sep 29 '18 at 11:51
  • Above mentioned code is working on the same workbook , but when i give direction and path it shows error. – Naresh G Sep 29 '18 at 11:54
  • Please edit your question to include the error. – Twisty Impersonator Sep 29 '18 at 11:56
  • Hi! Question edited – Naresh G Sep 29 '18 at 12:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.