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.

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

my-zinc.xlsm

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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(

Case 5.81 To 6.31: strValue = WorksheetFunction.VLookup(

Case 6.32 To 6.81: strValue = WorksheetFunction.VLookup(

Case 6.82 To 7.31: strValue = WorksheetFunction.VLookup(

Case 7.32 To 7.81: strValue = WorksheetFunction.VLookup(

Case Is > 7.81: strValue = WorksheetFunction.VLookup(

End Select

ActiveCell.Offset(0, -6).Value = strValue

ActiveCell.Offset(1, 0).Select

Next x

End Sub

Lemme know if it works for you.

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?

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.

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(

Case 5.81 To 6.31: strValue = WorksheetFunction.VLookup(

Case 6.32 To 6.81: strValue = WorksheetFunction.VLookup(

Case 6.82 To 7.31: strValue = WorksheetFunction.VLookup(

Case 7.32 To 7.81: strValue = WorksheetFunction.VLookup(

Case Is > 7.81: strValue = WorksheetFunction.VLookup(

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(

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.

For Each row In rng1

Select Case ActiveCell.Value

Case Is < 5.81: strvalue = WorksheetFunction.VLookup(

Case 5.81 To 6.31: strvalue = WorksheetFunction.VLookup(

Case 6.63 To 6.81: strvalue = WorksheetFunction.VLookup(

Case 6.82 To 7.31: strvalue = WorksheetFunction.VLookup(

Case 7.32 To 7.81: strvalue = WorksheetFunction.VLookup(

Case Is > 7.81: strvalue = WorksheetFunction.VLookup(

End Select

Cells(ActiveCell.row, 13).Value = strvalue

If IsEmpty(ActiveCell.Offset(

ActiveCell.Offset(1, 0).Select

End If '<----- Enter END IF here; it might be the issue.

Next row

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(

Case 5.81 To 6.31: strvalue = WorksheetFunction.VLookup(

Case 6.63 To 6.81: strvalue = WorksheetFunction.VLookup(

Case 6.82 To 7.31: strvalue = WorksheetFunction.VLookup(

Case 7.32 To 7.81: strvalue = WorksheetFunction.VLookup(

Case Is > 7.81: strvalue = WorksheetFunction.VLookup(

End Select

Cells(ActiveCell.Row, 13).Value = strvalue

If IsNumeric(ActiveCell.Offse

ActiveCell.Offset(1, 0).Select

End If

Next Row

This one worked for me with your new sample.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial