VLOOKUP on a hidden sheet tab?

Is there a way to do a VLOOKUP on a hidden sheet?  I have a value stored in a vaiable called,  "MatlNumber" and I would like to know if it is in column A of a sheet tab called "ChkItemCatG".  If it is there? store a "Yes" in a variable called Exclusion, if vlookup is false store a No in a variable called NoExclusion.  Issue is that I would like to hide the sheet tab called "ChkItemCatG".

How would this look in vba and is it even possible to do this on a hidden sheet?  Please advise and thanks.
RWayneHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
If the MatlNumber is not present in the table then VLOOKUP throws a “1004 Error”.
    Sub CheckItemCategory(ByRef MatlNumber as String)  
    On Error GoTo MyErrorHandler:  
         Dim value As String
         value = Application.WorksheetFunction.VLookup(MatlNumber, ChkItemCatG.Range("A:A"), 1, False)
        Exclusion = "Yes"
    Exit Sub  
    MyErrorHandler:  
    If Err.Number = 1004 Then  
      'Value Not Present in the table
      NoExclusion = "No"
    End If  
    End Sub  

Open in new window

0
RWayneHAuthor Commented:
Can I use whatever "value" is?  If value is not blank and if value is blank?  if there is a value or it finds something in vlookup?  I tested one that is not blank and it came back as blank??

    Dim value As String
        value = Application.WorksheetFunction.VLookup(MatlNumber, ChkItemCatG.Range("A:A"), 1, False)
    If Not value = "" Then
        c.Offset(0, 1).value = "MatlNumber in ExclusionTable"
    End If

Open in new window

0
RWayneHAuthor Commented:
Add we can assume that MatlNumber is always there.  Does it matter if A:A has some blank cells at the top?  We do not have to do the error check.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

MlandaTCommented:
A:A so long as that is sorted in ascending order. You can put a specific range of you can
0
RWayneHAuthor Commented:
Having issues getting "Exclusion" a value??

On Error GoTo MyErrorHandler:
         Dim value As String
         value = Application.WorksheetFunction.VLookup(MatlNumber, ChkItemCatG.Range("A:A"), 1, False)
         Exclusion = "Yes"

MyErrorHandler:
    If Err.Number = 1004 Then
      'Value Not Present in the table
      Exclusion = "No"
    End If

If Exclusion = "No" Then
        c.Offset(0, 1).value = "NO"
        c.Offset(0, 1).Interior.Color = 255
End If
If Exclusion = "Yes" Then
        c.Offset(0, 1).value = "YES"
        c.Offset(0, 1).Interior.Color = 255
End If

Open in new window

0
RWayneHAuthor Commented:
Yes A:A is in ascending order.  there are 18  blank row prior to data starting...  I need them there.  Is that a problem?
0
MlandaTCommented:
Shouldn't be. Give it a shot let's see
0
RWayneHAuthor Commented:
Not working.. Exclusion is = ""   Can we write the checking for Error out?  MatlNumber will always exist.

On Error GoTo MyErrorHandler:
         Dim value As String
         value = Application.WorksheetFunction.VLookup(MatlNumber, ChkItemCatG.Range("A:A"), 1, False)
         Exclusion = "Yes"

MyErrorHandler:
    If Err.Number = 1004 Then
      'Value Not Present in the table
      Exclusion = "No"
    End If

If Exclusion = "No" Then
        c.Offset(0, 1).value = "NO"
        c.Offset(0, 1).Interior.Color = 255
End If
If Exclusion = "Yes" Then
        c.Offset(0, 1).value = "YES"
        c.Offset(0, 1).Interior.Color = 255
End If

Open in new window

0
RWayneHAuthor Commented:
or should I say the "Exclusion" = Empty.  Should it be either Yes or No??
0
Saurabh Singh TeotiaCommented:
RWayneH,

I don't think the issue is in the code..Issue can be the data which you are picking it up..Can you post your sample file with dummy data as it becomes easy to write a code for you then..

Saurabh...
0
RWayneHAuthor Commented:
There is so much else that it does it will confuse the issue.  When I use a formula it finds it??  Need to remove the Error check... "MatlNumber" always exists....  I carved up the sheet tab that will be hidden.  The Error check is confusing me... not getting Exclusion defined... it is Empty...  value is = ""
VLOOKUPEx1.xlsx
0
Saurabh Singh TeotiaCommented:
RWayneH.

The workbook doesn't have the code which are you running..can you give your code as well which you are running..

Saurabh...
0
RWayneHAuthor Commented:
This is working when I added a formula in the active row...

    c.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[3],ChkItemCatG!C[-1],1,FALSE)"
    If c.Offset(0, 1) = "N/A" Then
        c.Offset(0, 1).value = "NO"
        c.Offset(0, 1).Interior.Color = 255
    Else
        c.Offset(0, 1).value = "YES"
    End If

Open in new window


What I really needed was the following command, but it is not returning a value or some to hang on.

value = Application.WorksheetFunction.VLookup(MatlNumber, ChkItemCatG.Range("A:A"), 1, False)

Open in new window


How can I get this line to return something, so I can put an if statement on it?
0
RWayneHAuthor Commented:
Trying to explain the whole process would take a long time...  All that is needed is that MatlNumber has a value stored in a variable.  If that value is in the ChkItemCatG first column I need to know so I can run an if statement on it and place text in a cell of the active rows I am using.
0
Saurabh Singh TeotiaCommented:
If you just want to see if the value exist or not then you can do something like this..

Value = Application.WorksheetFunction.CountIf(Sheets("ChkItemCatG").Range("A:A"), Range("MatlNumber").Value)

If Value > 0 Then
c.Offset(0, 1).Value = "MatlNumber in ExclusionTable"
End If

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
Thanks for help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.