Excel VBA or Lookup Help

Expand Post »

Unfortunately, I don't think this question is solved with a simple vlookup, or etc.

I have a spreadsheet that has several tables of data with the following format on one sheet:

gxxxx
gxxxx
gxxxx
zzzzz

gxxxx
gxxxx
gxxxx
zzzzz

The "g"s above would indicate an item for lookup. The "z"s would be another lookup reference. The "x" fields will always be a numeric value.

I have a field that defines a lookup value which is associated with the "z" data. Each of these tables are defined as one named range and I can't be sure ahead of time which table the lookup value will be found in.

What I would like to do is as follows:
1. Have the value "z" be located in my tables
2. Identify any values "x" in the column above the "z" value that are non-zero.
3. Return the corresponding value "g" for this lookup.

What I'm guessing needs to be accomplished is to somehow identify the column and row of the lookup on z. My tables are each a defined length, so I can then return a sub-range based on this lookup. So, e.g. if the value is found to be in B200, I could define my range as B141:B200.

I can then scan this range for any values that are non-zero - for each one, I would probably want to assign the value of the cell to one variable (dim value); identify the row that the value is in (dim row), and then return the corresponding value from column A ("A" & row) into a variable (dim item). I would then populate two fields with the values from row and item and move on to the next non-zero "x" and return the x and corresponding "g".

Does anyone have any ideas on just how to accomplish this?

Similar Threads

Trojan.Cachecachekit in Viruses, Spyware and other Nasties
VBA - Excel - sheet,range lookup with variables in Visual Basic 4 / 5 / 6

omrsafetyo

Reputation Points: 13

Solved Threads: 9

Junior Poster in Training

Offline

58 posts
since Apr 2008

Permalink

Jan 23rd, 2009

Re: Excel VBA or Lookup Help

Okay, I came up the following code which finds the cell address of the lookup (z) data.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Sub lookup()
Dim lookup
lookup = Range("lookup").Value
For each c in Range("lookup_range").Cells
   If (c.Value = lookup) Then
       Range("lookup_return").Value = c.Address
       Exit Sub
   End If
Next
End Sub
Sub lookup()
Dim lookup
lookup = Range("lookup").Value
For each c in Range("lookup_range").Cells
   If (c.Value = lookup) Then
       Range("lookup_return").Value = c.Address
       Exit Sub
   End If
Next
End Sub

For now I return the address to a placeholder cell.
This returns a value of, e.g. $A$67. I can also return just the row or column with c.Column, c.Row.

Moving on to the next part - but help is still appreciated. Thanks!

omrsafetyo

Reputation Points: 13

Solved Threads: 9

Junior Poster in Training

Offline

58 posts
since Apr 2008

Permalink

Jan 26th, 2009

Re: Excel VBA or Lookup Help

I have this all figured out now.

omrsafetyo

Reputation Points: 13

Solved Threads: 9

Junior Poster in Training

Offline

58 posts
since Apr 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: vb6 moving frameless form on screen

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Excel cell references

DaniWeb Message

Cancel Changes

User Name
Password