We help IT Professionals succeed at work.

Excel VBA, locate data with sub tables, repeated rows/column headings

newparadigmz
newparadigmz asked
on
I have the following tables in a report:

Adam
                         Address          Phone            
Home
Work

Barry
                         Address          Phone            
Home
Work

Charles
                         Address          Phone            
Home
Work

What is the best way to cherry pick the data I need to store in a variable in VBA?
For example, Barry's Work Phone or Charles' Home Address

Also, these are in the middle of the spreadsheet, they don't start on A1, so need something purely driven by table name (Adam), and row / column name (Work,Phone).

Thanks
Comment
Watch Question

SILVER EXPERT
Top Expert 2014

Commented:
please elaborate on "cherry pick"

Author

Commented:
For example, Barry's Work Phone or Charles' Home Address

i just mean individual pieces of data
SILVER EXPERT
Top Expert 2014
Commented:
You still aren't clear.  A two short-sentence reply isn't an elaboration.

Here is a vague notion of what I was expecting:
* Post an example of your workbook.
* Post some VBA code.
* Explain where, in the code, you need to access some data and a different/better description of the problem you face.

===========
I suspect that your problem exists because your data isn't laid out in a standard format.  I don't know where the data came from and your options to retrieve the data directly from the source, rather than access it in its report format.
I solved this myself doing the following;

findBarry = find(what:"Barry")
findWork=find(What:"Work", After:findBarry)
findPhone=find(What:"Phone", After:findBarry)
foundBarryWorkPhone=cell(findWork.Row, findPhone.Column)

Author

Commented:
thanks, though

Explore More ContentExplore courses, solutions, and other research materials related to this topic.