Looking data up on a complex grid with VBA 1

[Qazzart]

Good afternoon everyone,

I would like to set up a macro, I have some experience in visual basic, so I know the steps that I would take to create this macro, I just don't know how to search through a column or a row for a specific value, then to save only the value identifying only that column or that row.

I've worked out my steps, just need some help with the lines.

From worksheet #1, it will count how many columns are in a specific row.
Then starting from the first column, it will remember three fields. (A, B C and D). Using those variables, it will find what goes in line E from a table on a different worksheet.

Open the second worksheet (from a share server).

It will then need to search as follows...

1. Search through row 58 (specifically from A58 to BT58) and find a specific value to match to value A. (lets call it "X") we will need to remember only the column that "X" is in.
2. Match for "B". (B can either be yes or no)
3. if “Yes” then set the Column to A (A58), if “No” then the column to B (B58) having the value of “No” shifts the column with the data needed over one to the right.)
4. Search that column for the value to match for C. (Lets call these G, H, and I) and remember that row.
5. Search that same column for another value to match to D ( this will be a decimal number) and remember that row.
6. for the row with the G, H, or I (from line 4) search across that Row for another value (x, y or z) remember that column.
7. match the column from line 6 and the row for line 5 and go to that Cell. copy and paste the value of that cell to line E from the parent worksheet.
8. go to the next column and repeat the above steps until all columns have been filled in with value E.

I know it is all very confusing.

I wish I could upload the layout of the table, that would clear things up, but I’m at work and cannot.

The table is several tables in one.
Think of it like a plus sign, the bottom left will tell you what row will have your answer. The top left will refine that row down even more (for every four in the bottom left, the top left has one.) the top right gives you the column you need to be in for your answer. The answer block is the bottom right.

If your totally confused, then your in the right spot… cause that’s how it is.. (and this is why I would like to create a macro to help me find my answer, and to help me reduce mistakes. )

Here is what I have to begin with...
I would like for it to update as data changes from within the cells that fill in the fields A B C and D.. I don't know how to do that ether, so for now it will run from clicking a button.

Function TableLookup ()
On Error Resume Next
Workbooks("Test.xls").Activate
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 2 To RowCount
Range("a" & i).Select
Aa = ActiveCell.Value
Bb = ActiveCell.Offset(0, 1).Value
Cc = ActiveCell.Offset(0, 2).Value
Dd = ActiveCell.Offset(0, 3).Value

Workbooks("Table0026.xls").Activate

'this is where I need help
'the value will be set to "Ee"

Workbooks("Test.xls").Activate
Range("a" & i).Select
ActiveCell.Offset(0, 4).Value = Ee

Next
End Function

Any help would be very much appreciated
Thank you all!!

Qazzart.

 

[SkipVought]

Hi,

Of course, Table Lookup functions are available on the sheet.

However, please explain, in plain terms, WHAT you are trying to accomplish, rather than trying to explain HOW
your want it done.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Qazzart]

I just want to look up the value on the table, then paste that value into my worksheet.

problem is the table is rather complex and busy. (serves as several tabels in one.)

So what I want to do is simple, I just cant seem to figure out how to do it with this table. I wish it was as simple as writing a formula (vlookup has worked on past tables) but this one is kicking my butt.

I dont think I explained it any better, but at least now you know what Im trying to do.

 

[SkipVought]

Multiple dependent table, I'm guessing?

The first result leads to a subset in a second table (a criterial value for a column in the second table)

Am I on the right track?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Qazzart]

Yes and no...

let me try to explain this way...


