VLookup within Range

Question

Hello, I've been finally on a roll with my project, but now I've run into a speedbump. The following is quite useful in my Vlookup. However, now the variable I need will fall with a range. I just need the variable match with what you see below the macro.

Sub Domestic_Ops()

  Dim i As Long, n As Variant, v As Variant 

If InStr(Range("H8"), "Domestic Ops") > 0 Then 'Only if Domestic Ops is in cell

For i = 6 To Split(Worksheets("MSN Decoder").UsedRange.Address, "$")(4) 

    n = Mid(Worksheets("MSN Decoder").Cells(i, "K").Value, 5, 5) ''' Extracts the 5-9 Character Position 

      v = Application.VLookup(n, Worksheets("Domestic Ops").Range("A:B"), 2, 0) 

    If IsError(v) Then v = Application.VLookup(Val(n), Worksheets("Domestic Ops").Range("A:B"), 2, 0) 

    If Not IsError(v) Then Worksheets("MSN Decoder").Cells(i + 12, "H").Value = v 

Next i 

End If

End Sub

This works great when I had a very specific value in column A on Worksheet "Domestic Ops".

However I have values like in Column A

100XX - 299XX series Federal Funded

300XX - 499XX series State Funded

500XX - 599XX series Private Funded

The XX will have the following Possibilities

AR Agriculture

CD Commodities

EL Electronic

VH Vehicles

This means if the 5-9 characters were 168AR the result would be "Federal Funded-Agriculture"

584EL would give me "Private Funded-Electronic". If the numbers don't exist then it just ignore the macro. In other words I don't want to get an error message.

I thought I could have two macros one for characters 5-7 and the other for characters 8-9. However for the second macro I would need the result added to the first one with "-" in-between.

Thank you so much!

Answer 1

have a cell that is 12 characters long, and the 5-9 characters happen to be 138AR.

sorry I can not visualize the detail of what you mean.

if possible，can you share some specific data and expected result rather than description？

Answer 2

Thank you for getting back,

If I have a cell that is 12 characters long, and the 5-9 characters happen to be 138AR. Can I have a VLOOKUP where rather than listing all the Variables from 100AR-299AR individually and I have it set as a range. Looking back at my original post I have a basic VLOOKUP macro that does work. I just want it to work for a range of numbers in Column and not have to list every single option in a different row.

Answer 3

if the 5-9 characters were in the 100AR- 299AR?(sorry I do not get the meaning)

5-9 characters is mid("100AR- 299AR",5,4)=R- 2

why R-2 result to Federal Funded - Agriculture?

rather than AR?characters 4-5

I'm trying to find out if Column A can be left as 100AR-299AR in one row or do I have to list every single number as 100AR, 101AR, etc. all the way to 299A in separate rows

100AR-299AR original

serial

become

discrete value

Answer 4

The final result would be

Federal Funded - Agriculture if the 5-9 characters were in the 100AR- 299AR.

I'm trying to find out if Column A can be left as 100AR-299AR in one row or do I have to list every single number as 100AR, 101AR, etc. all the way to 299A in separate rows. As you can see that would be massive.

Thank you,

Answer 5

original data

100AR- 299AR series

expected result

Agriculture

AR return Agriculture?