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]

It is a number.
Sorry for the delay in replying.

 

[VoG]

In that case, assuming it is an integer it should be

Dim pno As Long

 

[Norie]

Try adding worksheet references for things like Columns.

 

[gmazza76]

Good Afternoon,

I have changed pno to long and the VB debugs at "pno = cells"

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

    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

Could this be anything to do with using Excel 2003 at home and 2007 at home?

Sorry for keep messaging you but i need to get this up and runnin as soon as.

Cheers
Gavin

 

[VoG]

What exactly is in D11? What does

=ISNUMBER(D11)

return?

 

[gmazza76]

Afternoon,

I thought i had named the correct paramaters as follows for the info being pulled in

DValue = Cells(ExtractRow, 1)
Sheets("Amend Quality").Cells(7, 7) = DValue

What do you mean name the colums?

Cheers

 

[gmazza76]

A reference number is in D11 "1-55554255" this needs then to be found in column 5 depending on what sheet D4 points the VB to

 

[VoG]

That is a string then, not an integer.

Sorry, I don't know why it isn't working.

 

[gmazza76]

=isnumber(D11) turns up false

 

[VoG]

That's because it is a string. Try declaring pno as String then

Set MyFind = Columns(5).Find(what:=pno, LookIn:=xlValues, lookat:=xlWhole)