Excel VBA help ?

[El Greco]

I basically need to help someone with an assignment. Don't ask why, I've been asking "why me" all day long. The problem is I don't know much about Excel. I'd be happy to learn but the time-limit is rather pressing. The assignment is here (.doc file) and what the student has done so far is here (.xls file). What we're interested in is this part: "b) have a user interface sheet that just deals with one day; when the day is finished use a macro to copy the current day values to the correct row of the history sheet."

So, if anyone is a macro wizard with some time to spend....

 

[Yahweh]

Copy this code, then assign it to the button labeled "Save to History Details":

Sub Save_Current_Day_Values()

Dim CurrentDay As Integer
Dim PredictedDemand As Single
Dim Temperature As Integer
Dim PricePerCup As Currency
Dim QuanLemonade As Currency
Dim QuanIcecubes As Integer
Dim QuanCups As Integer
Dim TotalQuantity As Integer
Dim CupsSold As Integer
Dim TotalSales As Integer
Dim TotalCosts As Currency
Dim TotalProfit As Currency

Dim iRowOffset As Integer
Dim iColOffset As Integer

    iRowOffset = 4  'Set this variable equal to (row number in which day "1" appears) - 1
    iColOffset = 0  'In case you move your table left or right, you can set this variable
                    'equal to (column number in which day "1" appears) - 1
    
    'The two above variables can be useful when scaling a project. I choose to use them to
    'so I can make use of the Cells(somerow, somecol) feature rather than
    'Range(cellname). It seems like I'm taking the hard way to figuring this out, but
    'in actuality I'm just cutting out the process that I'd need to take to locate which
    'row to work on when specifying a day (and furthermore translating that row and column
    'to a cellname).

    Sheets("Daily_Performance").Select
    
    'Saving all the cell values to temporary variables
    With ActiveSheet
        CurrentDay = .Range("B3")
        PredictedDemand = .Range("E3")
        Temperature = .Range("B5")
        PricePerCup = .Range("E5")
        QuanLemonade = .Range("E13")
        QuanIcecubes = .Range("E14")
        QuanCups = .Range("E15")
        CupsSold = .Range("C20")
        TotalSales = .Range("C21")
        TotalCosts = .Range("C22")
        TotalProfit = .Range("C23")
    End With
    
    'Writing all the cell values to History_Details
    Sheets("History_Details").Select
    
    With ActiveSheet
    'Note: When using iColOffset + SomeNumber, set SomeNumber to (number of cells to the
    'right of day "1") + 1
    
        .Cells(iRowOffset + CurrentDay, iColOffset + 2) = Temperature
        .Cells(iRowOffset + CurrentDay, iColOffset + 4) = PricePerCup
        .Cells(iRowOffset + CurrentDay, iColOffset + 6) = CupsSold
        .Cells(iRowOffset + CurrentDay, iColOffset + 12) = QuanCups
    End With
    
End Sub

That is a rough outline of the code you need, all it will take is just a bit of copy and paste work to finish finish writing all the variables to their appropriate spot on the sheet called "History_Details". I would have completed the code myself, but some of the table details were a little vague, and I could not tell where some values ought to be pasted. To finish up the code (if you already know what you are doing) will take no longer than about 5 minutes.

 

[El Greco]

Thanks Yahweh, I appreciate it