VBA finding column number and add specific values in column

[Newbie06]

Hello,

I have a sheet named "FinalOverview":

Enter Year:	<Ex: User inputs "2020" in field B1>
	2019	2020	2021
Final Amount	<Value to be computed by macro>	<Value to be computed by macro>	<Value to be computed by macro>

another sheet called "Data". The "Data" sheet has N columns and each column has specific numerical values. Ex:

Year	2019	2020	2021
Legend1	1000	2000	3000
Legend2	10	12	15
Legend3	34	45	98

The user enters values such as "2020" or "2021" in the Sheet "FinalOverview" and will click a btn to trigger macro. The macro then searches for column having "2020" and then uses the corresponding row values to do some calculation such as "0.85*2000+(12-45)" and then the value needs to be put into the cell of FinalOverview below 2020 (C3)

I am fully confused as to how to extract the column ID, use it to do calculation and put it back into the cell.

Can you please guide me

 

[Newbie06]

Got it to work with another user's help, posting the code here in case anyone needs it in future!

Option Explicit

Sub NewbieMacro()
Dim intYear As Integer
Dim intCol As Integer
Dim lngOut As Long

intYear = Sheets("FinalOverview").Cells(1, 2)
'Debug.Print "Looking for Year " & intYear
Sheets("FinalOverview").Cells(2, 2) = "I am lost"

With Sheets("Data")
    intCol = 2
    Do Until .Cells(1, intCol).Value = ""
        If .Cells(1, intCol).Value = intYear Then
            '"0.85*2000+(12-45)"
            lngOut = 0.85 * .Cells(2, intCol).Value
            lngOut = lngOut + (.Cells(3, intCol).Value - .Cells(4, intCol).Value)
            Sheets("FinalOverview").Cells(2, 2) = lngOut
            Exit Do
        End If
        intCol = intCol + 1
    Loop

End With

End Sub