×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Adding results to a list?

Adding results to a list?

Adding results to a list?

(OP)
I've got a spreadsheet that calculates 2 values for data entered along with a drawing number. I would like to create a list that tracks the drawing number and the resulting values automatically so there is a file of work that has been done.

I did a Macro to copy the dwg number and values, then paste (Paste Special, Values, transpose) them under the header. Ctrl +s activates the macro. It works fine but overwrites the same line with each new data set. I've tried 'relative reference' but can't get that to work without placing the cursor on the next cell of the list and making it active. The Help archive is useless.
HELP!
THANKS!!

"If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut."
-- by Albert Einstein

RE: Adding results to a list?

As a Q&D solution you can include a line in your code before the paste special that reads something like:
    ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
This mimics selection of A1, then pressing End and Down arrow, then moving 1 row down. Experiment some with it.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Adding results to a list?

sprintcar,

the solution presented by joerd will work; however, if there is no data in "A1" and in "A2", then either an error will occur (cannot select beyond last row in workbook) or data will be written in last row of workbook - i have not tested the code.

btw, myself and another engineer did the same activity (gathered rotating machinery performance test data) back in the early/mid 90s starting with xl ver 5.  i've since converted the macro to vba and will post code tomorrow - if i do not forget!

perhaps the skilled joerd will offer a solution to posting data; especially when no data exist in log.

good luck!
-pmover

RE: Adding results to a list?

Another - less quick&dirty - way is to use ActiveSheet.UsedRange.Rows.Count to get the total number of rows used, then set the activecell using that number.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Adding results to a list?

(OP)
pmover... did you forget??

"If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut."
-- by Albert Einstein

RE: Adding results to a list?

yes, sorry!  will attempt again tomorrow or maybe later today.

crs frequently occurs . . .

-pmover

RE: Adding results to a list?

Sprintcar

rev your engine a little!  . . .

improvements can be made in simplifying the code here, but it does work.

the range "harddata" is a named range in top row (several continuous columns wide) on sheet named "Unit Data".  the code simply copys the data and paste values only at bottom of current list.  The code is activated by a button on another sheet.  hope this helps and good luck!

'   StoreData Macro
'   Macro recorded 8/9/94 by DG
'
'   Keyboard Shortcut: Ctrl+s
'
'   This macro takes the input & results data and stores
'   the data on the Unit Data Spreadsheet.  This data is
'   in turn used in historically analyzing the engine/compressor
'   performance or operation.  The information is mainly used
'   by Engine Analysis and Reliability Engineering.
'
Sub StoreData()
   Application.ScreenUpdating = False
    Sheets("Unit Data").Select
    Range("HardData").Select
    Selection.Copy
    kount = Application.Cells(3, 2)
    If kount = 0 Then
        Application.Goto Reference:="R7C1"
        Selection.Offset(1, 0).Select
    Else
        Application.Goto Reference:="R7C1"
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
    End If
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Goto Reference:="R1C1"
    Sheets("Calcs").Select
    Application.ScreenUpdating = True
End Sub

good luck!
-pmover

RE: Adding results to a list?

Sprintcar,
Here's an easy solution to try.  Record a macro similar to your initial approach but use the INSERT-copied-cells so that the previous values move over as you insert the new values.  Or if you're transposing, first insert blank cells, moving your previous data over, then execute your copy routine.  This approach usually works for me and I don't have to get into the VB code beyond using the recorder.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources