Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2012
    Posts
    10
    Thanks
    3
    Thanked 1 Time in 1 Post

    Selected output from coin inventory

    I am using a coin inventory for US coins, 1 dollar to 1 cent. Columns are: year, mintage qty, mint mark, type(union shield, kennedy half, wheat cent, washington head, jefferson head, etc), detail and qty. Max potential output is over 1400. I wish to print an inventory where i have every qty greater than 0. I want to include the 5 columns to the left of the value, giving me a nice inventory printout. I don't think pivot table, but I am lost on where to start or how to extract. Any help or ideas would be much appreciated. Thank you.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts

    Using VB

    You can do this very easily using VB within Excel. I presume you are trying to do this in excel. You did not mention a version but this code is written in Excel 2010 but should work fine in earlier versions.

    On Error GoTo ErrorHandler
    With Worksheets("Sheet2")
    For I = 1 To 1500 'Number of rows
    If ActiveSheet.Cells(I, 6).Value > 0 Then
    For H = 1 To 6
    .Cells(I, H).Value = ActiveSheet.Cells(I, H).Value
    Next H
    End If
    Next I
    End With
    Worksheets("Sheet2").PrintOut
    Exit Sub
    ErrorHandler:
    MsgBox "Make sure that the target sheet exists"

    This code will rebuild a second sheet row by row if the qty is > 0 then print the rebuild page as a repot

    In this example, Qty is the sixth column and the first row is the header. Adjust the code to meet your needs as follows:
    1. Change the 6 to the column number that is QTY
    2. Change the 1500 to the number of rows it will copy
    3. Change Sheet2 to the sheet name it will copy to (put inside quotes as above). The target sheet must be present or the code will fail
    4. The first line is the header
    Where to put the code?
    Open the Developer Tab (Ribbon)> Visual Basic. Insert>Module. Insert>Procedure>Type PrintSummary> OK. Paste the above code between the following lines:

    Public Sub PrintSummary()

    ....code goes here....

    End Sub
    VB PrintSummary.jpg


    In the attached file, I have attached the code to a Form button that will automatically perform the operation. Perhaps it would be easier if you built off of this attachment.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2012-10-15 at 19:37.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    jmm4908 (2012-10-15)

  4. #3
    New Lounger
    Join Date
    Sep 2012
    Posts
    10
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hello and thanks. I use Excel 2003, but it was converted down from 07 or 10 probably. I have never approached nor learned VB, just aware of it's abilities. I will read and research this, but your script and help is greatly appreciated. Thank you. There are six column groups, one for each denomination, so there are six qty columns. I will need to read up on VB, and i look forward to it. I got this s/s off the internet from a coin shop offering it for users, and will be sharing this work with the author. I have attached excel copy of top 9 cells to display format, cell id's etc. There are 500 rows total, taking the years down into the early 1800's. Off to learn the basics of VB.........
    Attached Files Attached Files

  5. #4
    New Lounger
    Join Date
    Sep 2012
    Posts
    10
    Thanks
    3
    Thanked 1 Time in 1 Post
    I have read further into your comments, and you have explained excellently the steps for Qty, row count, etc. I am just learning, but your work and comments have certainly helped. Even how to introduce it into the worksheet. While I don't have as much work to do, I am going to use this as tutorial for starting VB education.

  6. The Following User Says Thank You to jmm4908 For This Useful Post:

    Maudibe (2012-10-15)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    jmm,

    Here is an excellent site for VB tutorial. It is where I made my start many years ago. Good luck. http://www.garybeene.com/vb/tutor.htm

  8. #6
    New Lounger
    Join Date
    Sep 2012
    Posts
    10
    Thanks
    3
    Thanked 1 Time in 1 Post
    thanks much for the link to garybeene.com looks like i will be spending a lot of time there.


    Here is link to the spread sheet. My cut and post did not do it justice: http://uscollectacoin.com/
    Last edited by jmm4908; 2012-10-15 at 21:27.

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts

    Revised Spreadsheet

    Jmm,

    I took a look at your spreadsheet and adapted the code for all your columns. There is a button on Sheet1 that will print out the Report form (Sheet2) in landscape view and a second button on the report sheet (Sheet2) that will clear the existing report. Also noticed a couple of your formulas giving you the DIV/0 error and fixed them as well. Pretty cool website. My father-in-law is a novice coin collector. I'll point him your way!

    Hope it works out,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2012-10-16 at 06:25. Reason: spelling

  10. #8
    New Lounger
    Join Date
    Sep 2012
    Posts
    10
    Thanks
    3
    Thanked 1 Time in 1 Post
    wow, great. That is not my website nor creation, i just stumbled upon it. he gets the credit for all that work. i did notice some of his formatting/code errors, etc. i planned to share with him the inventory vb code, with credit to you, or you certainly may. best part is I get to learn another chapter of computer stuff! it all helps the world be a better place. thank you!

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    I'll leave the code with you to do whatever you may please. If you ever find yourself stumbling through visual basic, you know where to turn. All the best.

    Maud

  12. #10
    New Lounger
    Join Date
    Sep 2012
    Posts
    10
    Thanks
    3
    Thanked 1 Time in 1 Post
    I have tripped over =concatenate commands to combine cells. that affected the cut and paste i first attempted, and I did know then why. I am working on the VB tutorial. I will need to communicate with the author about his concatenate use. I am using 2003 vers. and perhaps that is not used or issue in 2007 or 2010. thank you for the updated code. jim

  13. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts

    Concatenate functions and copy/paste with cell referencing

    In his use of Concatenate functions:
    Cell E8 =CONCATENATE(D8," ",C8) could have also been written as =D8 & " " & C8 both yielding Sacagawea D. Both are acceptable statements in Excel 97 - 2010. VB does not compile the concatenate function but will compile the combining of terms using the ampersand and plus signs. Make it a habit to use "&" to combine strings while using "+" for calculation of numbers.

    There is an important thing to remember with copying and pasting cells that contain formulas: Relative and absolute referencing.

    Example, the formula for cell A3 =A1+A2. This is relative referencing. If you copy cell A3 and paste it into G5, the formula for G5 is =G3+G4. In short when you use use relative referencing, you are comparing the locations of the cells in the formula to the cell that has the formula. A1 is 2 to the left of A3 + A2 is 1 to the left of A3. When you paste the formula into G5, the relative positions wll enter G3 which is 2 to the left of G5 + G4 which is 1 to the left of G5.

    However, suppose you wanted to paste the formula of a cell into another cell and still point to the same cells, you would use absolute referencing. If A3 =$A$1 + $A$2 and then you copied cell A3 into cell G5, G5 formula would now be =$A$1 + $A$2. So when you use absolute referencing, the contributing cells of the formula do not change.

    Each have their own use, the trick is to know which one to use or combinations of both within the terms of the formula (=$A$1 + A2) or in the cell address of each ($A1 or A$1).

    HTH,
    Maud
    Last edited by Maudibe; 2012-10-17 at 00:05.

  14. #12
    New Lounger
    Join Date
    Sep 2012
    Posts
    10
    Thanks
    3
    Thanked 1 Time in 1 Post
    Yes, good info on absolute and relative. I was more aware of the "&" function in place of the older concatenate. Here though, it seems the columns e, and others, are not visible in the normal working sheet,and they are having adverse effect, I think, on the VB script. The columns exist, but do not display. Therefore the VB script has to account for them? It perhaps seems to affect the Do Not Have worksheet, at least with the pivot table work he has done.

    That is a fair bit of work on the addtional coding, and I thank you for that. Good learning material.

    I am plugging along, and did send email to the author. I think once I understand the reason for his use of the concatenate and non-displayed column(s), I can adjust accordingly with the VB. Just a tool to use for learning, and exceptions seems to open up the learning process for me.

    thanks, JIM

Posting Permissions

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