Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Benbrook, Texas, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Button Codes (2000)

    I have attached an excel file that has a code that is messed up. I don't know enough about it to fix it. I hope you can help. I am in the reserves and I need to use this spreadsheet to help keep track of our personnel.

    A word doc explaining it will follow as the excel file is at the limits of the allowable size.

  2. #2
    Lounger
    Join Date
    Jul 2005
    Location
    Benbrook, Texas, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Button Codes (2000)

    Here is the explanation of what I need the spreadsheet to do.

    Thanks for any help you can offer.

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

    Re: Help with Button Codes (2000)

    The data in the second page of your Word doc and in the "Scrub Down Sheet" is obviously a report generated by some kind of database system that stores the data in a table format. It would be much more convenient to export the data from the database system in a table format instead of in a report format. Is that possible?

  4. #4
    Lounger
    Join Date
    Jul 2005
    Location
    Benbrook, Texas, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Button Codes (2000)

    The format that the information in the word document is in, is how I get it. I am not very Office literate. I can work circles around AutoCAD users, but give me an excel or word document, and I am lost.

    I hope you can still help me out!? I know there is a way to transfer text to columns and the column widths can be set manually, but I don't know how to write a code or macro to do it for me.

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

    Re: Help with Button Codes (2000)

    I know that is the format you get it in, but you should ask whoever sends you the data, to send them in table format instead of in report format.

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

    Re: Help with Button Codes (2000)

    Try this version of the code for the command button:

    Private Sub CommandButton1_Click()
    Dim lngMaxRow As Long

    ' Convert text to columns
    Range("A1", Range("A65536").End(xlUp)).TextToColumns _
    Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, xlSkipColumn), Array(31, xlGeneralFormat), _
    Array(37, xlSkipColumn), Array(42, xlGeneralFormat), Array(52, xlSkipColumn), _
    Array(59, xlGeneralFormat), Array(64, xlSkipColumn))

    ' Sort on column B
    Range("A1", Range("A65536").End(xlUp).Offset(0, 2)).Sort _
    Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo

    ' Remove text and blanks
    lngMaxRow = Application.WorksheetFunction.Match(1E+100, Range("B:B"))
    Range((lngMaxRow + 1) & ":" & 65536).Delete

    ' Sort on column A
    Range("A1:C" & lngMaxRow).Sort _
    Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo

    ' Format column B as SSN
    With Range("B1:B" & lngMaxRow)
    .Replace "-", ""
    .NumberFormat = "000-00-0000"
    End With
    End Sub

  7. #7
    Lounger
    Join Date
    Jul 2005
    Location
    Benbrook, Texas, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Button Codes (2000)

    HansV,

    Awesome!! That seems to be working as advertised. I just have a couple of questions about the code for the button and also the sorting sheet.

    #1. As for the button code, I would like it to delete duplicate ssn's. Due to human error, there are occasionally duplicate entries. This will cause manning issues if they are counted twice. We will think we have more people than we actually do and if we get deployed, we will be hurting for people.

    #2 & #3. As for the Sorting sheet, there are two issues. The first one is purely for looks. #2 How can I keep the rows from expanding as they are on the 'Sorting Sheet'? When the raw data is entered on the 'Scrub Down Sheet', some of the rows on the 'Sorting Sheet' expand to different heights of over 150.00. I would like all of the rows to stay at a height of 13.50. #3. This one may be a little more difficult. When the "Click to Sort" button is pushed, the 'Sorting Sheet' does what it is supposed to, except for the rows that don't have information in them. They return a #REF error. How can this be prevented? The reason I don't want that error there is because of the pivot table on the 'Count of Rank' sheet. I use that information to know how many of each rate/rank we have and to double check the numbers in the total block. With the #REF error, the pivot table is adding them to the total.

    Do you still have the original excel sheet or do I need to send another one so you can see exactly what I am talking about?

    Thanks again for your help. You have no idea how much it means to me.

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

    Re: Help with Button Codes (2000)

    See the attached version, it weeds out the duplicates, it has removed text wrapping from the Sorting Sheet, and it recreates the table in Sorting Sheet and refreshed the pivot table in Count of Rank.

  9. #9
    Lounger
    Join Date
    Jul 2005
    Location
    Benbrook, Texas, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Button Codes (2000)

    It seems to work as I need it to. I don' t have the complete list here at home, but I will run it again in the morning when I get to work.

    I have over 500 names so will I have to copy and paste down the commands on the "Sorting Sheet' or will that be automatic?

    Thanks so much for all of your help.

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

    Re: Help with Button Codes (2000)

    The Sorting Sheet will be filled automatically by the code.

  11. #11
    Lounger
    Join Date
    Jul 2005
    Location
    Benbrook, Texas, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Button Codes (2000)

    I overlooked something before. I forgot that the officers are listed by an alpha numeric number in the IRATE column. This is causing an #N/A error on the

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

    Re: Help with Button Codes (2000)

    See attached code, it moves officer data to a separate sheet named Officers (it must already exist), but doesn't do anything with them; it shouldn't be hard to add that.

  13. #13
    Lounger
    Join Date
    Jul 2005
    Location
    Benbrook, Texas, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Button Codes (2000)

    You are a life saver. Thanks for your help...again. You just took something that took around 1-2 hours and made it a 2 minute job.

    How long have you been working with Excel? I will definately recomend this site to co-workers and friends.

    Thanks again.

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

    Re: Help with Button Codes (2000)

    I have used Excel since version 1.00 for the Apple Macintosh, back in 1985 or 1986.

Posting Permissions

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