asked on

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.

Mlanda T

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

RWayneH

ASKER

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

RWayneH

ASKER

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.

Mlanda T

A:A so long as that is sorted in ascending order. You can put a specific range of you can

RWayneH

ASKER

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

RWayneH

ASKER

Yes A:A is in ascending order. there are 18 blank row prior to data starting... I need them there. Is that a problem?

Mlanda T

Shouldn't be. Give it a shot let's see

RWayneH

ASKER

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

RWayneH

ASKER

or should I say the "Exclusion" = Empty. Should it be either Yes or No??

Saurabh Singh Teotia

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...

RWayneH

ASKER

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

Saurabh Singh Teotia

RWayneH.

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

Saurabh...

RWayneH

ASKER

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?

RWayneH

ASKER

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.

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

Thanks for help.