Search for entered value in another tab

[skittlz]

I have a spreadsheet with multiple tabs. In the tab titled "Cal/PM", The user will make an entry in Column A, and is supposed trigger the code below:

Dim Asset As String
Dim spec As String
Dim cal As String
Dim pm As String


Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the changed cell is column A
    If Not Intersect(Target, Target.Worksheet.Range("A:A")) Is Nothing Then
        Asset = Target.Value
   
    Sheets("Equipment - Initial Entry").Columns("A:A").Find(What:=Asset, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

spec = Trim(ActiveCell.Offset(0, 4)) 'copy spec#
cal = Trim(ActiveCell.Offset(0, 5)) 'copy Cal yes/no
pm = Trim(ActiveCell.Offset(0, 6)) 'copy PM Yes/no
MsgBox spec
    End If
 
End Sub

Basically, the code is "take value entered in "Column A" of "Cal/PM" tab, go to "Equipment - Initial Entry" tab, and get the spec, cal, and PM details.

Unfortunately my "Find" function isn't working - from what I can tell - the code doesn't like to go to a different tab to store the values. How do I get around this issue?

 

[AC PORTA VIA]

this?

Dim Asset As String
Dim spec As String
Dim cal As String
Dim pm As String


Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the changed cell is column A
    If Not Intersect(Target, Target.Worksheet.Range("A:A")) Is Nothing Then
        Asset = Target.Value
Sheets("Equipment - Initial Entry").Select

    Sheets("Equipment - Initial Entry").Columns("A:A").Find(What:=Asset, After:=Range("a1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

spec = Trim(ActiveCell.Offset(0, 4)) 'copy spec#
cal = Trim(ActiveCell.Offset(0, 5)) 'copy Cal yes/no
pm = Trim(ActiveCell.Offset(0, 6)) 'copy PM Yes/no
MsgBox spec
    End If

End Sub

 

[AC PORTA VIA]

or better yet

Dim Asset As String
Dim spec As String
Dim cal As String
Dim pm As String
Dim aCell As Range
Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the changed cell is column A
    If Not Intersect(Target, Target.Worksheet.Range("A:A")) Is Nothing Then
        Asset = Target.Value


     Set aCell = Sheets("Equipment - Initial Entry").Columns("A:A").Find(What:=Asset, After:=Range("a1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

spec = Trim(aCell.Offset(0, 4)) 'copy spec#
cal = Trim(aCell.Offset(0, 5)) 'copy Cal yes/no - not sure what  you are doing with this unless you  use this MsgBox spec & vbCr & cal & vbCr & pm
pm = Trim(aCell.Offset(0, 6)) 'copy PM Yes/no - not sure what  you are doing with this unless you use this MsgBox spec & vbCr & cal & vbCr & pm
MsgBox spec
'or MsgBox spec & vbCr & cal & vbCr & pm
    End If

End Sub

 

[skittlz]

THAT'S IT!! oof what a pain. I had it as your original entry (where sheet was selected as a sperate line item) - but that wasn't working. The only thing that changed from what I can tell is the Set aCell =

What was the problem? Why was it not working? I may have to do it a few more times.