VBA: trying to obtain value based on 2 criteria

Hi All,

I'm new to this forum.

I have a table in excel sheet with several columns; the leftmost column has the names for each row and a subtotal.

The top 3 rows are headers with different names.

I have a task to match the top row with a cell in another sheet and then the Month name in second row.

Based on which I need to obtain the value from the 12th row in the same column.

This has to continue for all the columns.

I have written a Macro, but I'm facing initial hurdle of getting the address of the "arng".

Please help:

Sub mcrCC()

Dim i, n, m As Integer
Dim shArr()

Dim rng, arng, rowrng As Range
Dim wb, wb1 As Workbook
Dim ws, ws1 As Worksheet

Dim filepath As String
filepath = "D:\Sankalp\COst Center report - Macros\" & "India CC_Site PL_V2.xlsx"

On Error Resume Next

Set wb = Workbooks.Open(filepath)

MsgBox wb.Name

Set ws = wb.Worksheets("Kochi")
ws.Activate

MsgBox ws.Name

Set rowrng = ws.Range("B2:D2")

MsgBox rowrng

Set arng = ThisWorkbook.Sheets(1).Range("B65") ' Cost Center Number*/

For Each rng In rowrng.Cells
    stri = rng.Value
    'openPos = InStr(stri, "-")
    closePos = InStr(stri, "-")
    midbit = Mid(stri, 1, closePos - openPos - 1)
    
    MsgBox "Midbit is " & midbit & " arng is " & arng
    
    MsgBox midbit - arng.Value
    MsgBox arng
    'MsgBox arng.Offset(0, 1)
    If midbit = arng Then
    
      'If ThisWorkbook.Sheets(1).Range("C65") = wb.Range(B4) Then
        
        MsgBox rng
        'MsgBox midbit
        arng.Offset(0, 1) = 4
        
      'End If
      
    End If
Next rng

MsgBox arng



End Sub

Hi Kaper,

Thanks for the response.

Please find attached the Sample Excel file. The last tab is the desired output which will be obtained by multiplying the result by 100000

Thanks,