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

Question

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

Replace `ActiveCell.Value = "PN"` with `ActiveCell.Value = PN` — Victor K, Sep 21 '17 at 13:37
Also [avoid Select and Activate](https://stackoverflow.com/q/10714251/1188513), and [watch for implicit ActiveSheet references](https://stackoverflow.com/a/46324415/1188513). — Mathieu Guindon, Sep 21 '17 at 13:42

score 2 · Accepted Answer · answered Sep 21 '17 at 13:45

2

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.

answered Sep 21 '17 at 13:45

Mathieu Guindon

69,817
8
107
235

thank you for your advice, I fix the errors I did because I didn't check the code carefully...by speed to post a solution. – Nikolaos Polygenis Sep 21 '17 at 15:14

Nikolaos Polygenis · Answer 2 · 2017-09-21T15:27:24.113

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

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

2 Answers2