Search Query

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
771
Office Version
  1. 365
Platform
  1. Windows
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

Code: 
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

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

Many Thanks
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
This is not not all clear.Maybe like this

Code: 
Set MyFind = Columns(5).Find(what:=ws.Range("D11").Value, lookat:=xlWhole, LookIn:=xlValues)
 
Upvote 0
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
 
Upvote 0
So is it?

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

Sorry about keep email over the same thing but i have tried the following
Code: 
    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
 
Upvote 0
Shouldn't this

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

be

Code: 
    mgrname = Cells(4, 4).Value
    pno = Cells(11, 4).Value
 
Upvote 0
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
 
Upvote 0
There might be leading or trailing spaces. Do either of these work

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

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

I have tried both and it just says it cannot find it but highlights the cell in "Samantha Roberts" Sheet
 
Upvote 0
Is pno definitely a string and not a number?

What happens if you search for it manually?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formula - Search multiple values within cell value against a range of values
Replies
1
Views
535
MHamid
M
H
  • Question Question
Searching any field in array
Replies
2
Views
357
jasonb75
J
C
  • Question Question
Fetch domain name with vba in Excel
Replies
2
Views
12K
CatherineH
C
G
  • Question Question
search and Load a Userform issue
Replies
7
Views
833
gmazza76
G
K
  • Question Question
Vlookup formula that returns multiple answers by searching through multiple worksheets in a single workbook
2
Replies
14
Views
2K
mumps
mumps

Forum statistics

Threads
1,224,586
Messages
6,179,712
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top