Updating value in table based on user input

**danresnick.dr** · 02-10-2010, 06:53 PM

Hi all,

I have one sheet to act as a splash page for user input. The second sheet stores all relevant data. My goal is for the user to input a numerical value in a cell (or input box) and then click a submit button. The code for the macro should look at a cell on sheet 1 which displays the primary key of the row in the table on sheet 2. Based on that primary key, it should look to sheet 2 and then insert the value into the table in the correct row and column. The column headings are the days of the week, and the specific day the user is concerned with is also displayed on sheet 1.

Sheet 1:

A1 = Day of the week
A2 = Unique primary key
A3 = user input value

Sheet 2:
table
Rows=primary key (001 - 999)
Columns = days of the week (Monday - Friday)

example:
user inputs '5' on sheet 1 with 'Tuesday' and '007' selected in their respective cells.
I would then like 5 to be copied to B7 (row 7 for 007 and column B for Tuesday).

Can anyone provide some clues or guidance on the code needed to make this work?

Thank you in advance for any help.

-Dan

**Leith Ross** · 02-10-2010, 07:10 PM

Hello Dan,

This macro will uses cells "A1:A3" on "Sheet1" and assumes the table is on "Sheet2". If your worksheet sheet names are different then you will need to change them in the macro.

Sub Macro1A()

  Dim C As Long
  Dim R As Row
  
    With Worksheets("Sheet1")
      Select Case LCase(.Range("A1"))
        Case Is = "monday", "mon": C = 1
        Case Is = "tuesday", "tue": C = 2
        Case Is = "wednesday", "wed": C = 3
        Case Is = "thursday", "thu": C = 4
        Case Is = "friday", "fri": C = 5
      End Select
      R = .Range("A2")
      Worksheets("Sheet2").Cells(R, C) = .Range("A3")
    End With
    
End Sub