Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inputing data into next open cell (Excel 2003)

    I have a question for the gurus here. I am trying to automate data entry for my area at work. They currently use a calculator to average, but it is a spreadsheet. I know you can use an average function to calculate it, but it is not a simple average. Nevertheless, I would like to make it so that when said average is calculated, It will automatically populate another workbook. But, I would like it to go to the first empty cell in a column, so that we can chart it. I am pretty sure we have to do it in code, any ideas where to start?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputing data into next open cell (Excel 2003)

    You'll have to provide a bit more information.
    Where will the average be calculated? In a fixed cell, or can that vary?
    Will the average be calculated as an Excel formula, or will the operator use the calculator and type in the average?
    Will the other workbook be open, or should the code open it if necessary?
    Should the other workbook be updated each time the average changes?

  3. #3
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Basically, the calculator is a separate workbook. The person would use it you figure out their average, and then I would like to have it automatically input it into a cell in another workbook. The cell will vary, hopefully having it fill itself in downwards, in the next empty cell. This is kind of a retrofit to what they are already using, thus why I am using the calculator. The other workbook can be open, or can be opened, whatever is easiest. Also, I would like to then take the charts created with this data, and have them roll into a powerpoint presentation so that it can be presented on a plasma display we have in the area. I would like to have the other workbook updated whenever a change is made, as I am dealing with a lot of people who don't know a lot about computers, so the most painless way would be the best.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Could you provide an example of each separate workbook and detail what you want and need to occur?

    To have powerpoint read a chart, you just have to copy the chart from excel and paste it into powerpoint as an object and link. Then when you open powerpoint, the link can be updated with the latest chart.

    Steve

  5. #5
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Thanks for the help. The inputting of the chart into powerpoint is the easy part. The part that troubles me is that I have to make it easy for the people that input the data. I would like to have it so that when they get their average, it will be automatically inputted into the excel worksheet, and then said data will be charted. But, we would like to have it so that the past 31 days is on the chart, and the older info falls off the chart. But, we would like to be able to keep the older data so that we can track it back for the entire time. I am not able to post the worksheets, for security reasons, but I will explain what I would like to happen as best as I can. The calculator is a separate workbook, in which the person would enter their data. 10 samples, 4 machines. So there is 40 numbers for one area. Each machine is then averaged, and I would like to have that info go directly into a separate worksheet, automatically, in the next open cell, filling in downwards. Then the chart is created using the previous 31 days, to show a trend.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputing data into next open cell (Excel 2003)

    I understand that you can't post the original workbooks, but you might prepare stripped down copies with some dummy data, so that Loungers reading this thread have an idea of what you're working with. Otherwise, it's too abstract (for me, at least).

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputing data into next open cell (Excel 2003)

    You don't have to attach the actual workbook. Just create samples with non-proprietary data with the relevant areas you need to do. To help with any macro coding we need to have the details of how the relevant worksheets and workbooks are setup.

    What you want could consist of one line of code:

    Workbooks("WorkbookA.xls").Worksheets ("Sheet1")
    .Cells(.Cells.Rows.Count, 1).End(xlUp). _
    Offset(1, 0).Value = Application.WorksheetFunction.Average _
    (Workbooks("WorkbookB.xls"). _
    Worksheets("Sheet1").Range("A110"))

    Which will put the average from the open WorkbookB.xls, and the range of cells A110 in Sheet1 and put it into the first blank row of the open workbookA.xls in Sheet1 of Column A. You could add this to an existing procedure or have it as a standalone to be run at the press of a button.


    But other than this line of code, I am not sure what else you want (and I am not completely sure the line of code is even what you want). You need to be more detailed on your wants and needs...

    Steve

  8. #8
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Thanks for the help. I have made a file for your viewing pleasures. The basic premise that I am going for is to have the "Calc" tab average add itself to the next available line of the "repairs - front" tab. Keep in mind they will not be in the same workbook, but I have put them together for you to view.
    Attached Files Attached Files
    • File Type: zip 1.zip (4.1 KB, 2 views)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Which value should be copied?

  10. #10
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputing data into next open cell (Excel 2003)

    The values that total up at the bottom of the the 150 Susp table. The total average for each cell should be copied into the other worksheet under the coresponding heading. But, I would like to have them fill into the next empty cell, so that we can track the trends.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputing data into next open cell (Excel 2003)

    The value in B21 on the calc sheet will change each time one of the cells B10:B19 changes. Do you really want to add a new line whenever on of these cells changes? If so, you could use the following code in the worksheet module of the calc sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    If Not Intersect(Target, Range("B10:B19")) Is Nothing Then
    Worksheets("Repairs - Front").Range("B" & _
    Rows.Count).End(xlUp).Offset(1, 0) = Range("B21")
    End If
    If Not Intersect(Target, Range("C10:C19")) Is Nothing Then
    Worksheets("Repairs - Front").Range("C" & _
    Rows.Count).End(xlUp).Offset(1, 0) = Range("C21")
    End If
    If Not Intersect(Target, Range("D1019")) Is Nothing Then
    Worksheets("Repairs - Front").Range("D" & _
    Rows.Count).End(xlUp).Offset(1, 0) = Range("D21")
    End If
    If Not Intersect(Target, Range("E10:E19")) Is Nothing Then
    Worksheets("Repairs - Front").Range("E" & _
    Rows.Count).End(xlUp).Offset(1, 0) = Range("E21")
    End If

    ExitHandler:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Hans had a different interpretation of your desires than i did, so I will post my code as well. I did not think that you wanted to transfer the average at each of the 10 entries in the column, but after the 40 entries were added, the user would press a button to start the transfer.

    I presumed that the worksheet named "Repairs - Front" is in a workbook named "RepairTracking Example.xls" and is open when the code is run ( you could have the code open at the start and close it at the end if desired). The worksheet named "150 susp, beam & arm calc" would presumably have button assigned to the code below which would be in a module in the workbook that the worksheet named "150 susp, beam & arm calc" is located in. You can change the names of the workbook and or worksheets to match your actual names.

    Steve
    <pre>Option Explicit
    Sub Transfer150Susp()
    Dim wks150 As Worksheet
    Dim wkbRepair As Workbook
    Dim wksRepair As Worksheet
    Dim lRow As Long
    Dim iCol As Integer

    On Error GoTo ErrHandler
    'Change workbook and worksheet names as appropriate
    Set wks150 = ThisWorkbook.Worksheets("150 susp, beam & arm calc")
    Set wkbRepair = Workbooks("RepairTracking Example.xls")
    Set wksRepair = wkbRepair.Worksheets("Repairs - Front")
    With wksRepair
    lRow = .Cells(.Cells.Rows.Count, 2).End(xlUp).Row + 1
    End With

    For iCol = 2 To 5
    wksRepair.Cells(lRow, iCol) = wks150.Cells(21, iCol)
    Next
    MsgBox "Data transfered to row " & lRow & vbCrLf _
    & "in Workbook: " & wkbRepair.Name & vbCrLf _
    & "in Worksheet:" & wksRepair.Name

    ExitHandler:
    Set wks150 = Nothing
    Set wksRepair = Nothing
    Set wkbRepair = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub</pre>


  13. #13
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Thank you so much for all your help guys. I really appreciate it. I will load the code in tomorrow, when I have a bit more time. Again, thanks for all your help, I know where to go when I need some help.

  14. #14
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Steve,

    I used your code to do it, and it worked like a charm. But now, I need to do the same thing, but instead of transfering it into the columns 2-5, I need it to transfer the data into columns 7-10 in the same worksheet. If I copy the same code, what do I need to change to have the code able to transfer into a different column?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputing data into next open cell (Excel 2003)

    Change the line

    wksRepair.Cells(lRow, iCol) = wks150.Cells(21, iCol)

    in Steve's code to

    wksRepair.Cells(lRow, iCol + 5) = wks150.Cells(21, iCol)

    This adds 5 to the column number of the target cell (that of the source cell remains unchanged).

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •