Write to intersect of a row and column

[L. Howard]

This returns the value of the intersection of the drop downs in D2 and F2.

Sub Update()

Dim MyValue$

MyValue = Evaluate("INDEX(C5:G8,MATCH(D2,B5:B8,0),MATCH(F2,C4:G4,0))")
MsgBox MyValue

End Sub

I want to write the value of cell I2 TO the intersection of the drop downs in cells D2 and F2.

I thought the Intersect function would be the key, but all I can find is RETURN the value not ENTER a value in the intersection.

Thanks,
Howard

 

[Claus Busch]

Hi Howard,

Am Sat, 29 Nov 2014 08:28:41 -0800 (PST) schrieb L. Howard:

MyValue = Evaluate("INDEX(C5:G8,MATCH(D2,B5:B8,0),MATCH(F2,C4:G4,0))")

this is not an array function to use Evaluate. Write it with
Worksheetfunction:

With WorksheetFunction
Range("I2") = .Index(Range("C5:G8"), .Match(Range("D2"), _
Range("B5:B8"), 0), .Match(Range("F2"), Range("C4:G4"), 0))
End With


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sat, 29 Nov 2014 17:41:44 +0100 schrieb Claus Busch:

With WorksheetFunction

or with Evaluate:

Range("I2") =Evaluate("INDEX(C5:G8,MATCH(D2,B5:B8,0),MATCH(F2,C4:G4,0))")

Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sat, 29 Nov 2014 08:28:41 -0800 (PST) schrieb L. Howard:

I want to write the value of cell I2 TO the intersection of the drop downs in cells D2 and F2.

I guess I misunderstood ypor problem.
Is it that what you wanted?

Sub Update2()
Dim myAddr As String

myAddr = Evaluate("Address(MATCH(D2,B1:B8,0),MATCH(F2,A4:G4,0))")
Range(myAddr) = Range("I2")
End Sub


Regards
Claus B.

 

[L. Howard]

Hi again,

Am Sat, 29 Nov 2014 08:28:41 -0800 (PST) schrieb L. Howard:


I guess I misunderstood ypor problem.
Is it that what you wanted?

Sub Update2()
Dim myAddr As String

myAddr = Evaluate("Address(MATCH(D2,B1:B8,0),MATCH(F2,A4:G4,0))")
Range(myAddr) = Range("I2")
End Sub

Yes indeed, I believe this does it perfectly.

I'll plug it into my project and give it a go.

Thanks,
Howard

 

[L. Howard]

Yes indeed, I believe this does it perfectly.

I'll plug it into my project and give it a go.

Thanks,
Howard

Hi Claus


This is strange, can't figure it out.

Code in a standard module.

Sub Update2()
Dim myAddr As String

myAddr = Evaluate("Address(MATCH(C1,A4:A9,0),MATCH(E1,B3:I3,0))")
MsgBox myAddr
Range(myAddr).Select ' = Range("I1")
End Sub

The code selects Range("E6")on the sheet. Msgbox = $E$6

Formulas on the sheet.

=INDEX(B4:I9,MATCH(C1,A4:A9,0),MATCH(E1,B3:I3,0))
Returns the value in F9.

=ADDRESS(MATCH(C1,A4:A9,0),MATCH(E1,B3:I3,0))
Returns the address $E$6

The correct cell should be F9.

What ever is selected in the drop downs C1 and E1 the error with the =ADDRESS(...) formula is always one column to the left and three rows up from the correct cell, while the INDEX/MATCH formula is correct.

Howard

 

[Claus Busch]

Hi Howard,

Am Sat, 29 Nov 2014 11:19:14 -0800 (PST) schrieb L. Howard:

myAddr = Evaluate("Address(MATCH(C1,A4:A9,0),MATCH(E1,B3:I3,0))")

MATCH returns the column number refering to the range. For the correct
column number you have to start in A and 1 or you have to add the
offset:
myAddr = Evaluate("Address(MATCH(C1,A1:A9,0),MATCH(E1,A3:I3,0))")
or
myAddr = Evaluate("Address(MATCH(C1,A4:A9,0)+3,MATCH(E1,B3:I3,0)+1)")


Regards
Claus B.

 

[L. Howard]

MATCH returns the column number refering to the range. For the correct
column number you have to start in A and 1 or you have to add the
offset:
myAddr = Evaluate("Address(MATCH(C1,A1:A9,0),MATCH(E1,A3:I3,0))")
or
myAddr = Evaluate("Address(MATCH(C1,A4:A9,0)+3,MATCH(E1,B3:I3,0)+1)")


Regards
Claus B.

Thanks Claus. Clears that up, and seems to be working just fine.

Howard