Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Excel VBA Interception and Implicit Intersection and VLookUp

  1. #1
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Excel VBA Interception and Implicit Intersection and VLookUp

    What is Implicit Intersection
    As far as I know there is no official documentation on this and blogs on it are sparse.
    The phrase Implicit Intersection is generally used to explain when a formula does what might not initially be expected, ( in most cases this means that it surprisingly “works” in certain cells in an Excel spreadsheet! ) .

    What is Excel VBA Interception
    I made that title up myself.
    It is a theory or idea I have about how Excel works which explains the results in an Excel Spreadsheet which are covered often by the phrase Implicit Interception. It also helps explain and predict some other interesting things in VBA. My suggestion is that what is meant by Implicit Intersection is a by product of Excel VBA Interception. Excel VBA Interception is an integral part of the basic way that Excel works. The by product of Implicit Intersection results from an attempt to “hold” cell values in a way to help speed up calculations in Excel and VBA.
    I tend to view in my theory Excel spreadsheet operation and VBA as the same. That view point is based on the idea of that much of what happens in a spreadsheet is based on pre written VBA type coding.
    _This post offers some theory, or a model, that can explain the Interception Idea.
    _The following post shows how this can explain what is typically referred to as Implicit Intersection, ( I might argue this is a specific Implicit Intersection type)
    _The third and final post attempts to show a working example

    Excel is a cell, mostly. ( What I see and how).
    Of course that is an over simplification, but most often that is what anyone would think about when talking about Excel. Most VBA codes are designed to change things in cells in a spreadsheet. Using them and their contents is fundamental then to Excel and VBA. I am thinking that Excel only has a model of one cell that includes all the stuff of a cell. That information is stored in a massive thing ( http://www.excelforum.com/showthread...11#post4551080 ) called a Range object.
    A spreadsheet is made to look like lots of cells on our screen by the software holding similar information as in the one cell at a specific offsets in memory locations. Those offsets are somehow synchronised to the rate at which the information is put on the screen. So we are fooled into thinking we have a sheet with cells spread out across our screen. This last point is important. In my explanations I will often talk about extra help matrix holding values. In reality they will not actually be that. They will be some mathematical formula that actually optimises how the information is held. The same is done with the formulas used to produce the spreadsheet that we see: They are somehow optimised, for example, such that empty cells do not exist. Otherwise our Excel File would always be enormous even with nothing in it Just as we “visualise” the spreadsheet, I am visualising other matrixes that I discuss. I think my imaginary matrixes would be sort of technically where you effectively get to by the “dependency tree”, which again in the final “thing” is a formula based on certain “offset type coordinates”

    Excel has been written such that when a “change” is made “anywhere” in the spreadsheet, then all cells are “updated”. ( That is one reason you often here that VBA codes that “do things” to a worksheet can be slow as every interaction “slams the brakes on” whilst the whole worksheet is updated. Often in a code you will see Screen Updating turned off until the end of the code when all the updating is then done in one go rather than at every interaction. All the “VBA Array stuff” is about is like, for example, making an internal copy matrix of cell values, doing any processing you want to on them internally, then “pasting them out” to the worksheet in one go. You can do that all in one go stuff efficiently as you just like use the same single offset value for all the values to put them in the place for those things in the referenced Range object ). With this in mind, I expect some attempt at making Excel efficient in doing that may have been made. I think the effect observed and attributed to “Explicit intersection” could be a by product from that.

    Referring to cells and groups of cells areas. Offsets and Resize to “full area”
    Excel’s way of organising and “getting at” the cells and constructing Range objects
    Cells can be organised into groups of cells. These are organised in exactly the same form as a single cell. In other words a Range object can be made up of groups of cells or Range objects. Whether one imagines one “sees” one or more cells is all based on some offset or rather sets of offsets.
    Imagine in the screenshot below that the yellow is the entire spreadsheet. That itself could be a Range object, ( indeed in VBA we have a Property, .Cells , of a worksheet, which when applied to a Worksheet object returns a Range object containing all the cells in a worksheet ).
    “Getting to”, “seeing”, “returning some Property, (value” ).. etc.. is all based on a “offset referencing coordinate system”. This has two basic forms.
    _ a simple ( Up/down , left/right ) to “get to any point going in any direction within the Worksheet”, and
    _ a similar but restricted to from top left, to bottom right to define a full rectangular area
    Using such a system it is possible to “get to” or “construct to see” any combination of cells in a worksheet. So in the screenshot below the Red and Blue areas could be organised into a single Range object. In other words a Range object is organised into groups of contiguous cells. ( These are called Areas ). It can of course be just one Area or even a single cell. ( http://www.excelforum.com/showthread...11#post4551484 http://www.excelforum.com/showthread...154829&page=13 )
    Row\Col
    A
    B
    C
    D
    E
    1
    2
    3
    4
    5
    Based on the last section we have then a system based on going +ve and –ve in two directions to “get there” , then once there we go from a top left to a bottom right to make a “seen rectangular area of contiguous (contiguous means here all there, no spaces ) cells”. ( It comes then maybe as no surprise that in VBA Programming one finds the parallel concepts of a Offset and Resize Property. Furthermore one finds these a very efficient way to do things directly rather than adding an ““in between step” for simplicity of understanding” such as including different variable for different Range objects. )
    See here http://www.excelforum.com/showthread...154829&page=13 for the parallel VBA concepts_..
    _ going +ve and –ve in two directions –--- Range.Item.Property , and
    _ from a top left to a bottom right to make an area ---- Range(“ “) Property.

    Intercepts. ( after Enter in a cell with a formula )
    Everything that we visualise is going to be realised by a Offset to the “one Excel cell”. We see a cell as a result of some synchronisation to a continuum whose “refresh” or “update” is based on going from top left to right, then next row, from left to right, etc. finally ending up at bottom right . It makes sense to use this “positional information” in some efficient way. My idea, which in the reality is realised by a complex mathematical calculation, can be thought of in a similar way to as we imagine a spreadsheet as a matrix. Any single cell reference used in any formula, that is to say appearing in a string after a = , will present a matrix which is “revealed” as the value “seen” by the window which in this case is the single cell.
    The screen shot below is intended to represent 2 different update runs in a worksheet, ( not necessarily the same worksheet ). Imagine that yellows are the cell which we might see in a snapshot in time as a spreadsheet has its displayed values updated. Imagine the Values are in my imaginary help matrix which I am suggesting is created for any single cell reference.
    A1 A1 A1 A2 A2 A2
    A1 A1 A1 A2 A2 A2
    A1 A1 A1 A2 A2 A2
    My idea of how Excel works is to display what is at the intercept of the Yellow and the help matrix. If you type in =A1 or =A2 in any cell it will ”work”. I expect this is very efficient. I think implicit intersection is also a good phrase for what is going on.

    I am thinking that if I have a reference to any 2 dimensional range area, such as A1:B2, the help matrix is created differently ( or in the reality a different mathematical process is going on which can be visualised as such a matrix)
    A1 B1
    A2 B2
    My idea of how Excel works is to display what is at the intercept of the Yellow and the help matrix. I would expect this intercept idea to cause some sort of error in this case as there is nothing to intercept. Indeed if you try anything of the form =A1:B2 in any cell then it will error.

    Controlled Shift before Enter in a cell with a formula
    To still get my basic idea to work you would somehow need to add some additional action to effectively do some effective Shifting of the yellow so that the correct cell in the help matrix is Covered before doing the same intercepting process. This would need to be repeated for as many effective single cell references in the are represented by the 2 dimensional range area referenced. We can now understand possibly why our cells and Range objects are organised such that a Range object construction is similar for single and multi cell areas. The same process used in updating an entire worksheet can be utilised to carry out this multi cell reference evaluation. In doing so it is probably a good guess to say that the returned values will start in origin which in this case is the yellow cell and will extend in to the right and down directions from that origin. This is available to us. As it is somewhat inefficient, ( doing a sort of 2 step Shift along and vertical ), this is not the default that would be implied and applied up until now after the use of Enter. The keys chosen as an alternative are correspondingly Control ( Ctrl ) + Shift + Enter. Such an Entry is known as
    CSE type 1 Entry.
    You will find writing in a references such as = A1:B2 as follows “works” ( does not error) if you do this
    _a)_ Select any cell in the worksheet ( other than in the range ( A1:B2 )
    _b)_Hit F2 or click in the formula bar ( or don’t bother )
    _c)_Type in =A1:B2
    _d)_ Hold down Ctrl + Shift , then hit Enter ( or hit simultaneously Ctrl+Shift+Enter)

    CSE type 2 Entry
    If you try the CSE type 1 Entry with a range of values in cells A1:B2 you will notice that only the value for top left ( A1 ) is displayed. As noted the values are effectively there offset to the right and down. The yellow cell displaced as a snapshot in the Worksheet updating process will correspondingly be at the top left of the range returned by the evaluation. It is a reasonable assumption that the intercept “partner” of the defined cell where the formula is needs to be extended to cover that range. So simply repeat the CSE type 1 entry procedure, but at step _a)_ select any 2x2 range in the worksheet ( other than one containing any cell in A1:B2 ) . This is known as CSE type 2 Entry.
    After carrying this out, if you select any of the cells in which you CS Entered the reference, the following will be shown in the formula bar
    ={A1:B2}
    The { } is just the spreadsheet convention for an Array of values.
    You can repeat the exercises above with ={1, 2; 3, 4} in a cell in place of a reference. The results are similar but will not error without a CS Entry. This is because no Controlled Shift is required to obtain that Array of values. The process here is the multi evaluation which Excel is set to do in general when an Array is in any formula. The CS Entry was previously required to return that Array. The CS Entry is required here only for display of all values.
    As a last note of interest: Excel and VBA basically use the same processes: In VBA there is Function
    Evaluate(strEval)
    This basically allows you to put a string, strEval, such as this form “=A1:B2”, into the argument and basically what happens is that done by for that =A1:A2 in the cell. There is no requirement for any CSE action: The Function returns the Array directly. As it is not an entire spreadsheet updating action, the intercept efficiency thing is not needed
    This basic process can also be used from within the worksheet as follows:
    Type any reference such as =A1:B2 in any cell. Select ( Highlight ) the display it in the formula bar. (The reference can be entered by Enter or by CS Entry ) . Hit F9. This calls into play the basic Excel evaluation procedure and the results are shown. Important: Hit Esc or Ctrl+z to return to formula view.
    Last edited by DocAElstein; 01-05-2018 at 11:32 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  2. #2
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Excel VBA Interception and Implicit Intersection and single Breadth Arrays

    Excel VBA Interception and Implicit Intersection and single Breadth Arrays


    If the last post is understood, then what is often considered as Implicit Intersection can be explained as a by product of what Excel does in terms of my imaginary help matrix, when we consider a similar idea for the case of a reference of a particular form. It is the sort of "middle case":
    To refresh: In the last post we looked at the two "extreme cases" : That of
    _ a multiple 2 dimensional range, like = A1:G5, or
    _ a single cell reference, like =C1.

    We now consider the case of a single "breadth" range. By single "breadth", I am talking about a range like A1:C1 or A1:A2 etc.
    We considered already for the case of a 2 Dimension range that we could visualise a full range help matix
    A1 B1
    A2 B2
    We said that effectively a Shift was needed in "horizontal" and vertical directions before the effective "revealing" of the cell "snap shotted in time" could be done. The imaginary help matrix, I suggested, was empty elsewhere, so nothing was revealed by a simple = in a cell. We were imagining that the = "made visible" the help matrix at the same point as that of the cell when "seen" by a screen update for that cell.

    The idea for the single reference case was that the help matrix had the single value from the referenced cell in all its "cells".
    B2 B2 B2 B2
    B2 B2 B2 B2
    This made the intersect very fast.


    My guess is that the corresponding imaginary help matrix for a single "breadth" range such as =A1:A2 could look like somewhere between the two, like this:
    Help Row\Help Col
    A
    B
    C
    D
    1
    A1 A1 A1 A1
    2
    A2 A2 A2 A2
    3
    ( A1 is the value in cell A1, A2 is the value in cell A2 from the spreadsheet we "see". The above screenshot is my imaginary help matrix. Yellow is the position in the spreadsheet cell for a cell containing the reference, in this case =A1:A2)

    It may be obvious the situation here. The CS Entry will work just as in the case of the 2 dimensional Array case, and Excel will with the returned array, follow its normal multiple evaluation , ( but going just along or just down to select the values and place in a return array for output. ) So we effectively only have to Control a Shift in one direction
    Possibly at this stage, as an aside, stepping back and adding some clarity to what is going on with a simple example is helpful here:
    Any range reference in a cell ( we are just considering one here, =A1:A2 ) , will as a result of a CS Entry have an array returned mirroring in dimensions and size the range referred to, but having the values from that range in it. The complete formula will then be evaluated talking in turn the single values from the corresponding position on any array ( only one this case of the simple formula of a single reference, =A1:A2 ) , and the total results collected in a similar size and dimensioned array.
    For example, consider the array {1, 2 } and { 3, 4 } as those which are returned from two references in a formula in a cell of = B1:C1 * C2:D2
    Excel will return finally { 3, 8 }
    Graphically consider some values in the first 2 rows of a spreadsheet, then if I do a type 2 CSE entry of that formula in, say in the range A3:B3, this is the sort of results I will have in the spreadsheet:
    Values:
    Row\Col
    A
    B
    C
    D
    1
    1
    2
    2
    3
    4
    3
    3
    8
    Formulas:
    Row\Col
    A
    B
    3
    {=B1:C1*C2:D2}
    {=B1:C1*C2:D2}
    Excel can be thought as initially returning the arrays, {1, 2 } from B1:C1 and { 3, 4 } from C2:D2, as instructed by the CS Entry, and then finally it does these evaluations
    ______ 1x3 __ , __ 2x4 ( columns then next row, - in this case just 1 row )

    Coming back to the example in the original screenshot:
    If we do no CS Entry , the default process comes in which effectively imagineatary "reveals" the help matrix.
    It will not give us a value in every cell in the worksheet. But as can be seen in the first screen shot we will get in the yellow cell the value from cell A2. ( remember the formula under consideration in that yellow cell would be =A1:A2 written in the spreadsheet a t the corresponding position in the imaginary spreadsheet on out screen ).
    If the formula were CS Entered, the value would be A1 in that cell, ( or in any cell other than A1 or A2 anywhere in the spreadsheet).
    In a cell in row 1 we would get A1 with or without CSE.
    For any row other than row 1 and row 2 we would get an error without CSE






    Edit later ref: https://support.microsoft.com/en-us/...rs=en-us&ad=us
    Last edited by DocAElstein; 02-02-2021 at 04:40 PM. Reason: https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34?ui=en-us&rs=en
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Example: VLookUp and Intercept and Implicit Intersection for single breadth ranges

    Example: VLookUp and Intercept and Implicit Intersection for single breadth ranges
    .

    I am not too convinced that this is such a good example as I cannot see any advantages. But possibly I am wrong. If it does have, for example, speed advantages over a normal formula then some of my theories are probably out of wach.
    But as this is often given as an example, I will demo it. Any comments or any better examples of using a formula such that it uses Implicit Intersection for single "breadth" ranges would be very welcome.

    I will not go into great detail of the basic VLookUp formula and typical forms in this post. ( I have done that in the next post for revision and reference ) . In this post I will start with three columns holding 3 typical versions of the formula working on the same Look Up values and Look Up Tables, and then add a forth, the often given Implicit Intersection version.

    So in the Window below are the first of a 7 row VLookUp formula in 3 versions in columns B C and D. Column E will then be considered for the Implicit Intersection version.
    Code:
    First row formula
     
    Row\Col
    B
    3
    =VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    Row\Col
    C
    3
    {=VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE)}
    Row\Col
    D
    3
    =VLOOKUP({"Chocolate-europe aroma";"Chocolate-Cookies";"Banana-Chocolate-Split";"Limette-Käsekuchen";"Erdbeere-Quark";"Erdbeere-Mix";"Jamaica Sun";"Waldbeeren"},$A$16:$C$33,3,FALSE)
    Look Up values and returned formula Values seen in cells
    Row\Col
    A
    B
    C
    D
    2
    Given Look Up Value
    Simple Formula. First argument single Look Up value. Draged down
    Column range reference first argument - Type 2 CS Entry
    First argument array. Type 2 CS Entry
    3
    Chocolate-europe aroma
    4
    4
    4
    4
    Chocolate-Cookies
    0
    0
    0
    5
    Banana-Chocolate-Split
    10
    10
    10
    6
    Limette-Käsekuchen
    16
    16
    16
    7
    Erdbeere-Quark
    8
    8
    8
    8
    Erdbeere-Mix
    0
    0
    0
    9
    Jamaica Sun
    6
    6
    6
    10
    Waldbeeren
    0
    0
    0
    Look Up Table
    Row\Col
    A
    B
    C
    14
    LOOKUP Table
    Column 2
    Column 3
    15
    Look Up ValueProduct Name
    16
    Haselnuß-Walnuß-aromatisiert
    17
    Tiramisu
    2
    18
    Chocolate-colonial blend
    19
    Chocolate-europe aroma
    4
    20
    Chocolate-Cookies
    21
    Jamaica Sun
    6
    22
    Himbeere-Joghurt
    23
    Erdbeere-Quark
    8
    24
    Erdbeere-Mix
    25
    Banana-Chocolate-Split
    10
    26
    Waldbeeren
    27
    Kirsche
    12
    28
    Kirsche-grüner Apfel
    29
    Kirsche-Ananas
    14
    30
    Stracciatella
    31
    Limette-Käsekuchen
    16
    32
    grüner Apfel-Quark
    33
    Blutorange-Quark
    _...........................

    The characteristic , or as I would say the By product, of Intercept or Implicit Intersect for a single "breadth" Array that we utilise here is the returned single value in certain cells for a reference such as =A3:A10.
    It might slowly become apparent that I did not randomly pick the rows to be used for the normal formulas. Considering the imaginary help matrix discussed in post 2 for the reference = A3:A10, we will have for that help matrix corresponding to having the formula in any cell ( other than cells A3 through to A10 ) the following:
    Code:
    1
    2
    3
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    Chocolate-europe aroma
    4
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    Chocolate-Cookies
    5
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    Banana-Chocolate-Split
    6
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    Limette-Käsekuchen
    7
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    Erdbeere-Quark
    8
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    Erdbeere-Mix
    9
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    Jamaica Sun
    10
    Waldbeeren
    Waldbeeren
    Waldbeeren
    Waldbeeren
    Waldbeeren
    Waldbeeren
    11
    12
    13
    So, the outcome of this is that a reference, such as that in the first argument of our VLookUp formula, se to =A3:A10 will return ( without CS Entry ) the Look Up value we require. So this formula
    =VLOOKUP(A3:A10,$A$16:$C$33,3,FALSE) or =VLOOKUP($A$3:$A$10,$A$16:$C$33,3,FALSE)
    in rows 3 to 10 in any spare column, such as column E will give the same results as in the other columns.

    The argument for using Implicit Intersection in such a way is that it is very quick.

    But I do not see that it is quicker than the formulas in column B

    May be I will.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    This Post is to support the last Post. It is a review of a simple application of VLookUp.
    The VLookUp is reviewed in
    _1) Simple single value first argument form
    _2) Multiple value first argument form

    The example used is the same as that used in the demo of Intercept and Implicit Intersection for single breadth ranges in VLookUp in the last Post

    _1) Simples use of VLookUp ( non CSE )
    _1a) Single formula, like written in cell B3 this: = VLOOKUP(A3,$A$16:$C$33,3,FALSE)
    Consider the final Screenshot below where the first 10 rows are to be used for our VLookUp formulas, and the range A16:C33 is the Look up table used in the VLookUp formula. ( It might help if you copy that to a spare worksheet when following the explanations. Take care to copy to the correct range area. Alternatively the worksheets "XLORX" and "Intercepts" in the uploaded file have a mixture of the various formulas and tables )

    Just to refresh what is going on: VLookUp is:
    https://support.office.com/en-us/art...8-93a18ad188a1
    Simplified VLOOKUP function says,
    _ =VLOOKUP( argument1 , argument2 , argument3 , argumant4 )
    _ =VLOOKUP( argument1 ,
    argument2 ,
    argument3 ,
    argumant4 )
    _ = VLOOKUP(A3 , $A$16:$C$33 , 3 , FALSE)
    _ = VLOOKUP(A3 ,
    $A$16:$C$33,
    3,
    FALSE)
    _ =VLOOKUP([size=1]Value you want to look up,
    range where you want to lookup the value,
    the column number in the range containing the return value,
    Exact Match or Approximate Match
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  5. #5
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Spreadsheet Functions to return multiple values when they usually return just one and VLookUp

    Extending the background work on VLookUp
    I am looking generally at getting multiple values out of a Worksheet formula, in particular for formulas that might not generally be used for or thought of as doing that. I am putting some emphasis finally on the case of VLookUp as it is a bit quirky and does not quite follow the trend of what usually happens.

    In many cases Functions, especially worksheet Functions, which usually take one or more of their arguments as single values will accept in place of those arguments multiple values as range address reference or an Array of values, see here for example:
    http://www.excelforum.com/showthread...95#post4571172

    Often this will result in multiple answers, in place of the single answer for the case of the normal single value arguments case.
    The simplest form and analysis and explanation of this is as follows:
    In most things that Excel does relating to a spreadsheet, or at the occurrence of most events , a process of analysis sets off as a result of which the entire worksheet is updated. It sets off a progression along some effective 2 dimensional grid system , starting at top left, then along the columns, then down a row and repeats again from the left, eventually arriving at bottom right.
    Synchronisation of this to offset memory locations results in what we "see" on our computer screen. http://www.excelfox.com/forum/showth...on-and-VLookUp
    A by Product of this is that when a usually single value argument is given multiple values the evaluation process is effectively perverted such that the evaluation is done at a "time" or point of the progression offset by an amount proportional to the offset in the range or the Array of the member values http://www.eileenslounge.com/viewtopic.php?f=4&t=22534 The end result of that is that Excel has the multiple answers "available" somewhere/ somehow in a matrix or array of dimensions mirroring the maximum dimensions of any Arrays or ranges used in the place of usually single value arguments.

    ==In a Spreadsheet:==
    The process to allow this to happen and "see" the "available" values in a spreadsheet seems fairly well understood involving a mechanism for
    _ somehow doing effectively a Controlled Shift before Enter adjustment adjusting ( CSE Type 1 ) .
    _ In addition, one can effectively place the formula in a range of cells which if chosen appropriately will reveal all the "available" values ( CSE type 2 ). http://www.excelfox.com/forum/showth...alue#post10038 http://www.excelfox.com/forum/showth...0061#post10061

    Without this process it is not always straight forward to get the multi value output results:

    In a famous blog here: https://excelxor.com/2014/09/05/inde...ray-of-values/ a way is shown to get multiple values from a VLookpUp Function without CS Entry

    I am having a look at that a bit here:
    Consider a simple VLookUp Function example use of:
    This could be the "Look Up Table"
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    VLookUp Function is, in the usual single value argument option, then used to return the number 1, 2, or 3, depending on whether you give the function A, B, or C.
    The first single argument then would be, for example A. This is the "Look Up Value"
    The second argument is that Look Up Table ( which is a fixed array always, and not part of our "multi value ( array ) in place of usual single value discussions )
    The third argument simply defines the column number used in the Look Up Table for the output, ( in this case 3 )
    The last argument takes a 0 or False as meaning only accept an exact match to the Look Up value. ( 1 or True allows the nearest match to be accepted )
    ____ = VLOOKUP(LookUpValue,LookUpTable,ColumnNumber,0)
    So this pseudo like thingy would be our example
    ____ 1 = VLookUp("A", I22:K24, 3, 0)
    or
    ____ 1 = VLookUp(I22, I22:K24, 3, 0)

    In most cases, as is here, CS Entry of the formula in such a form _..
    VLookUp({"A"; "B"; "C"}, I22:K24, 3, 0) or VLookUp(I22:I24, I22:K24, 3, 0)
    _..will give us an Array of values out in the orientation of the first argument,
    {1; 2; 3}.
    By the way, this, ; , is the Excel convention for "vertical" or "column" orientation so we effectively have this:
    1
    2
    3
    ( Remember I22:K24 is always our fixed Look Up table. This could be replaced with
    { " "A","-","1";"B","-","2";"C","-","3"} , which is effectively "looking" like:
    A - 1
    B - 2
    C - 3 )

    Things are not always so straight forward as they usually are in using CS Entry, and even the best people are not quite sure why.....

    =="Evaluate" outside the spreadsheet==
    I would argue the distinction between in a spreadsheet and similar things "outside" like in a VBA code is fine as the basic processes are probably the same... But anyway:
    We have 2 other ways of "evaluating" such a formula. They are both, I think, similar in how they work. They both use the same basic processes as in a spreadsheet after hitting Enter, or, I expect, they actually go straight into doing the CS Enter. ( In the spreadsheet you may do the CS Entry in any situation where you would do just Enter. It does no harm. It may involve doing unnecessary extra work. I don't know )
    _(i) One way - F9:
    If you select a part of a formula or function in the formula bar and hit F9, you will get an "instant" evaluation.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Multi value return for simple Formula. Case Typical is Rept(str, how_many_times)

    Spreadsheet Functions to return multiple values when they usually return just one and here is with
    Rept(str, how_many_times)
    ( In the over next post is an accompanying code. Any green numbers given in the text are approximate line numbers of accompanying parts

    _.. This is just to review a fairly usual case, before looking at the VLoookUp case, which we will see is a bit unusual.
    _.. So here a review of a more straight forward example, the Rept(str, how_many_times) Function:
    ( _.. typical normal use to get “AA” __ = Rept(“A”,2) )

    ==In Spreadsheet==
    _ Put this =REPT({"A";"B"},2) or this =REPT(I22:I23,2) in any cell. ( The second will, unless in a cell allowing Implicit Intersection, error. http://www.excelfox.com/forum/showth...on-and-VLookUp http://www.excelforum.com/showthread...95#post4571173 Hold my implicit offset intersexual intercept excepting intersect automagically wonks. – Don’t worry about that for now )

    ==“Evaluate” outside the spreadsheet==
    _(i) F9
    Select either of those formulas above in the formula bar and hit F9, ( You can even use the one which errors).
    You should then see this_.....
    {"AA";"BB"}
    _........instead of the formula , which is like “seeing” this
    AA
    BB
    ( Remember after to hit Esc or Ctrl+z to get back to formula view )
    It appears that F9 does something similar to CS Entry type 1 or 2. ( Doing CS E type 2 is required in the spreadsheet for the second formula =REPT(I22:I23,2) to do the Controlled Shift and get all values seen for the first formula. CS type 2 Entry is also required in the spreadsheet for the first formula , but only to get all the values seen )

    _(ii) Looking again in VBA Evaluate(“ “)
    ( Full codes here: http://www.excelforum.com/developmen...ml#post4596154 ( Post #200 ) )
    It would appear that Evaluate(“ “) will do a “single cell evaluation” usually. Somehow that is basically what it does. However, it would appear that the basic processes are likely carried out somehow such that the Arrays {"A";"B"} and I22:I23 are available, possibly. This is unclear.

    We do not have the need, or so it appears, for Controlling a Shift before an Enter. This is reasonable as we are not “in a cell”, so do not have the Intersection “revealing” going on. So we see that both Evaluate("=REPT({""A"";""B""},2)") and Evaluate("=REPT(I22:I23,2)") do not error.
    However we only get the first value. ( up to code line 100 ). So it is not clear if the multi values are there or not. We do not have an available Controlled Shift process as it is not necessarily needed in the first instance.
    But the basic process is likely available somehow.
    That is to say, within any process be it in VBA or Excel the “along the columns then down to next row “ is available. If such a process were “running”, then returned output multiple values appear in an Array to match the orientations and largest dimensions of Arrays within the formula or function being “run”. In this case a 2 “row” x 1”column” Array would be returned. During the “running”, at each position for the output array being considered, any available position in any other arrays will be used. Therefore in order to get out Function to return that Array, we just need to add something to the complete equation that does not affect the answer but causes that process to take place
    This is all very important stuff, that no one appears to understand fully, or is sure about.
    So we will say the same thing again, just slightly differently:
    Although not a complete 1 to 1 translation, the Evaluate(“ “) is generally intended to do the processes in Excel that would take place in a Spreadsheet. Because we are not physically in a spreadsheet we do not require the “extraction” type process of Controlled Shifting before individual value like Entering. This explains why we have no error as in a non implicit Intersection spreadsheet entry.
    The determination generally of whether multiple values are obtained in Evaluate(“ “) is generally thought to be based on if the formula itself usually returns multiple values. Our Rept( , ) Function isn’t. So it doesn’t.

    So (i) F9 and (ii) Evaluate(“ “) are not quite doing the same thing

    Unfortunately before going further a transgression is necessary so as not to confused in taking the “(i) F9 and (ii) Evaluate(“ “)” story further.
    ' '_- Asside:
    Excel and Range referencing.
    A long standing curiosity in Excel VBA is, ( was ), Why VBA Evaluate does not work on a closed Workbook reference. http://www.eileenslounge.com/viewtop...=25213#p202227
    This can be explained.( I did have I done )
    We need to consider an understand Excel VBA Interception and Implicit Intersection
    http://www.excelfox.com/forum/showth...0061#post10061
    http://www.excelforum.com/tips-and-t...ml#post4575459

    I only introduced the concept recently so it is no surprise the original question of “Why VBA Evaluate does not work on a closed Workbook reference”, was difficult to answer.
    When placed in a spreadsheet processes are under way which speed up the updating of values in the spreadsheet. The references above explain this in some detail to an extent not discussed previously. To function, this process requires evaluation of the cell value. Excel is written to do that when after seeing a = in a cell.
    “Behind” every cell is a Range object from which this information is retrieved.
    http://www.excelforum.com/the-water-...ml#post4586265
    http://www.excelforum.com/developmen...ml#post4551080
    http://www.excelforum.com/developmen...ml#post4563838
    https://powerspreadsheets.com/excel-vba-range-object/

    In the cell this value ( specifically the .Value Property ) is returned, as indeed is returned as the default Property for many uses of the Range object. This last point has possibly some more relevance to Excel Generally.

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel

    I believe that in a cell a string range reference , is recognised as such. I believe when a string address such as M40:M41 is written in a cell in a worksheet “MySheet” in a File, “MyWorkbook.xlsx”, then this defaults to a string reference of such a form
    strRef ='[MyWorkbook.xlsx]MySheet'!A1
    Or for a Worksheet named "XLORX", such a form is “seen” by Excel
    270 strRef ='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41
    There are many ways to demonstrate this.
    '_- Fundamental looking at Range object properties and referring to ranges and spreadsheet cells is one way:
    http://www.excelfox.com/forum/showth...eadsheet-cells
    http://www.excelforum.com/tips-and-t...eet-cells.html

    '_- Index Example way
    ' '_- Index way of looking at it: Code lines 400 - 560
    Another way , I would suggest, is to look more closely at how a cell, cel As Range object, is handled by Externally showing it, for example on a spreadsheet, “in Ex cel” or “Excel” as it were.
    An Excel spreadsheet is arranged by default to show the value, ( .Value ) as indeed is required for Intersexual Interception Alan Theory. It probably helps in the coding to have this as the objects default given property. It appears to be that given in most cases when the range object is “presented” where one might consider a type mismatch error should occur, ( but by virtue of this default does not produce an error ) .
    I would suggest that in fact fundamentally, it is a range Object which Excel is “holding” when such a reference is given ( full or in shortened address may be given, but Excel “sees” the full, guessing the default extra string section ).
    I would suggest that therefore Evaluate(strRef) is fundamentally returning a Range object, despite that we see a value ( .Value ) in the spreadsheet. In the demo code we can see how The Evaluate(“ “) of a spreadsheet function, Index, which clearly in all spreadsheet use, does in fact give us a range Object in Evaluate.
    ( I would finally suggest that in fact Range(“ “) in VBA is using the same or very similar process to Evaluate(“ “) but restricted to, or optimised to just working on the strRef as the Evaluate String, strEval. That is to say it will only accept a string reference as argument. This is in agreement with the general documentation.
    http://www.excelforum.com/excel-prog...ket-for-2.html


    _........................
    That last was a bit of an aside, necessary not to get confused, with some aspects discussed now as we go further with the “(i) F9 and (ii) Evaluate(“ “)” story


    Recap: _(i) F9
    Select either of those formulas above in the formula bar and hit F9, ( Even the one which errors).
    You should then see this_.....
    {"AA";"BB"}
    _........instead of the formula , which is like “seeing” this
    AA
    BB
    ( Remember after to hit Esc or Ctrl+z to get back to formula view )
    It appears that F9 does something similar to CS Entry type 1 or 2. ( Doing CS E type 2 is required for the second formula to do the Controlled Shift and get all values seen for the first formula. CS Entry is also required for the first formula , but only to get all the values seen, for the first value )

    We had no problem to get our multiple output.. but

    ' '_- Evaluate multivalues 1 ( Back To is now )
    _(ii) Looking again in VBA Evaluate(“ “)
    ( Full codes lines from 570 : http://www.excelforum.com/developmen...ml#post4596154 ( Post #200 ) )

    It would appear that Evaluate(“ “) will do a “single cell evaluation” usually. Somehow that is basically what it does. However, it would appear that the basic processes are likely carried out somehow such that the Arrays {"A";"B"} and I22:I23 are available, possibly. This is unclear. _...............
    _.................
    _.......... The determination generally of whether multiple values are obtained in Evaluate(“ “) is generally thought to be based on if the formula itself usually returns multiple values. Our Rept( , ) Function isn’t. So it doesn’t.
    So (i) F9 and (ii) Evaluate(“ “) are not quite doing the same thing


    In general there is no problem with something of this form
    580 strEval = ={1;2}+{3;4;5} '_- Spreadsheet
    Evaluate(={1;2}+{3;4;5}) '_- VBA
    ( The only thing to note for the Spreadsheet case, is that, (whilst no Intersection is done leading to no error), we would still require Controlled Shifting before Entering over a 1 “wide” by 3 “deep” spreadsheet area to get returned
    4
    6
    error
    ( or {4;6;error} )
    In VBA, we get directly {4;6;error} returned in a variant type array of dimensions (1 to 3, 1 To 1)
    We also understand from the last aside that this_..
    Evaluate("=M40:M42")
    _.. is a special case, and that we will get a Range object or an array depending in how we declare the receiving variable.

    Some other following examples are not immediately obvious:
    This _..
    = Evaluate("={1;2}+M40:M42")
    _.. returns {4;6;error}
    Or
    4
    6
    error
    Whereas this:
    = Evaluate("={1}+M40:M42")
    _.. returns {1;1;1}
    Or
    1
    1
    1
    and _..
    = Evaluate("=1+M40:M42")
    _.. returns {1;1;1}
    Or
    1
    1
    1
    and _..

    Evaluate("={1,2}+M40:M42")
    _.. returns {1,2;1,2;1,2}
    Or
    1 _ 2
    1 _ 2
    1 _ 2

    Oh, God, another aside: We are demonstrating here again Interception Theory, which describes that Excel and VBA has a values help matrix for value ( or value from reference in a spreadsheet cell ) which for the case of
    single value http://www.excelfox.com/forum/showth...on-and-VLookUp http://www.excelforum.com/tips-and-t...d-vlookup.html
    and
    single “breadth” multi values http://www.excelfox.com/forum/showth...okUp#post10062 http://www.excelforum.com/tips-and-t...ml#post4575460

    ( Note in the last examples, Excel does not see a cell reference for those M40:M42 occurrences. They appear in a formula. The values are therefore taken. We are not dealing with the special case here

    The above can be summarised into a general Evaluate(“ “) process, which is slightly different to the F9 case which appeared to do some pseudo CSE type 1 or 2 process. We are saying different here, as right at the outset the problem appeared to be that only the first value was obtained.
    Our last experiments are suggesting the following summary can be given:

    A process appears to be going on in Evaluate(“ “), or rather possibly some general calculation in VBA, whereby a given formula will be repeated in a “all columns, then down to next row” sequence for as many times as there are maximum dimensions of “seen” arrays in a calculation protocol ( formula ). Interception Theory is integral to this working, allowing a single value to be taken as many times as there are final dimensions in the maximum dimensions. ( The output is given in an Array equal to those maximum dimensions ).

    We follow in the over over next section with getting our Rept(str, how_many_times) with multiple values to work. I forgot why.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  7. #7
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it a

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such


    ( For here : http://www.eileenslounge.com/viewtop...202227#p202227 )


    ' '_- Asside:
    Excel and Range referencing.
    A long standing curiosity in Excel VBA is, ( was ), Why VBA Evaluate does not work on a closed Workbook reference. http://www.eileenslounge.com/viewtop...=25213#p202227
    This can be explained.( I did have I done )
    We need to consider an understand Excel VBA Interception and Implicit Intersection
    http://www.excelfox.com/forum/showth...0061#post10061
    http://www.excelforum.com/tips-and-t...ml#post4575459

    I only introduced the concept recently so it is no surprise the original question of "Why VBA Evaluate does not work on a closed Workbook reference", was difficult to answer.
    When placed in a spreadsheet processes are under way which speed up the updating of values in the spreadsheet. The references above explain this in some detail to an extent not discussed previously. To function, this process requires evaluation of the cell value. Excel is written to do that when after seeing a = in a cell.
    "Behind" every cell is a Range object from which this information is retrieved.
    http://www.excelforum.com/the-water-...ml#post4586265
    http://www.excelforum.com/developmen...ml#post4551080
    http://www.excelforum.com/developmen...ml#post4563838
    https://powerspreadsheets.com/excel-vba-range-object/

    In the cell this value ( specifically the .Value Property ) is returned, as indeed is returned as the default Property for many uses of the Range object. This last point has possibly some more relevance to Excel Generally.

    Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel

    I believe that in a cell a string range reference , is recognised as such. I believe when a string address such as M40:M41 is written in a cell in a worksheet "MySheet" in a File, "MyWorkbook.xlsx", then this defaults to a string reference of such a form
    strRef ='[MyWorkbook.xlsx]MySheet'!A1
    Or for a Worksheet named "XLORX", such a form is "seen" by Excel
    270 strRef ='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41
    There are many ways to demonstrate this.
    '_- Fundamental looking at Range object properties and referring to ranges and spreadsheet cells is one way:
    http://www.excelfox.com/forum/showth...eadsheet-cells
    http://www.excelforum.com/tips-and-t...eet-cells.html

    '_- Index Example way
    ' '_- Index way of looking at it: Code lines 400 - 560
    Another way , I would suggest, is to look more closely at how a cell, cel As Range object, is handled by Externally showing it, for example on a spreadsheet, "in Ex cel" or "Excel" as it were.
    An Excel spreadsheet is arranged by default to show the value, ( .Value ) as indeed is required for Intersexual Interception Alan Theory. It probably helps in the coding to have this as the objects default given property. It appears to be that given in most cases when the range object is "presented" where one might consider a type mismatch error should occur, ( but by virtue of this default does not produce an error ) .
    I would suggest that in fact fundamentally, it is a Range object which Excel is "holding" when such a reference is given ( full or in shortened address may be given, but Excel "sees" the full, guessing the default extra string section ).
    I would suggest that therefore Evaluate(strRef) is fundamentally returning a Range object, despite that we see a value ( .Value ) in the spreadsheet. In the demo code we can see how The Evaluate(" ") of a spreadsheet function, Index, which clearly in all spreadsheet use, does in fact give us a range Object in Evaluate.
    ( I would finally suggest that in fact Range(" ") in VBA is using the same or very similar process to Evaluate(" ") but restricted to, or optimised to just working on the strRef as the Evaluate String, strEval. That is to say it will only accept a string reference as argument. This is in agreement with the general documentation.
    http://www.excelforum.com/excel-prog...ket-for-2.html
    )

    Conclusion
    The end result as seen in the code lines in the next Post is that for a single Range object reference , ( noting this can be multi spreadsheet Areas of contiguous cells ) , we are able to get a Range object returned if the receiving variable is declared ( Dimed ) appropriately. This lead to my argument that Excel "holds" fundamentally from a string ( as simple address given!! ) reference in a cell a Range object. ( !!The string reference is increased to include the default Worksheet and Workbook if not given )
    Should the variable be otherwise declared, the .Value default appears to apply. As such the .Value Property applied to the Range object will return for a single cell Range object an appropriately dimensioned variable, or for the case of a Range object of more than one constituent cell, a Field ( Array ) of variant types, ( defaulting to the first Area of the Range object if no Areas item number is given . Once concatenated with anything else, Excel takes the value for the reference.
    We look at all this now in more detail in the over next post.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Codes for discussions of last few posts: Rept(str, how_many_times) multivalue return want Wonk I do have

    Codes for discussions of last few posts: Rept(str, how_many_times) multivalue return want Wonk I do have


    Code:
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    Row\Col
    M
    40
    41
    Sub EvalRep1() ' 10 Dim strEval As String 20 Let strEval = "=REPT({""A"";""B""},2)": Debug.Print strEval ' Ctrl + g gives: =REPT({"A";"B"},2) which is what we would write in a cell 30 Dim vTemp As Variant 'Choose Variant as we may get a single value or Array from Evaluate 40 Let vTemp = Evaluate(strEval) 'Returns "AA" 50 Let vTemp = Evaluate("=REPT({""A"";""B""},2)") 'Returns "AA" 60 Let strEval = "=REPT(I22:I23,2)" 'Returns "AA" 70 Let vTemp = Evaluate(strEval) 'Returns "AA" 80 Let vTemp = Evaluate("=REPT(I22:I23,2)") 'Returns "AA" 90 Let vTemp = Evaluate(strEval) 'Returns "AA" 100 ' A few strings for evaluate Preliminary: repeted in next code '_- results given by Evaluate(strEval) 110 Let strEval = "={" & """""" & ";" & """""" & "}" & "&" & "REPT(I22:I23,2)": Debug.Print strEval ' ={"";""}&REPT(I22:I23,2) 120 Let vTemp = Evaluate(strEval) '_- Just first value, "AA" returned ' A null string is being concatenated. 130 Let strEval = "=M40:M41" & "&" & "REPT(I22:I23,2)": Debug.Print strEval ' =M40:M41&REPT(I22:I23,2) 140 Let vTemp = Evaluate(strEval) '_- Just first value, AA returned ' A null string is being concatenated. 150 Let strEval = "=If(row(),M40:M41)" & "&" & "REPT(I22:I23,2)": Debug.Print strEval '=If(row(),M40:M41)&REPT(I22:I23,2) 160 Let vTemp = Evaluate(strEval) '_- returns "0AA" = {0;0} & {AA;BB} but only rule of a single value ?? 170 Let strEval = "=If(row(),M40:M41)" & "&" & "If(row(),REPT(I22:I23,2))": Debug.Print strEval '=If(row(),M40:M41)&If(row(),REPT(I22:I23,2)) 180 Let vTemp = Evaluate(strEval) '?? Ahh '_- returns 1 "column" 2 "row" array ' {0AA;0BB} It is doing the evaluation of range Object and getting values from the Function 190 Let strEval = "=If(row(),M40:M41)": Debug.Print strEval ' =If(row(),M40:M41) 200 Let vTemp = Evaluate(strEval) '_- returns 1 "column" 2 "row" array ' {0;0} It is doing the evaluation 210 Let strEval = "=M40:M41": Debug.Print strEval ' =M40:M41 220 Let vTemp = Evaluate(strEval) '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from Range object 230 Let strEval = "M40:M41": Debug.Print strEval ' M40:M41 240 Let vTemp = Evaluate(strEval) '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from Range object 250 260 ' '_- Asside: Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel ' the fundamental idea of a Excel "holding" a cell, through a string reference, as a Range object http://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-14.html#post4595462 http://www.eileenslounge.com/viewtopic.php?f=30&t=25213&p=202227#p202227 270 Let strEval = "='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41" 280 Let vTemp = Evaluate(strEval) '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from Range object 290 Let vTemp = Evaluate(strEval).Value '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from Range object 300 Let vTemp = Range("M40:M41").Value '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from Range object 310 Let vTemp = Range(strEval).Value '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from Range object 320 Dim RngTemp As Range 330 Set RngTemp = Evaluate(strEval) '1 Areas Rng '_- Evaluate works slightly differently for a range referrence, and will return a Range Object if the recieving variable is declared ( Dim'ed ) appropriately 340 Let strEval = "='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41,N42" 350 Let vTemp = Evaluate(strEval) '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from first Area of Range object 360 Let vTemp = Evaluate(strEval).Value '_- returns 1 "column" 2 "row" array ' {Empty;Empty} value from first Area of Range object 370 Set RngTemp = Evaluate(strEval) '2 Areas Rng '_- Evaluate works slightly differently for a range referrence, and will return a Range Object if the recieving variable is declared ( Dim'ed ) appropriately. Here it is a 2 Areas range object 380 390 ' '_- Index way of looking at it: 400 Let strEval = "=Index(I22:K24,0,0)" 410 Let vTemp = Evaluate(strEval) '_- Without specific type, this defaults to Evaluate(strEval).Value --- Variant member type array 420 Set RngTemp = Evaluate(strEval) '_- Range object returned 430 Let strEval = "=Index(I22:K24,1,0)" 440 Let vTemp = Evaluate(strEval) 450 Set RngTemp = Evaluate(strEval) '_- Range object returned 460 Let strEval = "=Index(I22:K24,1,1)" 470 Let vTemp = Evaluate(strEval) 480 Set RngTemp = Evaluate(strEval) '_- Range object returned 490 Let vTemp = Application.Index(Range("I22:K24"), 0, 0) '_- 3 x 3 Array returned 500 Set RngTemp = Application.Index(Range("I22:K24"), 0, 0) '_- 3 x 3 Range object returned 510 Let vTemp = Application.Index(Range("I22:K24"), 1, 0) '_- 1 Dimension 3 Member element Array returned Array returned 520 Set RngTemp = Application.Index(Range("I22:K24"), 1, 0) '_- 1 x 3 Range object returned 530 Let vTemp = Application.Index(Range("I22:K24"), 1, 1) '_- return value A in String type variable 540 Let vTemp = Application.Index(Range("I22:K24"), 1, 3) '_- return value 1 in Double type variabel 545 Let vTemp = Application.Index(Range("I22:K24"), 1, 3).Value '_- return value 1 in Double type variabel 550 Set RngTemp = Application.Index(Range("I22:K24"), 1, 1) '_- 1 cell Range object returned, or deafault to "A" 555 Set RngTemp = Application.Index(Range("A1:B2,I22:K24"), 1, 1, 2) '_- 1 cell Range object returned( same cell as last line ) 560 Let vTemp = Application.Index(Range("A1:B2,I22:K24"), 1, 3, 2).Value '_- return value 1 in Double type variabel pgc01 https://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375560 570 ' '_- Evaluate multivalues 1 ( Back To is now ;) ) 580 Let strEval = "={1;2}+{3;4;5}" 590 Let vTemp = Evaluate(strEval) '_- returns {4;6;error} 600 Let strEval = "=M40:M42" 610 Let vTemp = Evaluate(strEval) '_- returns {Empty;Empty;Empty} 620 Set RngTemp = Evaluate(strEval) '_- Range object returned 630 Let strEval = "={1;2}+M40:M42" 640 Let vTemp = Evaluate(strEval) '_- returns {4;6;error} 650 Let strEval = "={1}+M40:M42" 660 Let vTemp = Evaluate(strEval) '_- returns {1;1;1} 670 Let strEval = "={1,2}+M40:M42" 680 Let vTemp = Evaluate(strEval) '_- returns {1,2;1,2;1,2} 690 Let strEval = "=1+M40:M42" 700 Let vTemp = Evaluate(strEval) '_- returns {1;1;1} 710 End Sub
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  9. #9
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Getting our Rept(str, how_many_times) with multiple values to work. I forgot why.

    Getting our Rept(str, how_many_times) with multiple values to work. I forgot why.

    Killings Interception: Suspending, ( or stopping at the first of ), Multi value return analysis.

    Remove the Excuse for an Abortion

    Using the same screen shots data as the last posts and running through some more_.....
    Row\Col
    I
    J
    K
    22
    A
    -
    1
    23
    B
    -
    2
    24
    C
    -
    3
    Row\Col
    M
    40
    41
    _..... code lines to demo... ( Code in next post, Matey boy ( or Girly )

    Rem 1 Going again to find that we only get one value from Evaluate("=REPT({"A";"B"},2)"), which is "AA"

    Rem 2 Trying to get both possible values out.
    We have already established , via CSE or F9 in formula bar, that we have two values available. But how do we get them?

    '2a) review Excel VBA multi value analysis
    We discussed in detail in the aside, ( ' '_- Asside: Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel ' the fundamental idea of a Excel "holding" a cell, through a string reference, as a Range object http://www.excelforum.com/developmen...ml#post4595462 http://www.eileenslounge.com/viewtop...202227#p202227 ) , that we can choose to get the full
    210 Range object
    or
    220 its values held in ( for more than one cell ) a Field ( Array ) of Variant types.

    230-270 Once Excel does not recognise this as a reference a Formula is recognised and simple range references are taken as appropriate dimensioned Arrays holding their cell values
    The final output will be set to a dimension of pseudo "Open Window" to encompass the largest of any of the Arrays.

    '2a(ii) When there is a mismatch in Array sizes,
    _(i) 280 for example when a 3 x 1 ( 1,1 2,1 3,1 ) and a 2 x 1 ( 1,1 2,1 ) are present, then a fairly predictable result is obtained of an error in the 3, 1 output Array as Excel is trying to concatenate to nothing.

    'Interception
    _(ii) 300 ' Single value .
    Results are less obvious for the case of one Array being a single element, or even if that is replaced by
    320 A single value. In these cases we see a duplicated taken copy of that single value to concatenate rather than as we might of expected an error due to nothing there
    _(iii) 340 ' Single breadth .
    Similarly extending the pseudo "Open Window" such that a single breadth Array has a missing counterpart in the extended breadth will duplicate rather than erroring, ( but only up to the depth ) of the single breadth Array.

    The above results, in particular the last two require the understanding of Intercept Theory which I wrote: 'Interception Theory:
    http://www.excelfox.com/forum/showth...on-and-VLookUp
    https://www.excelforum.com/tips-and-...d-vlookup.html

    In brief , 'Interception Theory: Excel appears_..
    _.. (ii) for the case of a Single value, to "hold" a help matrix of size of all excel spreadsheet cells to speed up interception of that with the effective "open window for intercept"
    and
    _..(iii) for the case of a Single "breadth" Array , to hold duplicated values extending outside the single breadth, "across" as it were all the spreadsheet cells , restricted to the "length" or "depth" of the Array. ( The Latter, (iii) , is responsible for Implicit Intersection ).

    '2b) Attempting concatenations of 2 x 1 Arrays with Rept(__;__ , how_many_times) .
    ('2b(ii) Killing Interceptions )
    The last code section demonstrated that Excel is "opening up a window" or "Array space" to encompass the largest dimensions of all seen Arrays. The point of this section is to try to replace one of the two references in the last code lines with the REPT(I22:I23,2) or REPT({"A";"B"},2), with the hope that somehow Excel would somehow find the I22:I23 or {"A";"B"}. Should this work, then we could concatenate to an empty Range or to {"";""} and so have a successful "coercion"
    It didn't work
    I had a feeling it would not
    Never mind.
    I expect this is telling me that each section in a & is being evaluated separately.. ( and I am somehow Killing Interception **)
    But, and this is quite interesting, we are getting from REPT({"A";"B"},2) just the first value AA, but if I replace REPT({"A";"B"},2) with AA,
    530, Then I get again my result as explained by Interception Theory, {"AAA";"BAA"}
    550 Going back and using REPT("A",2) in place of REPT({"A";"B"},2) and once again I do get my result as explained by Interception Theory, {"AAA";"BAA"}. Very strange ** Somehow REPT({"A";"B"},2) KILLED interception.
    I do not really know what is going on here , with killing interception. Possibly Excel is starting the process to which Controlled Shifting before Entering . This preliminary step is possibly suspending, ( or stopping at the first of ), Multi value return analysis.
    So maybe we have a new idea : "Killings Interception: Suspending, ( or stopping at the first of ), Multi value return analysis. " or "An excuse for an abortion"

    _._____________


    '2c) Multivalve from REPT({"A";"B"},2)
    One thing is clear:.. It is not at all clear the exact processes that are going on.

    I suspect up until now, a lot of published work, workarounds, are based on empirical measurements. As a general rule, it would appear that including the "Excuse for an Abortion Function" in a complete Formula in which has valid returning multi value properties will somehow retrieve those values. But why it should retrieve the appropriate individual values and not then do a Interception evaluation with the first value ( multiple thereof ) , is not clear..

    Standard Wonks
    In any case we have the ( probably empirically derived ) standard wonks that work in most cases.
    '2c)(i), '2c)(ii), '2c)(iii)
    These are just a few possible ways to incorporate the Excuse for an Abortion Function , REPT({"A";"B"},2) into a total formula that will not change the final result, but includes another function which does in general return multiple values.
    '2c)(i)
    We discussed this here: https://www.excelforum.com/developme...ml#post4595462 . We showed that Index can be used to return multi cell ranges or array of values of contents thereof. In an extreme case we simply use it to return the entire first argument grid. When, as in this case , that forst argument is our Excuse for an Abortion Function, then somehow an extra pre evaluation appears to be going on of the F9 type to bring out the multiple values. The trick is that we have embedded it inside another function... ( So why is that? https://www.excelforum.com/excel-for...ml#post4601913

    '2c)(ii) Transpose
    Once again the expected first argument is expected to be, and wired to look for, and possibly evaluate to, multivalves.

    '2c(iii) If( condition, do it then if condition is True )
    This generally is a two argument If statement resulting in the second argument being done if the first argument condition is met. Once again no one really understands this fully.
    A typical fist argument is Row(). A likely explanation:
    _ In a spreadsheet , I believe this does an ( Inverse ) Intercept case 1 single value type process. In a spreadsheet it reveals the row number in which the formula =Row() is written. In evaluate it has lost some connection in the spreadsheet. In any case, we are just using it to get a True. As such I believe it sort of floats or transgresses all , waiting to be tied down. The use of it in If(__ will always cause the second argument expression to be done. The dimension of the second argument will define the final Output window dimension. Possibly indirectly this returns for Row() an Array of Long numbers greater than 1, which as can be seen in '2c(v), will be taken as 1 or True
    _
    '2c(iv)
    A version of the previous If( condition, do it then if condition is True ), with specific row or column in the argument.
    Here, an Array size is determined by the first argument. This will therefore contribute to the final Open window to encompassing maximum dimensions of all contributing Arrays
    The values in the Array are not important, as long as they are greater than 0 then they are taken as 1 or True ( 930, 935, 970 )
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Code for last Post

    Code for last Post

    Code:
    '10   '         I   J   K
    '20   '
    '30   '  22     A   -   1
    '40   '  23     B   -   2
    '50   '  24     C   -   3
    '60   '
    Sub EvalRep2()      '                                https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-14.html#post4602295
    70   Rem 1) Here I go again. Just getting First value.    http://listenonrepeat.com/watch/?v=WyF8RHM1OCg#Whitesnake_-_Here_I_Go_Again__87
    80   Dim strEval As String
    90    Let strEval = "=REPT({""A"";""B""},2)": Debug.Print strEval           '  Ctrl+g reveals   =REPT({"A";"B"},2)    in the Immediate Window" , which is as "seen" by VBA, which is how we would write it in a cell  -  Quotes:  http://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-11.html#post4555023
    100  Dim vTemp As Variant 'Choose Variant as we may get a single value or Array from Evaluate
    110   Let vTemp = Evaluate(strEval)                                         ' Returns "AA"
    120   Let vTemp = Evaluate("=REPT({""A"";""B""},2)")                        ' Returns "AA"
    130   Let strEval = "=REPT(I22:I23,2)": Debug.Print strEval                 '  Ctrl+g reveals   =REPT(I22:I23,2)   in the Immediate Window
    140   Let vTemp = Evaluate(strEval)                                         ' Returns "AA"
    150   Let vTemp = Evaluate("=REPT(I22:I23,2)")                              ' Returns "AA"
    160   Let vTemp = Evaluate(strEval)                                         ' Returns "AA"
    170  Rem 2) Trying to get both possible values out.
    180  '2a) review Excel VBA multi value analysis
    190   Let strEval = "=I22:I23": Debug.Print strEval                         '    =I22:I23
    200  Dim RngTemp As Range
    210   Set RngTemp = Evaluate(strEval)
    220   Let vTemp = Evaluate(strEval).Value: Let vTemp = Evaluate(strEval)    '   Default of .Value for returned Range object returned for unspecific Declaration ( Dim ing )
    230   Let strEval = "=I22:I23" & "&" & "I22:I23": Debug.Print strEval       '    =I22:I23&I22:I23
    240   'Set RngTemp = Evaluate(strEval) ' Error '421 Object required. - Excel takes this as a formula and not a referrence, values are given for the two referrences
    250   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BB}
    260   Let strEval = "=I23:I24" & "&" & "I22:I23": Debug.Print strEval       '    =I23:I24&I22:I23
    270   Let vTemp = Evaluate(strEval)                                         ' Returns {BA;CB}
    '2a(ii) When there is a mismatch in Array sizes,
    280   Let strEval = "=I22:I24" & "&" & "I22:I23": Debug.Print strEval       '    =I22:I24&I22:I23
    290   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BB;error 2042}
    'Interception Theory:   http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp              https://www.excelforum.com/tips-and-tutorials/1172587-excel-vba-interception-and-implicit-intersection-and-vlookup.html
    '_(ii) ' Single value
    300   Let strEval = "=I22:I24" & "&" & "I22": Debug.Print strEval           '    =I22:I24&I22
    310   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BA;CA}
    320   Let strEval = "=I22:I24" & "&" & """A""": Debug.Print strEval         '    =I22:I24&"A"
    330   Let vTemp = Evaluate(strEval)                                         ' Returns {AA;BA;CA}
    '_(iii) ' Single breadth
    340   Let strEval = "=I22:J24" & "&" & """A""": Debug.Print strEval         '    =I22:J24&"A"
    350   Let vTemp = Evaluate(strEval)                                         ' Returns {AA,-A;BA,-A;CA,-A}
    360   Let strEval = "=I22:J24" & "&" & "I22:I23": Debug.Print strEval       '    =I22:J24&I22:I23
    370   Let vTemp = Evaluate(strEval)                                         ' Returns {AA,-A;BB,-B;error 2042,error 2042}
    380   Let strEval = "=I22:J24" & "&" & "{""A"";""B""}": Debug.Print strEval '    =I22:J24&{"A";"B"}
    390   Let vTemp = Evaluate(strEval)                                         ' Returns {AA,-A;BB,-B;error 2042,error 2042}
    400 '
    410  '2b)(i) Attempting concatenations
    420   Let strEval = "=I22:I23" & "&" & "REPT(I22:I23,2)": Debug.Print strEval '    =I22:I23&REPT(I22:I23,2)
    430   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA , so first value again
    440   Let strEval = "={" & """""" & ";" & """""" & "}" & "&" & "REPT(I22:I23,2)": Debug.Print strEval ' ={"";""}&REPT(I22:I23,2)
    450   Let vTemp = Evaluate(strEval)                                           ' Just first value, "AA" returned  ' A null string is being concatenated.
    460   Let strEval = "=M40:M41" & "&" & "REPT(I22:I23,2)": Debug.Print strEval       '    =M40:M41&REPT(I22:I23,2)  NOTE: M40:M41 are arbritrary empty cells
    470   Let vTemp = Evaluate(strEval)                                           ' Just first value, AA returned    ' A null string is being concatenated.
    480   Let strEval = "={""A"";""B""}" & "&" & "REPT(I22:I23,2)": Debug.Print strEval '    ={"A";"B"}&REPT(I22:I23,2)
    490  '2b(ii) Killing Interception
    500   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
    510   Let strEval = "={""A"";""B""}" & "&" & "REPT({""A"";""B""},2)": Debug.Print strEval '    ={"A";"B"}&REPT({"A";"B"},2)
    520   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
    530   Let strEval = "={""A"";""B""}" & "&" & """AA""": Debug.Print strEval    '   ={"A";"B"}&"AA"
    540   Let vTemp = Evaluate(strEval) '                                         ' Returns {"AAA";"BAA"}
    550   Let strEval = "={""A"";""B""}" & "&" & "REPT(""A"",2)": Debug.Print strEval '    ={"A";"B"}&REPT("A",2)
    560   Let vTemp = Evaluate(strEval) '                                         ' Returns {"AAA";"BAA"}
    570   Let strEval = "=I22:I23" & "&" & "REPT({""A"";""B""},2)": Debug.Print strEval       '   =I22:I23&REPT({"A";"B"},2)
    580   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
    590   Let strEval = "=I22:J23" & "&" & "REPT({""A"";""B""},2)": Debug.Print strEval       '   =I22:J23&REPT({"A";"B"},2)
    600   Let vTemp = Evaluate(strEval) '   String housed in Variant type --      ' Returns "AAA" which is A & AA   First Value.   KILLED interception
    610 '
    620  '2c) Multivalue from REPT({"A";"B"},2)        "to get Multivalue Wonks"
    630  '2c)(i) Index Wonks
    640   Let strEval = "=Index(REPT({""A"";""B""},2),0,0)": Debug.Print strEval  '=Index(REPT({"A";"B"},2),0,0)
    650   Let strEval = "=Index(REPT({""A"";""B""},2),0)"
    660   Let strEval = "=Index(REPT({""A"";""B""},2),0,1)"
    670   Let strEval = "=Index(REPT({""A"";""B""},2),)"
    680   Let strEval = "=Index(REPT({""A"";""B""},2),,)"
    690   Let strEval = "=Index(REPT({""A"";""B""},2),,0)"
    700   Let strEval = "=Index(REPT({""A"";""B""},2),,1)"
    710   Let vTemp = Evaluate(strEval)      '    All above succesful             ' Returns {"AA";"BB"}                                              '
    720
    730   Let strEval = "=I22:J24" & "&" & "Index(REPT({""A"";""B""},2),0,0)"
    740   Let vTemp = Evaluate(strEval)      '    Intersection well behaved       ' Returns {"AAA,"-AA";"BBB,"-BB";"error 2042,error 2042"}
    750
    760  '2c)(ii) Transpose Wonk
    770   Let strEval = "=Transpose(REPT({""A"",""B""},2))"
    780   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";"BB"}
    790 '
    800  '2c(iii) If(Row(), ) If(Column(), ) Wonks
    810   Let strEval = "=If(Column(),REPT({""A"";""B""},2))"
    820   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";"BB"}
    830 '
    840  '2c(iv) If(Row(j1:j2)xColumn(i1:i2), ) Wonks
    850   Let strEval = "=If(Row(1:2),REPT({""A"";""B""},2))"
    860   Let strEval = "=If(Row(45:46),REPT({""A"";""B""},2))"
    870   Let strEval = "=If(Row(A4:A5),REPT({""A"";""B""},2))"
    880   Let vTemp = Evaluate(strEval)                                           ' All above Returns {"AA";"BB"}
    890
    900   Let strEval = "=If(Row(1:3),REPT({""A"";""B""},2))"
    910   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";"BB";error 2042}
    920
    921 '2c(v) Some background to Row( ) type stuff.
    922   Let strEval = "=If({1;1},REPT({""A"";""B""},2))"
    923   Let strEval = "=If({True;True},REPT({""A"";""B""},2))"
    924   Let strEval = "=If({True;1},REPT({""A"";""B""},2))"
    925   Let strEval = "=If({True;2},REPT({""A"";""B""},2))"                     ' NOTE: any other than 0 is taken as True or 1
    926   Let vTemp = Evaluate(strEval)                                           ' All above Returns {"AA";"BB"}
    927   Let strEval = "=If({True;0},REPT({""A"";""B""},2))"
    928   Let vTemp = Evaluate(strEval)                                           ' Returns {"AA";False}
    ' Behaving itself once the Multivalues are obtained.
    930   Let strEval = "=If(Row(1:3)*Column(A:B),REPT({""A"";""B""},2))"         ' First argument returns an Array size, Interception ( Implicit Intersection on single breadth Arrays ) giving Array ##
    935   Let strEval = "=If(Row(3:5)*Column(AB:AC),REPT({""A"";""B""},2))"       ' First argument returns an Array size, Interception ( Implicit Intersection on single breadth Arrays ) giving Array ##
    940   Let vTemp = Evaluate(strEval)                                           ' All above Returns {"AA","AA";"BB","BB";error 2042,error 2042}
    950 '
    960 Rem 3
    970   Let vTemp = Evaluate("=Row(3:5)*Column(D:E)")                         ' Returns {12,15;16,20;20,25} = {2x4,3x5;4x4,4x5,5x4,5x5}
    End Sub
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Intersection of Overlapping Ranges:Space Operator
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  2. Vlookup Multiple Values By Adding Formula With Loop In VBA
    By Safal Shrestha in forum Excel Help
    Replies: 15
    Last Post: 04-22-2013, 04:49 PM
  3. Replies: 10
    Last Post: 11-27-2012, 08:27 PM
  4. Replies: 4
    Last Post: 08-14-2012, 03:17 AM
  5. Help with a Vlookup and SUMIF
    By Lucero in forum Excel Help
    Replies: 4
    Last Post: 07-24-2012, 05:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •