Search Query

[gmazza76]

Good Afternoon,

I am trying to create a facilty to search an number of spreadsheet via 2 criteria 1. Manager Name and Order No, but i am having an issue starting the VB as i need to name the 2 cells on a sheet called "Amend Quality" so i can run a module. I have tried a few ways but get a fault wach time.

What is the best way to name cell "D4" - Manager & "D11" - Order No. so they can be used in the module.

I have the following that i hopefully can use

If "D4" = "Manager 1" Then
ActiveWorkbook.Sheets("Ashley Longman").Activate
                Set ws = Worksheets("Ashley Longman")
 
Set MyFind = Columns(5).Find(what:="D11", searchdirection:=xlNext, lookat:=xlWhole)
If Not MyFind Is Nothing Then
    ExtractRow = MyFind.Row
Else
    MsgBox "Cannot find value " & MyProcessNumber
    Exit Sub
End If
Set MyFind = Nothing
 
'VB then to pull in info

Is there any way that is best to run this as i have 3 managers the Macro will need to search through. Currently using the VB above i will have to create this on a loop 3 times. Is there anyway i can do this without looping the VB. I used to use the code below but i am having problems with it even though all the info is Public for the names

For Each MySht In MyWB.Sheets
        If MySht.Name = MyManager Then
            MySht.Select
            Exit For
        End If
    Next MySht

Many Thanks

 

[VoG]

This is not not all clear.Maybe like this

Set MyFind = Columns(5).Find(what:=ws.Range("D11").Value, lookat:=xlWhole, LookIn:=xlValues)

 

[gmazza76]

As the D11 is in the "Amend Quality" sheet in the workbook, would i have to specify the sheet that "D11" is on.

I am using a sheet called "Amend Quality" as a template to pull in information from 1 of 3 other sheets "Manager 1, "Manager 2" & "Manager 3"

I have had to go back to VB that isnt complecated but takes a lot of typing

Cheers

 

[VoG]

So is it?

Set MyFind = Sheets("Ammend Quantity").Columns(5).Find(what:=ws.Range("D11").Value, lookat:=xlWhole, LookIn:=xlValues)

 

[gmazza76]

Afternoon,

Sorry about keep email over the same thing but i have tried the following

    Dim mgrname As String
    Dim pno As String
    
    Cells(4, 4) = mgrname
    Cells(11, 4) = pno

    If mgrname = ("Samantha Roberts") Then
        ActiveWorkbook.Sheets("Samantha Roberts").Activate
    
    Set MyFind = Columns(5).Find(what:=pno, searchdirection:=xlNext, lookat:=xlWhole)
    If Not MyFind Is Nothing Then
        ExtractRow = MyFind.Row
    Else
        MsgBox "Cannot find value " & pno
        Exit Sub
    End If
    
    DValue = Cells(ExtractRow, 1)
    Sheets("Amend Quality").Cells(7, 7) = DValue
    DAdvocate = Cells(ExtractRow, 2)

I have the issue where i want it to check the name and go to a sheet as stated above "Samantha Roberts" but it doesnt seem to go to it, it just jumps directly to the end of the End Sub and closes.

Can you give me any suggestions

Cheers

 

[VoG]

Shouldn't this

    Cells(4, 4) = mgrname
    Cells(11, 4) = pno

be

    mgrname = Cells(4, 4).Value
    pno = Cells(11, 4).Value

 

[gmazza76]

Thanks for that it was spot on for matching the reference number on the correct sheet. The problem i have now is it says it doesnt exist but shows the reference number on the page without pulling the values across

Is there anything i can do for this to work.

Cheers

 

[VoG]

There might be leading or trailing spaces. Do either of these work

Set MyFind = Columns(5).Find(what:=Trim(pno), searchdirection:=xlNext, lookat:=xlWhole)

Set MyFind = Columns(5).Find(what:="*" & pno & "*", searchdirection:=xlNext, lookat:=xlWhole)

 

[gmazza76]

Afternoon,

I have tried both and it just says it cannot find it but highlights the cell in "Samantha Roberts" Sheet

 

[VoG]

Is pno definitely a string and not a number?

What happens if you search for it manually?