Hello, I have a sheet named "Fin

[NewbieMacro]

Hello,

I have a sheet named "FinalOverview":

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

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

 

[Andrzejek]

If that's your data in "FinalOverview" sheet:

[pre]
A B
1 Enter Year: [red]2020[/red]
2 Final Amount:
[/pre]
And that's in "Data" sheet:

[pre]
A B C D E
1 Year 2019 2020 2021 2022
2 Legend1 1000 2000 3000 4000
3 Legend2 10 12 15 10
4 Legend3 34 45 98 25
[/pre]
This macro will put 1667 in cell B2 for year 2020 in "FinalOverview" sheet

Option Explicit

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

intYear = Sheets("FinalOverview").Cells(1, 2)[green]
'Debug.Print "Looking for Year " & intYear[/green]
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[green]
            '"0.85*2000+(12-45)"[/green]
            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

Change year in cell B1 and run the macro again, you should get different results for this new year.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[NewbieMacro]

Dear Andy, you are bang on target. It works perfectly fine as you pointed out!!!!! Thank you so much