discarnet Posted May 13, 2006 Share Posted May 13, 2006 OK I'M STILL NEW TO VBIn coloum A is a list of rota lines (e1a1, e1a2 ect) the input box ask for the name of the person on each rota line in turn, and then places thier name in the cell next to thier rota line. This works fine for the first input, but how can i change the Cell.find(what:= so that it will search for the cells contaning the value of rota (["e1a"] +ro) so 1st find is for e1a1 second find is for e1a2, e1a3 and so onSUB ROTA()COU = 0RO = 0start:RO = RO + 1rota = ("e1a") & roent = ("enter name for rota e1a")ent = ent & ROE1A = InputBox(ent, "ENTER NAMES")If E1A = "QUIT" Then Exit SubFINDER:On Error GoTo startIf COU = 3 Then GoTo startCells.Find(What:="e1a1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(0, 1).Select ActiveCell = E1ACOU = COU + 1GoTo FINDERGoTo STARTEnd Sub Link to comment Share on other sites More sharing options...
Blub Posted May 17, 2006 Share Posted May 17, 2006 Since no replies yet...Ok, first some general tips: Please use CODE tags when posting codeEven though VBA does not require you, it's a good idea to declare your variables anyway like this Dim X as <type>See web/help for recognized variable types. You can enforce declaring variables by adding the following declaration at the top of the VBA editor page. So before any other code. Option Explicit You say your code it works for the first entry of "e1a1". Did you actually compile your script succesfully and copy it? Anyway, let's assume it actually does work for the first entry for now.The answer to your question is simple: replace your text string "e1a1" in the Cells.Find line with a variable. For example: Cells.Find(What:= ROTA, ... BUT this will not work since you cannot use the same name for the routine ("Sub ROTA()") and a variable. Hence my remarks. If you'd use something else for the variable it would work. For example "SOMETHING" instead of "ROTA".EXCEPT that this code still wouldn't. You need at least to debug your code (use "Compile" menu item in the VBA editor) and also you need to reset your variable "COU" to zero inside each start loop. As you coded it here (if it'd work) ... your program would just count up "COU" to 4 and keep looping questions without acting after that.By the way, by looking at xlPart with the Find function "e1a1" would give more than one match. For example also "e1a11" ... This doesn't seem to be what you want.Anyway, try to improve these things and also use compile to debug. You can probably figure it out and change it accordingly. Good luck Link to comment Share on other sites More sharing options...
discarnet Posted May 18, 2006 Author Share Posted May 18, 2006 thanks and sorry about the code tags got it working now after several hours hard worknow checks 5 rotas for 7 daysand looks like thisSub search() Dim strRotaTop As String Dim strUserInput As String R = 1 RO = 1 INP = ("ENTER NAME FOR ROTA ") PROMPT = vbNewLine & vbNewLine & ("TYPE NEXT TO EXIT TO NEXT ROTA") PROMPT = PROMPT & vbNewLine & vbNewLine PROMPT = PROMPT & ("TYPE QUIT TO EXIT SET UP") ROTA = ("E1A")RESET:strUserInput = InputBox(INP & ROTA & R & PROMPT, "ENTER NAMES")If strUserInput = ("QUIT") Then GoTo LASTIf strUserInput = ("NEXT") Then GoTo LOOPROTALOOPCELL:For W = 1 To 200 Step 1If W >= 200 Then GoTo LOOPDAYCE = ("E") & WstrRotaTop = CERange(strRotaTop).ActivateIf ActiveCell = ROTA & R Then GoTo NAMEIf ActiveCell = ROTA & ("0") & R Then GoTo NAMENEX:Next WGoTo LOOPROTALINENAME:ActiveCell.Offset(0, 1).ActivateActiveCell = strUserInputActiveCell.SelectSelection.Interior.ColorIndex = xlNoneIf strUserInput = ("") Then GoTo COLOURGoTo NEXLOOPDAY:Da = Da + 1If Da = 8 Then GoTo LOOPROTALINEIf Da = 1 Then Sheets("SUNDAY").ActivateIf Da = 2 Then Sheets("MONDAY").ActivateIf Da = 3 Then Sheets("TUESDAY").ActivateIf Da = 4 Then Sheets("WEDNESDAY").ActivateIf Da = 5 Then Sheets("THURSDAY").ActivateIf Da = 6 Then Sheets("FRIDAY").ActivateIf Da = 7 Then Sheets("SATURDAY").ActivateGoTo LOOPCELLLOOPROTALINE:Da = 0R = R + 1GoTo RESETLOOPROTA:RO = RO + 1If RO = 1 Then ROTA = ("E1A")If RO = 2 Then ROTA = ("E2A")If RO = 3 Then ROTA = ("L1A")If RO = 4 Then ROTA = ("L2A")If RO = 5 Then ROTA = ("N1A")If RO = 6 Then GoTo LASTR = 1GoTo RESETCOLOUR:With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With GoTo NEXLAST:ActiveWorkbook.Save MSG = (" SCHEDULE SET UP COMPLETE ") MSG = MSG & vbNewLine & vbNewLine MSG = MSG & ("DON'T FORGET TO CHECK FOR SICKNESS AND ABSENCE") MSG = MSG & vbNewLine & vbNewLine MSG = MSG & (" AND UNALLOCATED ROTAS ") MSG = MSG & vbNewLine & vbNewLine MSG = MSG & (" ON A DAILY BASIS ") K = MsgBox(MSG, vbOKOnly, "DONE")End Subthanks for your help Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now