# Thread: Filling in a form (Office 2000)

1. ## 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

2. ## Re: Filling in a form (Office 2000)

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

3. ## Re: Filling in a form (Office 2000)

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

Thanks

4. ## Re: Filling in a form (Office 2000)

And the number of decimal places?

Always two.

6. ## 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. ## Re: Filling in a form (Office 2000)

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

8. ## 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. ## 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.

10. ## 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.

11. ## 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?

12. ## 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.

13. ## Re: Filling in a form (Office 2000)

Thank you very much Legare. Will check it out!

14. ## 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,

15. ## 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,

Page 1 of 2 12 Last

#### Posting Permissions

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