Code to write to Index Match intersection
 

What would be the code to write to the intersection of the following formula?

Where B1 is a scanned in ID number, like ABC1001 and C1 is the current day Date like 7/13.

I want to put an a number 1 in that intersection. I would want the ability to also enter a string if necessary, so an xyx or a 1)

=INDEX(G5:P13,MATCH(B1,B5:B13,0),MATCH(C1,G2:P2,0) )

Somehow I think I should be using FIND or SEARCH, but don't know how to produce a intersection range.

Thanks,
Howard

Code to write to Index Match intersection
 

Hi Howard,

Am Mon, 13 Jul 2015 22:23:54 -0700 (PDT) schrieb L. Howard:

What would be the code to write to the intersection of the following formula?

Where B1 is a scanned in ID number, like ABC1001 and C1 is the current day Date like 7/13.

I want to put an a number 1 in that intersection. I would want the ability to also enter a string if necessary, so an xyx or a 1)

=INDEX(G5:P13,MATCH(B1,B5:B13,0),MATCH(C1,G2:P2,0) )

Somehow I think I should be using FIND or SEARCH, but don't know how to produce a intersection range.

I don't understand your problem. Where do you want to add a number or a
string? To the result or to the search string? Can you provide an
example with the expected output?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Code to write to Index Match intersection
 

Hi Howard,

Am Mon, 13 Jul 2015 22:23:54 -0700 (PDT) schrieb L. Howard:

I want to put an a number 1 in that intersection. I would want the ability to also enter a string if necessary, so an xyx or a 1)

=INDEX(G5:P13,MATCH(B1,B5:B13,0),MATCH(C1,G2:P2,0) )

I hope I understand it.

Try:

Sub Test()
Dim sRow As Long, sCol As Long

sRow = Evaluate("=MATCH(B1,B5:B13,0)") + 4
sCol = Evaluate("=MATCH(C1,G2:P2,0)") + 6

With Cells(sRow, sCol)
.Value = .Value & "xyz"
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Code to write to Index Match intersection
 

Hi Claus,

The formula I posted refers to a small matrix.

It returns the value in cell J7. Instead of returning a value FROM the intersect, I want to write TO that intersect.

So with ID numbers down a column and Dates across a row, I want to write to the intersect of the ID and the Date (Date will current date as 7/13).

An example workbook.

https://www.dropbox.com/s/c641r1viwl...0Box.xlsm?dl=0

Howard

Code to write to Index Match intersection
 

Hi Howard,

Am Mon, 13 Jul 2015 23:40:24 -0700 (PDT) schrieb L. Howard:

https://www.dropbox.com/s/c641r1viwl...0Box.xlsm?dl=0

try:

Sub Test2()
Dim sRow As Long, sCol As Long

'+4 because you start Match in Row 5
sRow = Evaluate("=MATCH(B1,B5:B13,0)") + 4
'+6 because you start Match in column 7
sCol = Evaluate("=MATCH(C1,G3:P3,0)") + 6

With Cells(sRow, sCol)
.Value = .Value & "xyz"
'.value = range("B2").value
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Code to write to Index Match intersection
 

On Monday, July 13, 2015 at 11:47:30 PM UTC-7, Claus Busch wrote:
Hi Howard,

Am Mon, 13 Jul 2015 23:40:24 -0700 (PDT) schrieb L. Howard:

https://www.dropbox.com/s/c641r1viwl...0Box.xlsm?dl=0

try:

Sub Test2()
Dim sRow As Long, sCol As Long

'+4 because you start Match in Row 5
sRow = Evaluate("=MATCH(B1,B5:B13,0)") + 4
'+6 because you start Match in column 7
sCol = Evaluate("=MATCH(C1,G3:P3,0)") + 6

With Cells(sRow, sCol)
.Value = .Value & "xyz"
'.value = range("B2").value
End With
End Sub


Regards
Claus B.

Hi Claus,

This is exactly where I wanted to go with this.

I used a drop down for the date because I cannot recall the method or function where C2 in the Evaluate formula is actually the current date, instead of referring to C2 for the current date, where the =Now() equivalent would replace C2 and find the match in G3:P3. Then the date drop down would not be needed.

'+6 because you start Match in column 7
sCol = Evaluate("=MATCH(C2,G3:P3,0)") + 6

Is that possible?

Howard

Code to write to Index Match intersection
 

Hi Howard,

Am Tue, 14 Jul 2015 01:09:57 -0700 (PDT) schrieb L. Howard:

I used a drop down for the date because I cannot recall the method or function where C2 in the Evaluate formula is actually the current date, instead of referring to C2 for the current date, where the =Now() equivalent would replace C2 and find the match in G3:P3. Then the date drop down would not be needed.

try:

Sub Test2()
Dim sRow As Long, sCol As Long

'+4 because you start Match in Row 5
sRow = Evaluate("=MATCH(B1,B5:B13,0)") + 4
'+6 because you start Match in column 7
sCol = Evaluate("=MATCH(" & CDbl(Date) & ",G3:P3,0)") + 6

With Cells(sRow, sCol)
.Value = .Value & "xyz"
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Code to write to Index Match intersection
 

try:

Sub Test2()
Dim sRow As Long, sCol As Long

'+4 because you start Match in Row 5
sRow = Evaluate("=MATCH(B1,B5:B13,0)") + 4
'+6 because you start Match in column 7
sCol = Evaluate("=MATCH(" & CDbl(Date) & ",G3:P3,0)") + 6

With Cells(sRow, sCol)
.Value = .Value & "xyz"
End With
End Sub


Regards
Claus B.

Bingo!! Spot on, works perfect.

Thanks a million, Claus.

Howard

Code to write to Index Match intersection
 

Hi Howard,

Am Tue, 14 Jul 2015 01:54:51 -0700 (PDT) schrieb L. Howard:

Bingo!! Spot on, works perfect.

always glad to help you.
The last two months you are working very late. Do you have nightshift?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Code to write to Index Match intersection
 

On Tuesday, July 14, 2015 at 3:09:34 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Tue, 14 Jul 2015 01:54:51 -0700 (PDT) schrieb L. Howard:

Bingo!! Spot on, works perfect.

always glad to help you.
The last two months you are working very late. Do you have nightshift?


Regards
Claus B.

No nightshift. Retired commercial pilot in 2001, I'm at the computer when it is quiet or no John Wayne rerun westerns on TV.

Howard