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

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

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

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

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

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

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