VBA - Fixing Range Data in Code for VLOOKUP

[MixedUpExcel]

Hi All,

I don't know if this has been answered as I don't know how to word the question.

I want to put some code together which will do the following:

Put the code in Cell A1

vlookup(B1,namedrange,2,0)

I can do the above, however, I want to fix the namedrange within the code as it will always be the same.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]REP NAME[/TD]
[TD]REGION[/TD]
[/TR]
[TR]
[TD]REP 1[/TD]
[TD]NORTH[/TD]
[/TR]
[TR]
[TD]REP 2[/TD]
[TD]NORTH[/TD]
[/TR]
[TR]
[TD]REP 3[/TD]
[TD]SOUTH[/TD]
[/TR]
[TR]
[TD]REP 4[/TD]
[TD]SOUTH[/TD]
[/TR]
[TR]
[TD]REP 5[/TD]
[TD]EAST[/TD]
[/TR]
[TR]
[TD]REP 6[/TD]
[TD]EAST[/TD]
[/TR]
[TR]
[TD]REP 7[/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]REP 8[/TD]
[TD]WEST[/TD]
[/TR]
[TR]
[TD]REP 9[/TD]
[TD]WEST[/TD]
[/TR]
</tbody>[/TABLE]

The data in Cell B1 will be one of the Reps and I want to run the Macro to automatically insert the Region into Cell A1.

How do I store the namedrange within my code? And then make sure that my vlookup calls that data correctly?

Thanks in advance.

Simon

 

[Norie]

Simon

Do you actually have a named range?

If you do there's no reason to 'store' it in the code, you can use it in the formula as it is .

Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP(B1,namedrange,2,0)"

 

[MixedUpExcel]

Hi Norie,

No. I don't currently have a named range as I will be using this code (saved in my Personal Macro Workbook and then a short cut on my Excel Ribbon) against Multiple Workbooks (different Categories etc).

Hence, needing to store the data within a named range in the code.

Thanks.

 

[Norie]

Not sure I follow.

Do you mean you want to set the lookup range in the code?

If you do there's no need for a named range, you could use a standard range reference.

 

[MixedUpExcel]

Hi.

No.

I mean: instead of having the Cell Range A1 to B10 on an actual spreadsheet, I want to create a look up that refers to the 2 columns of data but from within the coding - not an actual sheet.

eg.

Range{Rep1, Rep2, Rep3, Rep4; North, North, South, South}

How would I set this out so that I can put the formula in Cell A1.. using Cell B1 as the Criteria (in this case Rep3), so my lookup would look at the Range (above) find Rep3 and return the Value South.

As I put right at the start, I'm not sure how to word what I am looking for... maybe there is a better way that doing it as a vlookup (it was the only way I could think of if it was using an actual formula against actual data on a sheet) - but this isn't actual data on a sheet.

Does that explain it any better?

Thanks.

Simon

 

[Norie]

Simon

Do you mean you have an array, or some similar data structure, in the code and you want to use that in a lookup?

 

[MixedUpExcel]

Hi Norrie,

Yes please.

The word was escaping me, which is why I showed it in an array (ish) format.

Any ideas how I could do that with the info I had from my first post?

Thanks.

S.