Experts Exchange connects you with the people and services you need so you can get back to work.
Your question, your audience. Choose who sees your identity—and your question—with question security.
Open in new window
Private Sub CommandButton1_Click()
Dim i As Long
Dim strSearch As String
Dim aCell As Range
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Set wb1 = ActiveWorkbook
Set ws1 = wb1.Sheets("Expansion Joint Checklist")
'~~> Change Path here
Set wb2 = Workbooks.Open("C:\SampleExpJoint-SpreadSheet.xls")
Set ws2 = wb2.Sheets("EXP JOINT LIST")
strSearch = ws1.Range("H5").Value
Set aCell = ws2.Cells.Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
If Not aCell Is Nothing Then
i = aCell.Row
MsgBox "the row that you want is " & i
'~~> Enter the description in sheets EXP JOINT LIST
'ws2.Range("N" & i).Value = ws1.Range("J6").Value
' and so on update the rest
MsgBox strSearch & " not found in " & wb2.Name
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.