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 |
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 |
All times are GMT +1. The time now is 07:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com