Referencing a cell for Do until Loop

[chadbi]

Hello,

I have this do until loop in my script:
'=========================================================
Range(cellchoice).Select
Do Until ActiveCell.Value = Empty
ActiveCell.Offset(0, 29).Value = RandomNumber
ActiveCell.Offset(1, 0).Select
Randomize
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
Loop
'=========================================================

What I would like to do is, collect the offset number from an input box from the user that is running the script to indicate what row the random number will be inserted into. So in the command ActiveCell.Offset(0, 29).Value = RandomNumber, I would like the user to be able to enter the number to take place of the 29. It would be something like, ActiveCell.Offset(0, (definednumber)).Value = RandomNumber.
Where definednumber would be entered during the beginning of the routine via an input box. Does anyone know what I need to do to make this happen, or if I am going about this the wrong way?

 

[Gavona]

Define a variable to hold the value.
Then use the InputBox method (see help)

But would it be better to ensure a cell on the required row was selected before your script runs. Then you could use something like Activecell.row
You might in any event want to use Activecell.row for the default value for the Inputbox.

Gavin

 

[GVF]

Input box is simple unless someone enters garbage. A row number has to be an integer. InputBox returns a string. You need to have an error check (an If statement works) in case someone has fat fingers and enters 1A, or the user cancels the InputBox, etc. You could generate an error that stops the code.

Unless your users are much more savvy than mine, they aren't going to understand "offset" and will think they are entering an absolute row number.

But I'm not clear here. It appears that you...

Define CellChoice somewhere.
Have VBA select the cell defined as CellChoice.
If CellChoice is empty then the loop is bypassed.
If CellChoice is not empty then...
A cell in the same row but 29 columns over gets nothing (because RandomNumber is empty at this point).
Offset to the next cell below the active cell.
Calculate RandomNumber
Then Loop to check if the new activecell is empty.

Did you mean "what column" the random number would be entered into? If that is the case then your error trap must insure that the number the user enters is less than {256-(ActiveCell.Column)-1}. Similar for rows to offset.

You are allowing users to determine the number of the offset? That's like another Random number. Once the user determines what row (or column) they want the random numbers offset to, how do you find them again (the numbers, not the users)?

When coding for users, try to anticipate all the bad things that they might do. Because eventually they will do all of them.

 

[chadbi]

Here's the whole script. It is used to randomize a list of participants based on column with continuous data. I have the cell defined, but I would like to get away from the ActiveCell.Offset(0, 29).Value = RandomNumber and have the number entered by user to replace the 29.
============================================
Private Sub CommandButton1_Click()
'Randomizing Lists

MsgBox ("Before running this script, you will need:" & Chr(13) & _
"The name of a cell that has data throughout the list as a reference point (i.e. Name field)" & Chr(13) & _
"The name of the cell that you want the random numbers to be added to (i.e. column to the right of last column)" & Chr(13))


Dim varFile As Variant
Dim lngCount As Long
Dim RecCount As Long
Dim BadNumber As Long
Dim cellchoice As String
Dim randomcolumn As String

varFile = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Open Sample File", , False)
If TypeName(varFile) = "Boolean" Then 'the user didn't select a file
Exit Sub
End If
Application.ScreenUpdating = False
Workbooks.Open Filename:=varFile

cellchoice = InputBox("Enter the cell of the column that has continious data to end of list")
randomcolumn = InputBox("Enter the cell of the column that you want to store the random numbers in")

Range(cellchoice).Select
Do Until ActiveCell.Value = Empty
ActiveCell.Offset(1, 0).Select
RecCount = RecCount + 1
Loop


'************************************************************
'RANDOMIZING THE SAMPLE *
'************************************************************
Dim intCumber As Integer
Dim Highest As Integer
Dim Lowest As Integer
Highest = 10000
Lowest = 1
Randomize
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest

Range(cellchoice).Select
Do Until ActiveCell.Value = Empty
ActiveCell.Offset(0, 29).Value = RandomNumber
ActiveCell.Offset(1, 0).Select
Randomize
RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
Loop
Cells.Select
Selection.Sort Key1:=Range(randomcolumn), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range(randomcolumn).Select
Do Until ActiveCell.Value = Empty
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(1, 0).Select
Loop

'************************
'SAVE AND CLOSE PROGRAM *
'************************
'after the "&" sign you can add whatever you want to the file name to indicate that it is cleaned
'command below is to save current file taking off the ".xls" (-4 below)
ActiveWorkbook.SaveAs Mid(varFile, 1, Len(varFile) - 4) & "_Randomized.xls"
ActiveWorkbook.Close 'To close the sample file workbook

'*****************
'DISPLAY SUMMARY *
'*****************
MsgBox ("Summary Report" & Chr(13) & _
"Total Number Of Records: " & RecCount & Chr(13) & _
"Total Number Of Bad Records: " & BadNumber & Chr(13) & _
"Total Left Clean Sample: " & RecCount - BadNumber)
ActiveWorkbook.Save
Application.Quit

End Sub

Private Sub CommandButton2_Click()
Application.Quit
End Sub


Private Sub UserForm_Initialize()
'This is to check to make sure there is no other open workbooks
Dim i As Integer
i = Workbooks.Count ' count of open workbooks
If i > 1 Then
MsgBox ("Warning! To run this program please exit out of all excel files open and try again.")
End
End If
End Sub