Select Case to determine correct lookup table

KatZoo
KatZoo used Ask the Experts™
on
I am trying to write code that will take the value in column H in the sample spreadsheet and use that value to determine the correct column to use in a lookup table (labeld Z in sample).  Once the correct table is located, the value in column A is the range used to determine the value that goes in colum B.  I have used do until, for next, for each next, if---then---elseif statements and the case select.  All codes do the first three values correctly and then assumes all other values are true for the lookup table used for the third sample.  .  I have attached a sample workbook (note:  in my original workbook there are formulas in column A & H that copies the given values from another worksheet to this one).  Also, in column D I have given what the final results in column B should be.
my-zinc.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rodney EndrigaData Analyst

Commented:
You can try this code out:

Sub EE_VlookupFind()
Dim wbk1 As Workbook, sht1 As Worksheet, sht2 As Worksheet, ZNLevel As Variant, strValue As String, rngZ As Range
Set wbk1 = Workbooks("my-zinc.xlsm")
Set sht1 = Sheets("Data")
Set sht2 = Sheets("Z")
Set rngZ = sht2.Range("A14:L19")
wbk1.Activate
sht1.Activate
Range("H3").Select
For x = 1 To sht1.UsedRange.Rows.Count - 2
Select Case ActiveCell.Value
    Case Is < 5.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 5.81 To 6.31: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 6.32 To 6.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 6.82 To 7.31: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 7.32 To 7.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case Is > 7.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
End Select
ActiveCell.Offset(0, -6).Value = strValue
ActiveCell.Offset(1, 0).Select
Next x
End Sub

Lemme know if it works for you.

Author

Commented:
Thanks, thanks, thanks.  Yes, this works; however, when I transferred this to the actual workbook in which this code will run I get an error.  This happens on the last activecell.offset (0,1).  In column H3 on my workbook it is reading a formula that is in that cell.  It has no value because there were only 14 samples in this run.   The next run could have only 1 sample or it could have 500.  Is there anyway to keep this error from happening once it comes to a cell with the formula to get the value?
Rodney EndrigaData Analyst

Commented:
Hi KatZoo,

It should read the result of any formula in that particular cell. In your sample file, I entered the formula "=5+1.13" and "=G2", where both result in some type of value. The macro still ran with no errors.

What type of formula do you have in your file?
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Rodney EndrigaData Analyst

Commented:
Or

You can try to put this:

On Error Resume Next

Enter this ABOVE the following code:

For x = 1 To sht1.UsedRange.Rows.Count - 2

Let me know if this works.

Author

Commented:
The on error resume next did not work.  It kept placing values in what would be column B in the sample I sent you.  The error I get on my original workbook is "run-time error 1004:  Unable to get vlookup property of worksheetfunction. " The formulas are vlookup formulas that gets the values placed in this column from another worksheet and places them in the this worksheet.  The worksheet I sent you "Data" is actually a final report to a client and can only contain test results from the samples they submitted.
Rodney EndrigaData Analyst

Commented:
You can try the following code:

Sub EE_VlookupFind()
Dim wbk1 As Workbook, sht1 As Worksheet, sht2 As Worksheet, _
strValue As String, rngZ As Range, rng1 As Range
Set wbk1 = Workbooks("my-zinc.xlsm")
Set sht1 = Sheets("Data")
Set sht2 = Sheets("Z")
Set rng1 = sht1.Range("H3:H" & sht1.UsedRange.Rows.Count)
Set rngZ = sht2.Range("A14:L19")
wbk1.Activate
sht1.Activate
rng1.Select
For Each Row In rng1
    Select Case ActiveCell.Value
        Case Is < 5.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 5.81 To 6.31: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 6.32 To 6.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 6.82 To 7.31: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 7.32 To 7.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case Is > 7.81: strValue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    End Select
Cells(ActiveCell.Row, 2).Value = strValue  ' You can change the value -2- to reflect a different column. This refers to Column B
If IsEmpty(ActiveCell.Offset(1, 0).Value) = False Then
    ActiveCell.Offset(1, 0).Select
End If
Next Row
End Sub

I'm not sure how much of the Sample Data differs from your actual Production Data, but if this doesn't work is there a chance that you can submit a different sample more like your actual Production Data. You can just fill in values with dummy data. With the Sample Data you provided, the code works with no errors on Excel 2007. I even referenced 5 different numbers that had a Vlookup formula, not an actual value but the Vlookup formula, and it ran successfully.

Author

Commented:
Thanks. This rountine works the same as the other.  When if offsets in column H the last time, it gets the run time error & the debug highlights the part in the routine that says:  
Case is >7.81=strvalue, etc.

I will do a worksheet that is more like the one I actually use that has the vlookup function located in the cells in column H.  The actual workbook contains too much proprietary programming to send.  I thank you for helping me and appreciate your patience.  It may be next week before I can get this too you.

KatZoo

Author

Commented:
I have attached a new file that may be more helpful.  It contains both of the codes you have given to solve this problem.  I've changed a few things to reflect this new file.  All areas in gray, in my original workbook, have data, formulas or functions in them (from the 8th through the 425th row).  Thanks again for your time and help with this.


my-zinc.xlsm
Rodney EndrigaData Analyst

Commented:
Try using an END IF in the after the IF statement in this code:

For Each row In rng1
Select Case ActiveCell.Value
    Case Is < 5.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 5.81 To 6.31: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 6.63 To 6.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 6.82 To 7.31: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case 7.32 To 7.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    Case Is > 7.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    End Select
    Cells(ActiveCell.row, 13).Value = strvalue
    If IsEmpty(ActiveCell.Offset(1, 0).Value) = False Then
        ActiveCell.Offset(1, 0).Select
    End If    '<----- Enter END IF here; it might be the issue.
Next row
Rodney EndrigaData Analyst

Commented:
Hi KatZoo,

Copy and paste the following code (remove the above post):

For Each Row In rng1
    Select Case ActiveCell.Value
        Case Is < 5.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 5.81 To 6.31: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 6.63 To 6.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 6.82 To 7.31: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case 7.32 To 7.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
        Case Is > 7.81: strvalue = WorksheetFunction.VLookup(ActiveCell.Offset(0, -7).Value, rngZ, 2, True)
    End Select
    Cells(ActiveCell.Row, 13).Value = strvalue
    If IsNumeric(ActiveCell.Offset(1, 0).Value) = True Then
        ActiveCell.Offset(1, 0).Select
    End If
Next Row

This one worked for me with your new sample.
Data Analyst
Commented:
Another thing, KatZoo, if you are going to use the 'Option Explicit' feature, make sure to declare the variable 'Row' as Variant in your DIM section. If you do not declare this, you will receive an error message of an undeclared variable.

Author

Commented:
It works on the test file I sent; however, on my original file, the code continues to count the rows in column S until it reaches a blank row.  At this time, this is acceptable.  It just slows the program a little.  You're a saint.  Thanks for all your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial