I'm not positive if I understand exactly what you're doing. Here's what I think you're asking: You want the program to go one cell down every time the loop runs---so basicaly increment the cell by a row change of 1
You'll need to have it take a new line with a rowvariable = rowvariable + 1 inside your loop to go to the next cell
You'll have to initialize rowvariable outside your loop & also make sure you have a Dim & whatever variable name you decide to use to increment row.
I'm going to adapt/mention some code I've written for another project into this situation:
I took Dim shtWhatever As Worksheet ' this is where you'd initialize whatever active sheet you want to be manipulating. I'm not sure off the top of my head what would be the best way to go about having it tell what the active sheet's name is & setting shtWhatever to the Active Sheet. Hopefully you can figure this out.
Dim RowCount As Long
Then inside the loop (I use a for loop in my code), you'll want to use something like:
shtWhatever.Cells(RowCount, [whatever column #]) = Acc & "*"
For example, if you want it to put the text in cell B4, your rowcount would take on a value of 4 (since it's adding it to the 4th row), and the column # would be 2 (because B is the second letter of the alphabet, or the second columm)
I'm not positive what's making your thing fail to loop, but I'd guess it's somewhere in your case handling (the if...else...select case part). I'm not sure if hitting cancel would give a "" output if the text is already "Account Number Here"
Hope that helps....sorry I couldn't be more help
[edit]: Don't feel bad, I'm a n00b too! It just takes some learning before it starts making sense--and even then, sometimes I'll get hung up on a detail
QUOTE(Craig_EP82 @ 15 Aug, 2008 - 04:56 AM)
Hi all,
Total n00b when it comes to writing Macros. The problem is I need a my Macro to select a cell, then bring up an input box. The user enters there acc number in to the box and it is displayed in the active cell with an * at the end. The macro then takes a new line and
should loop the input box again until the user enters stop (I'd prefer they just had to click on cancel). Here is my code:
CODE
Sub AddAcc()
' Select first cell for data to be entered.
Sheets("Macro Data").Select
Range("A6").Select
' Request account number.
Dim Acc As String
Do Until Acc = "Stop"
Acc = Application.InputBox(Prompt:="Please input the account number.", Title:="Account Number", Default:="Account Number here")
If Acc = "Account Number here" Then
Exit Sub
Else
Select Case Acc
Case "" & Acc & ""
' Input account number in to Active Cell followed by an "*" and take new line.
ActiveCell.FormulaR1C1 = "" & Acc & "*"
Application.SendKeys ("{ENTER}")
End Select
End If
Loop
End Sub
Please note that after the data is input into the active cell, a new line is taken. However with this loop in place, it just inputs the number without taking a new line and doesn't bring the input box back up either.
Any help appreciated!
| 