VBA for intersection of two named ranges

[brucemc]

I'm sorry. The answer has got to be out here somewhere, but I feel lik
I am beating my head against the floor.

A one sheet workbook.

Four named vertical ranges of 50 items each (Lets pretend each of th
four range names is a plant types).

50 named horizontal ranges of 4 items each (each range name is a
letter state abbreviations).

For simplicity, let's say that the resultant table is of the word
"No", "Poor","Good" "Great".

The user selects a state and a "plant" from two validation set dro
down lists. The workbook change event is triggered, and there goes th
code to determine the word or value in the cell that is at th
intersection of the two, let's say again for simplicity "MI" an
"Banana Tree", which would probably result in a "NO".

I can write the names of the ranges separated by a space after an = i
a cell and easily get the result, but I sure can't figure out how t
transfer the result of two drop-down boxes into same formula (at leas
not so it works), and all the more worse to do so via VBA. I would LOV
to be able to do so both ways, but just one would save me. Please hel
before my head explodes

 

[Guest]

Haven't we all beat our heads.

Are you using dropdown boxes on the sheet, or in a user created form?

I like creating vba forms with OK & Cancel buttons and list boxes and drop
down combos, you know.

Let me know if this would help.

 

[brucemc]

Just right there on the sheet, trying to make it as simple as possible
If I only made it more complicated I could pretend that there was goo
reason for me not being able to do something that should be s
simple..

 

[Guest]

This is my interpretation of your request:

Where cells G1 and H1 have the dropdown lists. The code selects the cell
instead of returning the value. Change to suit:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, cc As Range

Set c = Me.Range("G1")
Set cc = Me.Range("H1")
If Intersect(Target, Union(c, cc)) Is Nothing Then Exit Sub
If IsEmpty(c) Or IsEmpty(cc) Then Exit Sub
On Error Resume Next
Intersect(Range(c.Value), Range(cc.Value)).Select
On Error GoTo 0
End Sub

Regards,
Greg

 

[Guest]

Worksheetfunction version that returns the cell value of the intersection:

Where cells G1 and H1 have the dropdown lists.

=INDEX(INDIRECT(H1), ROW(INDIRECT(G1)))

Regards,
Greg

 

[Guest]

I forgot to point out that you need to subtract an offset if the vertical
ranges don't start in the first row. The function would have been better
stated as:

Where OS is the row number of the top of the vertical ranges.

=INDEX(INDIRECT(H1), ROW(INDIRECT(G1)) - OS)

Regards,
Greg

 

[brucemc]

Thank-you very much, this looks like it should work, if only I can fix
whatever I have done wrong:

In my working VBA with Access I regularly used the ME. reference, but
now with Excel it is not recognizing it for some reason and I have to
change it to Worksheets(1). - Any idea what would cause this? (Office
2003)

Though the code runs fine (without errors prior to introducing
"Indirect" and changing ME to WorkSheets(1)), it does not select the
cell - thoughts?

I am getting an undefined variable for INDIRECT - any thoughts on the
cause of this too?

 

[Guest]

Point 1:-
The Me reference should work if you put the code in the worksheet code
module (e.g. Sheet1(Sheet1)) instead of a standard module. I have it working
at my end. Granted, this is all in accordance with my interpretation of your
situation. I may have it wrong.

Point 2:-
There is no reference to "INDIRECT" in my VBA version. The INDIRECT
worksheet function is contained in the worksheet fucntion (wf) that I
intended to be entered in a worksheet cell. I do use "Intersect" however.
Possibly, you may have to qualify it as:

Application.Intersect

There are two references in the code. Try changing both.

Point 3:-
I had both the VBA code and the wf working provided the row offset issue is
accounted for. As usual, I blew the post re the wf version. OS should be the
row number of the top of the vertical ranges - 1. If the vertical ranges
start in row 5 then OS should be 4. You don't need it if they start in the
first row.

Point 4:-
The vertical range dropdown is assumed to be in G1 and the horizontal range
dropdown in H1. You may have to reverse these in the code and wf.

Regards,
Greg

 

[brucemc]

Great THANKS! With a little adjusting it works, and works very well.