Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
choose range based on criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
choose range based on criteria
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. "Ivanl" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing a range of cells based on criteria in another range | Excel Worksheet Functions | |||
choose a range to copy paste based on data in the cells | Excel Programming | |||
Copying Range Based On Certain Criteria? Possible looping through the Range | Excel Programming | |||
MIN within range based on criteria | Excel Discussion (Misc queries) | |||
group rows in a range based on criteria from another range (vba) | Excel Programming |