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

 

[gmazza76]

Afternoon,

I realised what the error is i was trying to name "D11" twice. I have changed it to the original name and it does go past the error, but doesnt upload any information.

I have tried stepping through using F8 but it doesnt pull any info in

new code is

Dim mgrname As String
    
    
    mgrname = Cells(4, 4).Value
    
    If mgrname = ("Samantha Roberts") Then
        ActiveWorkbook.Sheets("Samantha Roberts").Activate
    
    Set MyFind = Columns(5).Find(what:=CellProcessNumber, searchdirection:=xlNext, lookat:=xlWhole)
    If Not MyFind Is Nothing Then
        ExtractRow = MyFind.Row
    Else
        MsgBox "Cannot find value " & CellProcessNumber
        Exit Sub
    End If
    
    DValue = Cells(ExtractRow, 1)
    Sheets("Amend Quality").Cells(7, 7) = DValue
    DAdvocate = Cells(ExtractRow, 2)
    Sheets("Amend Quality").Cells(6, 4) = AdvocateValue 'Advocate Name
    DYourName = Cells(ExtractRow, 3)

Is there anyway i can run through the vb to see why it isnt pulling info in?

Cheers

 

[VoG]

You haven't assigned a value to CellProcessNumber.

 

[gmazza76]

Sorry,

When you say assigned a value, what do you mean?
The reason i am asking this is I am unsure where to go as it has been registered as public.

Also when i click D11 the cell name is shown as this

Cheers

 

[VoG]

You have to give it a value such as =Range("D11").Value