Articles

VBA Referencing a cell for a do until loop

August 21, 2009 at 12:29:18
Specs: Windows XP PRO SP2, P4 - 2.66GHz / 2 Gigs DDR

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?


See More: VBA Referencing a cell for a do until loop

Report •


#1
August 23, 2009 at 16:17:00

Does anyone know what I need to do to make this happen
Dim definednumber As Integer
definednumber = InputBox("What?") 
. . . Or something along those lines, I presume.

Report •

#2
August 24, 2009 at 06:29:24

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


Report •

Related Solutions


Ask Question