Use cell value in column and cell value in row to locate cell

[ThomasOES]

Hello
I have a worksheet with a row listing elements. Example (C, Ca, Co, Cu, Mn). Then a column listing sample labels. Example (A-1, A-2, B-1, B-2)

Looks like this

How to use vba to place a C value for sample A-1?
Sheet name is "Summary"
I would like something like

Sheets("Summary").Rows(3).Find(elsym).Offset(1).Value = WorksheetFunction.Average(posavgs)

At this point in the macro the current element being processed is referred to as "elsym"
The code works to locate the column
Of course offset wont work
I need to use the sample name to determine the row and the element symbol to determine the column.
Thanks for any help

Tom

 

[hernantorres23]

Hi, an idea

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		C	Ca	Co	Cr	Cu	Mn			For testing purpose
2	A-1									B-1	Co	Range
3	A-2									3	3	$D$4
4	B-1
5	B-2						89.3
6
7
ThomasOES

Cell Formulas
Range	Formula
J3	J3	=MATCH(J2,$A$2:$A$5,0)
K3	K3	=MATCH(K2,$B$1:$G$1,0)
L3	L3	=ADDRESS(J3+1,K3+1)

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=code
K2	List	=symbol

Option Explicit
'Hernan Torres, Mikel ERP
'December 9, 2019

Sub locate()
Dim xVal, yVal  As Long
Dim x, y As String
Dim fila, columna As Range
Dim cell As Range
Dim valor As Variant

Set fila = Sheets("ThomasOES").Range("B1:G1")
Set columna = Sheets("ThomasOES").Range("A2:A5")
MsgBox Range(fila.Address).Row

x = InputBox("Enter a symbol", "Mikel ERP")
y = InputBox("Enter a code", "Mikel ERP")
valor = InputBox("Enter a value for this intersection", "Mikel ERP")
Set cell = Cells(Application.Match(y, columna, 0) + 1, Application.Match(x, fila, 0) + 1)
'Cells(Application.Match(y, columna, 0) + 1, Application.Match(x, fila, 0) + 1) = valor
Range(cell.Address) = valor
MsgBox "Address for your input is " & cell.Address, vbInformation, "Mikel ERP by htorres"

End Sub

 

[ThomasOES]

Thanks htorres
I wanted to use index match and found this to work

elsym = "C"
Set sumsht = ThisWorkbook.Sheets("Summary")
sumsht.Select
Set elrng = sumsht.Range("B3", Range("B3").Offset(, 50))
Set samprng = sumsht.Range("A4").Resize(Range("sampiece").Value)
Set sumrng = sumsht.Range("B4").Resize(Range("sampiece").Value, 50)
With Application.WorksheetFunction
    .Index(sumrng, _
        .Match("A-1", samprng, 0), _
        .Match(elsym, elrng, 0)) _
        .Value = "0.001"
End With

Works perfect except the sheet must be selected to set elrng, samprng, and sumrng
I want to remain on the sheet that is generating the data and have the index match value filled in Sheets("Summary")
Any ideas?
Tom

 

[ThomasOES]

I got it.

Set sumsht = ThisWorkbook.Sheets("Summary")
Set elrng = Range(sumsht.Range("B3"), _
                sumsht.Range("B3").Offset(, 50))
Set samprng = Range(sumsht.Range("A4"), _
                sumsht.Range("A3").Offset(Range("Sampiece").Value))
Set sumrng = Range(sumsht.Range("B4"), _
                sumsht.Range("B3").Offset(Range("Sampiece").Value, 50))
With Application.WorksheetFunction
    .Index(sumrng, _
        .Match(Range("Samsheet").Value, samprng, 0), _
        .Match(elsym, elrng, 0)) _
        .Value = WorksheetFunction.Average(posavgs)
End With

This fills the cells on different sheet with the element value for each sample