yes no yes no [
a [ d e f
a [ d e f
a [ d e f
a [ d e f
b [ d e f
b [ d e f
b [ d e f
b [ d e f
c [ d e f
c [ d e f
c [ d e f
c [ d e f
--------------------------------------------------------------------
[ [.01] [.02] [.03] [.04] [.05] [.06] [.07]
1/3 [ [.02] [.03] [.04] [.05] [.06] [.07] [.08]
1/3 [ [.03] [.04] [.05] [.06] [.07] [.08] [.09]
--------------------------------------------------------------------
X W


so to find out what X would be at 1/3, yes, a and d i would find X on the bottom of the table, find the right column to answer yes or no, then match the a on the top to the d on the top to get my multiplier and come down that column to match with the row for the 1/3

of course this is very simplified... and its even confusing... LOL!

does this make better sense?

 

[SkipVought]

This seems like one of those cases where someone has configured a VISUAL lookup chart, and now you want to automate the lookup process, by using the same "visual" chart technique.

Except that Excel is not designed to work that way, if, indeed, that is the case.

Excel works best with tables:

one row of unique headings
data in every row and column
each table has similar data in each column: Yes/No in the [YesNo] column, name values in the [Names] column, decimal values in the [multiplier] column.....

In this particular case, as far as I can see your TABLE might ought to start looking like...
[tt]
[YesNo] [a col] [b col] [c col] [d col] [1/3 col] [mult]... and what else??????
YES a b c d .01
YES a b c d .02
YES a b c d .03
YES a b c d .04
YES a b c d .05
YES a b c d .06
YES a b c d .07
YES a b c d 1/3 .02
YES a b c d 1/3 .03
..... etc

[/tt]
THEN it becomes a multi-criteria lookup that can be done on a sheet.

Short of that, you'll have to focus on ONE portion at a time for SIMPLICITY to get any VBA help, IMHO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Skip, as always is right. But as a challenge I tried to understand your requirements and the extent to which they could be addressed without VBA:

Without VBA, assuming A is a named range myA etc and, for simplicitly, the second workbook is Sheet2 in the same workbook:

1.    Search through row 58 (specifically from A58 to BT58) and find a specific value to match to value A. (lets call it  "X")  we will need to remember only the column that "X" is in.

=MATCH(myA,Sheet2!A58:BT58,0)

2.    Match for "B". (B can either be yes or no)
3.    if "Yes" then set the Column to A (A58), if "No" then the column to B (B58) having the value of "No" shifts the column with the data needed over one to the right.)

=MATCH(myA,Sheet2!A58:BT58,0)+NOT(myB="YES")

4.    Search that column for the value to match for C. (Lets call these G, H, and I) and remember that row.

=MATCH(myC,OFFSET(Sheet2!A1,0,MATCH(myA,Sheet2!A58:BT58,0)+NOT(myB="YES")-1,65000,1),0)

5.    Search that same column for another value to match to D ( this will be a decimal number) and remember that row.

=MATCH(myD,OFFSET(Sheet2!A1,0,MATCH(myA,Sheet2!A58:BT58,0)+NOT(myB="YES")-1,65000,1),0)

I think the approach could be continued but the formulae would be awful to audit.

Not knowing the size of sheet2 I have assumed the column lookups from row1 to row 65000. Making these ranges as small as possible will speed the calculation.

Gavin

 

[Qazzart]

okay, here is an example of the grid that I'm working with. this is not the actual grid (as thats at work) but very similar.

I have to use this on a daily basis, to look up multipliers.
and mistakes would be very bad. As you can imagine its quite time consuming.

Most of the grids I work with are simple, and I have written macros to pull the data I need off of them... this one is just like you said, visual... so its not very user friendly as far as writing a macro goes.

 

[Qazzart]

Oh, one more thing... I cannot add any code or change the grids/tables in any way.. I can only view them. They are owned and updated by someone else..

 

[SkipVought]

I would never suggest CHANGING the source.

I WOULD suggest making a useful source from this source. I have to digest the structure.

Please give me an example of the GIVEN values for a search and what VALUE you expect to return for those GIVEN values.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I see 5 separate fields...
[tt]
THIS IS NOT A TABLE - just a range of values
Field1 Field2 Field3 Field4 Field5
AAT A D 0.125 .01 to 1.76
CGA B E 0.25
GAT C F 0.5
GGA 0.75
TGA 1
TGC 2
3
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Oh, yes, and Field6 has Yes No Values.


What about the rows that have no Field4 data value?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Qazzart]

lots of empty values.. but these...

AAT,TGA,GAT,GGA,TGC,CGA

were just the tip of the ice burg... there are literally hundreds of fields, and some go from 1/8 all the way to over 2 while others will have only only a few (1/8 to 1/2). I didn't put all of them on there, just made it small so you could visualize the type of table it was.

I know its gotta be doable...

visually, the table really works well for what it does, just wish it was easier to suck the data out of it without having to spend so much time on each one.

I'm hitting the sack, Ill look at it fresh in the morning and try to come up with something from the ideas that have already been proposed.

 

[SkipVought]

What I would like you to do is construct a small replica of this data with 2 values for fields 1, 2, 3, 4 & 6 and corresponding representative values for 5, so that we have a cohesive example to test with.

Then we can work at defining and using various Excel features and coding to attack the process one step at a time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Rather than force you thru an arduous exploratory exersize, let me explain the approch and then post the solution, which you can PASTE into a MODULE in your VB project and then use the LKUP function, as you would any other spreadsheet function.

1. Use the COMMENTS in the posted code to determine the RANGES and NAMES to use for these Named Ranges. Select each of these ranges, in turn and assign the Name in the Name Box of your spreadsheet. Once you have sucessfully completed this for each ranges, you can select each of the 6 Names in the Name Box and observe the correct range selected.

2. Paste the LKUP function into a MODULE in your VB project. Use Insert > Module to add a new module if none exists. Do not paste in a Workbook or Worksheet object code window.

3. On your sheet, any sheet, use Insert > Function and select the Category--User Defined LKUP function. You will notice that the Function Arguments Window displays the 5 arguments. You can supply hard values or reference cells containing each value. I assumed that you had to supply...

Where f1 is a value in the A,B,C range
Where f2 is a value in the Yes,No range
Where f3 is a value in the 1/3,1/2 range
Where f4 is a value in the AAT,GGA range
Where f5 is a value in the D,E,F range

Here's the concept.

I find the f4 value in range Field4.

Then I find the f2 value in the intersection of the COLUMN where f4 is, and the Field2 range.

Then I find the f3 value in the intersection of the COLUMN where f4 is, and the Field3 range.

Then I find the f1 value in the intersection of the COLUMN where f2 is, and the Field1 range.

Then I find the f5 value in in the intersection of the ROW where f1 is and the Field5 range.

And finally the decimal value is in the intersection of the f3 ROW and the f5 COLUMN.

Field6 is really never used, but would define the decimal values range.

Function LKUP(f1, f2, f3, f4, f5) As Single
'f1 is a value (A,B,C...) in the Field1 Range
'f2 is a value (Yes,No) in the Field2 Range
'f3 is a value (1/3,1/2...) in the Field3 Range
'f4 is a value (AAT,TGA...) in the Field4 Range
'f5 is a value (D,E,F...) in the Field5 Range
'Field6 is the range defining the DECIMAL NUMBERS, _
    one of which, this function returns
'these Fields must be Named Ranges, defining each data area of the table
    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range
    
    LKUP = 0#
    
    'Field4 has merged cells--must use MergeArea
    Set r4 = [Field4].Find(f4)
    
    If Not r4 Is Nothing Then
        Set r2 = Intersect(r4.MergeArea.EntireColumn, [Field2]).Find(f2)
        
        'Field2 has merged cells--must use MergeArea
        If Not r2 Is Nothing Then
            Intersect(r2.EntireColumn, [Field3]).Select
            Set r3 = Intersect(r4.MergeArea.EntireColumn, [Field3]).Find(f3)
            
            If Not r3 Is Nothing Then
                Set r1 = Intersect(r2.EntireColumn, [Field1]).Find(f1)
                
                If Not r1 Is Nothing Then
                    Set r5 = Intersect(r1.EntireRow, [Field5]).Find(f5)
                    
                    If Not r5 Is Nothing Then
                        LKUP = Intersect(r3.EntireRow, r5.EntireColumn).Value
                    End If
                End If
            End If
        End If
    End If
    
    Set r1 = Nothing
    Set r2 = Nothing
    Set r3 = Nothing
    Set r4 = Nothing
    Set r5 = Nothing
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Qazzart]

Skip,

I must be doing something wrong, cause I cant get it to work. Later this afternoon when I get home and have more time, I'm going to upload the two spreadsheets so you can check behind me.

this is what I did, incase it pops out that I blatantly did something wrong...

1. I copied the VB into a new module in my worksheet that contained my form.
2. I copied the worksheet that contains the table, into the workbook that contains my form. (not sure if I had to do this, but that is the only way I could name the ranges on the table??)
3. I used the lkup function in the block that would contain my decimal number.

Did I miss anything?

Thanks again
Qazzart

 

[SkipVought]

I used the lkup function in the block that would contain my decimal number.

NO!

This function does NOT CALCULATE a value.

This function LOOKS UP a value that is in your DECIMAL ARRAY (.01, .02, .02 etc) LOOKS IT UP.

You use this funcion OUTSIDE, maybe even on another sheet in your workbook, and provide it the FIVE arguments that will result in the LOOKUP and return of a value that is ALREADY IN YOUR TABLE.

Or have I completely missed the point?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Here's an example of how you might use it.

Suppose that in Sheet9 on row 1, you had these values
[tt]
A B C D E
C Yes 0.75 GGA F
[/tt]
Your function
[tt]
=lkup(A1,B1,C1,D1,E1)
[/tt]
returns 0.7 from the decimal array of the table you posted for download previously.

Is that value not what you would get manually?

I did find an errant statement that I removed, that would make it impossible to use on another sheet. Here's the corrected code...

Function LKUP(f1, f2, f3, f4, f5) As Single
'f1 is a value (A,B,C...) in the Field1 Range
'f2 is a value (Yes,No) in the Field2 Range
'f3 is a value (1/3,1/2...) in the Field3 Range
'f4 is a value (AAT,TGA...) in the Field4 Range
'f5 is a value (D,E,F...) in the Field5 Range
'Field6 is the range defining the DECIMAL NUMBERS, _
    one of which, this function returns
'these Fields must be Named Ranges, defining each data area of the table
    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range
    
    LKUP = 0#
    
    'Field4 has merged cells--must use MergeArea
    Set r4 = [Field4].Find(f4)
    
    If Not r4 Is Nothing Then
        Set r2 = Intersect(r4.MergeArea.EntireColumn, [Field2]).Find(f2)
        
        'Field2 has merged cells--must use MergeArea
        If Not r2 Is Nothing Then
            
            Set r3 = Intersect(r4.MergeArea.EntireColumn, [Field3]).Find(f3)
            
            If Not r3 Is Nothing Then
                Set r1 = Intersect(r2.EntireColumn, [Field1]).Find(f1)
                
                If Not r1 Is Nothing Then
                    Set r5 = Intersect(r1.EntireRow, [Field5]).Find(f5)
                    
                    If Not r5 Is Nothing Then
                        LKUP = Intersect(r3.EntireRow, r5.EntireColumn).Value
                    End If
                End If
            End If
        End If
    End If
    
    Set r1 = Nothing
    Set r2 = Nothing
    Set r3 = Nothing
    Set r4 = Nothing
    Set r5 = Nothing
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Qazzart]

Skip,

You are absolutely right, and yes, I know it doesn’t calculate the value. I just cant seem to get it to return the value.
okay, so here is how I have it set up... let me know if were on the same page...

I copied your latest macro to my personal.xls into a new module labeled LKUP.

I copied the table from the server to my workbook and applied the following names to the ranges

Worksheet #2

Field1 A,B,C values (BN69:EG78)
Field2 yes,no values (BN79:EG79)
Field3 1/8,1/4... values (BN80:EF124)<~actually goes to EF124 but has merged cells)
Field4 aaa, act, etc… (BN125:EF125)<~actually goes to EF125 but has merged cells)
Field5 D,E,F... (EH69:ES78)
Field6 decimal values (EH80:ES124)


Worksheet #1 (the one that needs the decimal value)
the data entry points are in the following cells...

M10: 1/8
N10: A
O10: YES
P10: D
Q10: ACT
R10: =PERSONAL.XLS!LKUP.LKUP(N10,O10,M10,Q10,P10)

but still R10 comes back blank.

Did I grasp your macro right? or am I missing something?
the logic if far beyond my level, so I’m just hoping that I’m putting all the pcs together right.

thanks,
Qazzart

 

[SkipVought]

Insert > Name > Define.

What do you see in the Define Name window?

What is the RefersTo: for each Name? (Please copy & paste here, the RefersTo: references)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]