Insert cell value somewhere in a column based on value in different cell

1
votes

I have two cells where the user can input two values (Year and Salary):

A1 = Year
A2 = Salary

After the user inputs these two values, I would like for them to be able to press a button - this would then run a macro and insert the Salary (A2) into a column on a different sheet (Sheet 2).

BUT - the row where the Salary value is inserted in Sheet 2 needs to be dependent on the Year value (A1). For example:

(Sheet 1 - User input)

A1 = 2007

A2 = 50000

.

User presses macro button

.

(Sheet 2-macro result)

Column A....................Column B

Year.........................Salary

2005

2006

2007.........................50000

2008

2009

2010

The user can repeat this for every year - essentially using cells A1 and A2 to create the table in Sheet 2. I also need the user to be able to write over any previously inputted value (e.g. if the Salary in 2007 should have actually been 40000, not 50000).

I know this is a fairly more complicated method (ideally, the user would just update the values in Sheet 2) but my boss requested this alternative method.

2
vbaexcel
What's wrong with a simple LOOKUP function? - Comintern
From your question it appears that user inputs for only one person and one sheet. Normally such data are to updated for a number of employees or customers. What is the frequency of such updates. Where is the user ID unique field. If you upload sample sheets to some uploading site, you may get better and quicker answer. - skkakkar

2 Answers

0
votes
   Sheet2.Columns("A").Find(Sheet1.Range("A1").Value, , xlValues).Offset(0, 1).Value = Sheet1.Range("A2").Value
0
votes

First you need to name both sheets as "Sheet 1" and "Sheet 2". Then press alt+F8 and create a macro named "save_salary" with the following code:

Sub save_salary()

Dim lastrow As Long
Dim i As Long

'Find the last available year
lastrow = Sheets("Sheet 2").Cells(1, 1).End(xlDown).Row

For i = 1 To lastrow
    'This loop will run until years match
    If Sheets("Sheet 2").Cells(i, 1) = Sheets("Sheet 1").Cells(1, 1) Then
        'Save new salary
        Sheets("Sheet 2").Cells(i, 2) = Sheets("Sheet 1").Cells(2, 1)
    End If
Next i

End Sub

Then the next step would be to insert a button. In this case I would just insert a Shape (Insert tab -> Shapes) and assign a macro to it (right click on the shape -> Assign Macro -> "save_salary").

Test it! ;-)