Using VBA to add a VLOOKUP formula without using a named range in the formula

[Wedge120]

How could I have used the VLOOKUP formula (last line of code below) without the use of a named range "MySortedRange"? My interest is in understanding how this could have been achieved prior to the addition of named ranges.

Please Login or Register  to view this content.

[teylyn]

Hello,

just glancing over the code, without actually running it.

- you use Vlookup with a 1 as the return column. That could more easily be achieved with Match() instead, since you are returning the search term if it can ber found. Match() would be much leaner. As a formula, something like

=if(Match(lookupTerm, LookupRange, 0),lookupTerm,"")

- you use Vlookup with "False" as the last parameter. This does not require the lookup range to be sorted at all. It can be in any order. So why go through the sorting routine in the first place?

What are you trying to achieve?

cheers,

[Wedge120]

Hello,

just glancing over the code, without actually running it.

- you use Vlookup with a 1 as the return column. That could more easily be achieved with Match() instead, since you are returning the search term if it can ber found. Match() would be much leaner. As a formula, something like

=if(Match(lookupTerm, LookupRange, 0),lookupTerm,"")

I see what you are saying; I chose the VLOOKUP simply because it is what I am familiar with. Obviously, I need to learn about the MATCH function. Still, my question applies here as well though. How would I designate "LookupRange" if using VBA to accomplish this task? The range would be dynamic in that the number of rows will not always be the same. Following your example, I guess I could have said:

Please Login or Register  to view this content.

I am not sure if "J:J" would have worked or not.

- you use Vlookup with "False" as the last parameter. This does not require the lookup range to be sorted at all. It can be in any order. So why go through the sorting routine in the first place?

I thought sorting in ascending order was required for this.

What are you trying to achieve?

I am trying to write a macro that looks up values in one sheet or one workbook and finds them in another sheet or another workbook where the columns of info are known, but the number of rows will change. I was simply wondering how to write a formula such as VLOOKUP, where the range argument is always changing. Using a range that is named is easy enough, but I thought named ranges were new to Excel 2007 or 2010. So I wanted to understand VBA code for inserting this type of function without using named ranges.

[teylyn]

Range names have been around for quite a while.

Why are you doing this with a macro? All the macro seems to do is to put a formula into a cell. So why not enter the formula into the cell manually? Are you creating the sheet with a macro and is the code above just a part of a bigger procedure?

I am not sure if "J:J" would have worked or not.

It would. Match can use whole column ranges. But thinking about this a bit more, it will throw an error when no match is found, so better use something like

=if(iserror(Match(J2,J:J,0)),"",lookupTerm)

I thought sorting in ascending order was required for this.

It is not if the last parameter is "False" or 0 (for both Match and Vlookup). If the last parameter is "True" or 1 (or omitted), then the range needs to be sorted ascending.

[Wedge120]

Range names have been around for quite a while.

Why are you doing this with a macro? All the macro seems to do is to put a formula into a cell. So why not enter the formula into the cell manually? Are you creating the sheet with a macro and is the code above just a part of a bigger procedure?

I am doing this with a macro because my coworkers asked me to. You're right, this is simple enough to just enter the formula manually. I am just doing what I was asked to do (my coworkers aren't familiar with some of the basics of functions), but I really enjoy learning how to write VBA code anyway.

What I am trying to do is highlight (in yellow) the order numbers in Column J of workbook "export", sheet "export", that are found in column J of workbook "Criticals export", sheet "export".

I was going to do all steps via macro button placed in the first workbook, "export", i.e., the macro would automatically open up the second workbook "Criticals export" and do everything necessary to insert the formula that runs the lookup. Again, I chose VLOOKUP because I was familiar with it. I'm not implying it's the best choice, but it works even if my procedure is less than elegant. Feel free to show me other methods; I am sure my way is unnecessarily convoluted. I realize I have a lot to learn.

Please Login or Register  to view this content.

My original question was a curiosity about how to code this using a VLOOKUP in VBA code, and I still would like to know how to place a range argument in the formula without use a named range. Is that possible to do when the range might be changing?

[teylyn]

If the range for the Vlookup is constantly changing, then I'd use a dynamic range name instead of a static range name. You can create the dynamic range name via VBA, but since it only needs to be created once, it can also be done via the Name Manager.

If you run the macro every time the range changes, then you can use code to refer to the changing range, since the code evaluates the range and determines the last row of data. As an example of the syntax for this see:

Please Login or Register  to view this content.

cheers,

[Wedge120]

Okay thanks. That answers my original question. I did not know I could use quotes to break into the middle of the formula to add my ' & Finalrow & ', then begin quotes to finish the formula. i.e., I didn't know you could concatenate in the middle of a formula. Makes sense to me.

Also, this showed me something I was forgetting to do -- use absolute references.

That said, I'm going to see if I can't use your suggested MATCH formula

Thanks a lot for your help.