choose range based on criteria

[Ivanl]

Hello,

I am trying to create a macro that would choose a random name based on the
first letter of that name if cell I8 has a letter in it. (e.g. if I8 has A
in it, the random name generator would only randomly choose from the names
starting with A). I have the random part working (I have cells 1 to 1238
chosen for the input) but do not know how to add in the criteria if a
specific letter is chosen from I8.

Here is the code I was working on:

Sub RandomLots()



Dim rndnum As Integer
Dim up As Integer
Dim low As Integer
Dim outp As String
Dim st As String

If Cells(8, 6) <> "ANY" Then st = Cells(8, 6).Value = ""
Else
Cells(8, 6).Value = ""


Range("StartW").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria")
Else

Randomize

rndnum = Int((1238 - 1 + 1) * Rnd + 1)
Sheets("Lots").Range("A" & rndnum).Copy Destination:=Sheets("Lots").Cells(3,
9)

End Sub

Thanks,

Ivan

 

[Ivanl]

I have made progress:
this macro atuo filters the selection

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/13/2009

Dim nm As String
'number for the vlookup
Dim org As String
'number for the vlookup

Worksheets("Longcri").Activate
nm = Range("a1").Offset(rowOffset:=Range("Letter") - 1, columnOffset:=0).Value

org = Range("b1").Offset(rowOffset:=Range("Orig") - 1, columnOffset:=0).Value

Sheets("Longnames").Select
Range("A1:c1238").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=org
Selection.AutoFilter Field:=1, Criteria1:=nm & "*"

HOW DO I CHANGE THIS SUB SO THAT IT WILL ONLY CHOOSE FROM THE VISIBLE CELLS:

Randomize

rndnum = Int((1238 - 1 + 1) * Rnd + 1)
Sheets("Longnames").Range("A" & rndnum).Copy
Destination:=Sheets("Lots").Cells(4, 1)


End Sub

Currently it chooses a random cell from the whole list (i.e. up to A1238).

THANKS.