Do Loop with InputBox and ActiveCell Ref HELP!!

[brilliantblk]

So far, I have a input box that asks the user how many groups he has. I have my spreadsheet setup with formulas such that, for example, if the user inputs 5 groups, five columns will populate with the header Group 1, Group 2, Group 3, etc. Hidden above these cells are the numbers 1-25 (max 25 groups), that is used in an IF statement that if that cell is less than the number of groups the user entered, write below the cell "Group" and the number. Under each of the headers is a cell to enter the number of employees. I am trying to incorporate that into my macro and here is what I have so far. Needless to say, I am VERY VERY VERY new to VBA and do not know the extent of the commands that I can use; this made sense in my head but apparently doesn't work too well:

Do

If ActiveCell.Value < SumOfGroup And ActiveCell.Value <> "" Then Exit Do
'ActiveCell.Offset(2, 0).Activate
Range(ActiveCell.Offset(2, 0)).Value = InputBox("Enter the number of employees in Group " & ActiveCell.Value)
ActiveCell.Offset(-2, 1).Activate

Loop

So in my mind, first it selects the hidden active cell containing 1. The If statement is proven true. It then activates the cell to enter the number of employees into Group 1. User inputs the value, and the loop continues to 2 and repeats until the value is greater than the number of groups.

[CodeNinja]

Brilliantblk,

This worked for me:

[vba]Sub test()
Dim iSumOfGroup As Integer

iSumOfGroup = Application.InputBox("Enter number of groups", Type:=1)

If iSumOfGroup > 25 Then i = 25
ThisWorkbook.Sheets(1).Activate
Sheet1.Cells(1, 1).Select
Do
If ActiveCell.Value > iSumOfGroup Or ActiveCell.Value = "" Then Exit Do
ActiveCell.Offset(2, 0).Activate
ActiveCell.Value = InputBox("Enter the number of employees in group " & iSumOfGroup)
ActiveCell.Offset(-2, 1).Activate
Loop

End Sub
[/vba]

Note: I used application.inputbox with type:=1 so you can prevent the user from entering "Five" instead of 5.

[patel]

brilliantblk

what is SumOfGroup ? it's not defined

Range(ActiveCell.Offset(2, 0)).Value is wrong

ActiveCell.Offset(2, 0).Value is correct

you don't need ActiveCell.Offset(-2, 1).Activate

attach please your file to understand better

[brilliantblk]

Attached is the excel file.

SumOfGroups is a named cell within the file.

[CodeNinja]

Try uncommenting the activecell.offset(2,0).activate and using activecell.value = ....

[vba] If ActiveCell.Value < SumOfGroup And ActiveCell.Value <> "" Then Exit Do
ActiveCell.Offset(2, 0).Activate
ActiveCell.Value = InputBox("Enter the number of employees in Group " & ActiveCell.Value)
ActiveCell.Offset(-2, 1).Activate[/vba]

[brilliantblk]

Works great, thank you!!

[CodeNinja]

Brilliantblk,
Glad this worked for you.

If the thread is resolved, please mark it that way. You can do so in the thread tools dragdown at the top of this page.