Can a macro with input boxes do this?

Posted by Tim Johnson on December 14, 2001 7:40 PM

I need help with a macro and some inputboxes. Below is a short example of what I am trying to accomplish:

accounting expense 26.00
legal expense 26.19
miscellaneous 26.38
travel 26.57
utilities 26.76

Is there a way to create a macro that has input boxes for me to type in the expense accounts (1 to 1 million or so) and have a button on the input box to tell the macro that I am through entering expenses? Then I want the macro to sort the expenses alphabetically and enter them in a cell that I choose, probably through another input box. I also want it to create an expense code, also in a cell that I choose through yet another input box, and fill the series down. The trick to that is that the expense codes must start with the same # (and I also would like to be able to tell it which # to start with by another input box) and the .## must be 2 digits and be evenly spaced between 01 and 78.
If anyone can help me (I know this is a huge pain in the butt) but I WOULD REALLY APPRECIATE IT!!!!!!!!!! It would really help me a lot for my job..

THANK YOU SO MUCH IN ADVANCE!!!!!!!!!!!!!!!!

Posted by Jacob on December 14, 2001 8:14 PM

Maybe this can get you started.
Sub Test()

Dim NewValue
X = 1
A:
NewValue = InputBox("Enter A Value, Enter done If Complete")
If NewValue = "done" Then GoTo Z:
Range("A" & X).Select
ActiveCell.FormulaR1C1 = NewValue
X = X + 1
GoTo A:
Z:
End Sub

Posted by Tim Johnon on December 14, 2001 8:55 PM

That helps a bit, but...

Thanks!!! That got me started, but that puts my list in cell A1 and down. How can I either tell the macro where to start the list or have it start the list in the active cell when I run the macro? THANKS AGAIN IN ADVANCE!!!!!!!!

Posted by Jacob on December 14, 2001 9:02 PM

Re: That helps a bit, but...

Try This : Sub Test() : Dim NewValue
Dim StartRow As Integer
StartRow = InputBox("What Row Do You Want To Start In?")
X = StartRow

Posted by Tim Johnson on December 14, 2001 9:17 PM

Can we tell it what cell to start in instead of the row?

Can we tell it what cell to start in instead of the row? Try This

Posted by Jacob on December 15, 2001 6:53 PM

Yes

You sure can.

Try this

Sub Test()
Dim MyRange
Dim NewValue

MyRange = InputBox("What Range Do You Want To Start In? i.e. c5 or d11 etc")

Range(MyRange).Select
A:
NewValue = InputBox("Enter A Value, Enter done If Complete")
If NewValue = "done" Then GoTo Z
ActiveCell.FormulaR1C1 = NewValue
ActiveCell.Offset(1, 0).Select
GoTo A
Z:
End Sub

Hope this helps

Jacob