当前位置: 动力学知识库 > 问答 > 编程问答 >

excel vba - Code a huge formula into VBA combining IF and Vlookup

问题描述:

I need to reduce a file I am developing and I know one good way is using VBA. Unfortunately I am not advanced in VBA yet and I am struggling in designing this.

I have a list price organized in three different streams, and I want to combine in one like below:

Stream Site Brand Code Price

Mainstream Boston Brand 01 Formula

Midstream New York Brand 02

Midstream Los Angeles Brand 02

Currently I am using a formula that basically does the following:

=IF(AND(stream="mainstream",Site = "Boston"),vlookup(Brandcode,list 1, 2,0),IF(ANd(stream="midstream", Site = "Boston", vlookup(Brandcode,list 2, 2,0),...))

The formula actually works just fine, the problem is I am testing many other conditions than just this one and thus the file is becoming very heavy, so I wanted to create a VBA code to create either a function or a subroutine, but I am struggling to understand how to do it.

Thanks

网友答案:

This can actually be achieved without using VBA by using named ranges instead. Looking at the formula, it's clear that your lookup range varies based on the combination of stream and site.

You can create a Named Range for each of these lookup ranges. To do so, highlight the range of cells that contains the first lookup group (let's say Midstream New York). Next, press CTRL + F3 to open up the Name Manager. Finally, give this group the name MidstreamNewYork. (Note: you cannot include spaces in the name).

Next, you can update your Vlookup function. You no longer have to include the IF(AND... component because your lookups will be dynamic. Let's say you're inputting a formula on Row 2, the formula would be:

=VLOOKUP(C2,INDIRECT(SUBSTITUTE(A2&B2, " ", "")),2,FALSE)

Let's break down the formula. (1) C2 is just the brandcode (I assumed it was in column C.(2) TheINDIRECTfunction treats aStringas aRange. We are passing in CellsA2andB2, which are "Midstream" and "New York", respectively. We use theSUBSTITUTEfunction to remove the spaces since they aren't allowed. So now, we are looking up a named range calledMidstreamNewYork` (sound familiar?). (3) The rest of the VLOOKUP is standard: lookup the second column, and only match exacts.

Give it a try and let me know if it meets your requirements.

分享给朋友:
您可能感兴趣的文章:
随机阅读: