Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling in a form (Office 2000)

    Hi,
    We have a form that we fill in manually every month based on the results of a spreadsheet. This form was created using Excel so I thought instead of filling it in manual, to have the figures fill in automatically from the other worksheet.

    My problem is that the form has "blocks" for each number and separated by a blank column. For example, let's say one result from the worksheet is 623.49, I would have to enter "6" in cell I26, "2" in K26, "3" in M26, the period is always there in col N, "4" in O26 and finally "9" in Q26.

    Manually is no big problem, I just thought I could automate it. I just don't know how I would pick apart the result and put pieces of it in the right places. Can this be done?

    Thanks
    Louise

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

    Re: Filling in a form (Office 2000)

    Will all numbers have the same number of digits, or does that vary?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    Hi Hans,
    It varies. It's never been more than 5 though.

    Thanks
    Louise

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

    Re: Filling in a form (Office 2000)

    And the number of decimal places?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    Always two.
    Louise

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

    Re: Filling in a form (Office 2000)

    Try this:

    Dim strText As String
    strText = Format(Range("A1"), "000.00")
    Range("I26") = Mid(strText, 1, 1)
    Range("K26") = Mid(strText, 2, 1)
    Range("M26") = Mid(strText, 3, 1)
    ' Only if you need to enter the period
    ' Range("N26") = Mid(strText, 4, 1)
    Range("O26") = Mid(strText, 5, 1)
    Range("Q26") = Mid(strText, 6, 1)

    Change Range("A1") to the source of the number you want to fill in.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    Hi Hans,
    Thank you..... Is this a formula I put in each of the cells?
    Louise

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

    Re: Filling in a form (Office 2000)

    No, it is a fragment of Visual Basic code (part of a macro).
    If you want to do this for multiple cells, we'd have to know more about the structure of the worksheets you're working with.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    Hi Hans,
    I have attached the spreadsheet. The form in question is on the bottom of the second tab called: TAX COMPUTATION WORKSHEET. The figures that would be used to fill in the form are from the bottom of the "March 2007" tab coumn H.

    Hope this helps.
    Attached Files Attached Files
    Louise

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    The attached file shows how the Collection Allowance would be done with formulas in the worksheet. I was not positive of what numbers went in the rest of the places.
    Attached Files Attached Files
    Legare Coleman

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filling in a form (Office 2000)

    I was able to get 'some' of the spaces filled in for you. 'tis just a start. I'd encourage you to not go too far with my example; there's bound to be a more elegant solution forthcoming...

    While playing around with the form, I thought the task would be simpler if I were allowed to use other areas of your spreadsheet for intermediate calculations. Would that be okay?
    Attached Files Attached Files
    - Ricky

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

    Re: Filling in a form (Office 2000)

    Here is another attempt. I don't know where the Authorized Credit comes from, so that is left blank.
    Attached Files Attached Files

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    Thank you very much Legare. Will check it out!
    Louise

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    Thank you Ricky. Go ahead and play around with it. I can't promise anything will change here though. They don't do well with changes to their forms.
    I'd love to see what you have in mind.

    Thanks again,
    Louise

  15. #15
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in a form (Office 2000)

    Thank you very much Hans!
    The Authorized Credit is left blank. We don't use that one.

    Thanks again,
    Louise

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
  •