CODEWITHSUNDEEP
vbaexcel
Schott.Ben
Schott.Ben

Reputation: 69

Issue with taking a number and searching for it in another form

I am attemping to ask for a employee id number then searching another form to find this employee. I know for a fact that my Mail_Alert works and PN is copying the number over but it is not detecting the side of the other form.

Sub Find_Employee()
    Dim PN As String
    PN = Worksheets("Sheet3").Cells(1, "B").Value
    Range("B2") = PN
    Worksheets("Sheet4").Activate
    Range("C2").Activate
    Do Until IsEmpty(ActiveCell.Value)
        If ActiveCell.Value = "PN" Then
        Call Mail_Alert
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Upvotes: 1

Views: 50

Answers (2)

Nikolaos Polygenis
Nikolaos Polygenis

Reputation: 691

Please make your Code better, don't Use Worksheets("Sheet4").Activate :

Option Explicit

Sub Find_Employee()

Dim PN As String
Dim sh As Worksheet

Set sh = Worksheets("sheet4")

PN = Worksheets("Sheet3").Cells(1, "B").Value
Range("B2") = PN

With sh

Do Until IsEmpty(.Range("C2").Value)

    If .Range("C2").Value = PN Then
    Call Mail_Alert
    End If


Loop

  .Activate 'activate sheet because wants to select Range("C2").Offset(1, 0) for some reason

Range("C2").Offset(1, 0).Select

End With






End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71197

Dim PN As String

Here PN is a variable, a programmatic symbol.

If ActiveCell.Value = "PN" Then

Here "PN" is a string literal, a value that a symbol can have, like ActiveCell.Value can. But the condition probably means to verify whether ActiveCell.Value matches whatever PN (the variable) contains, not "PN" (the literal value).

In VBA double quotes denote string literals. Remove them.

Upvotes: 2

Related Questions