find(what:= ?

[discarnet]

OK I'M STILL NEW TO VB

In 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 on

SUB ROTA()

COU = 0

RO = 0

start:

RO = RO + 1

rota = ("e1a") & ro

ent = ("enter name for rota e1a")

ent = ent & RO

E1A = InputBox(ent, "ENTER NAMES")

If E1A = "QUIT" Then Exit Sub

FINDER:

On Error GoTo start

If COU = 3 Then GoTo start

Cells.Find(What:="e1a1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select

ActiveCell = E1A

COU = COU + 1

GoTo FINDER

GoTo START

End Sub

[Blub]

Since no replies yet...

Ok, first some general tips:

1. Please use CODE tags when posting code
   
2. Even 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

   
   

3. 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

[discarnet]

thanks and sorry about the code tags

got it working now after several hours hard work

now checks 5 rotas for 7 days

and looks like this

Sub 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 LAST
If strUserInput = ("NEXT") Then GoTo LOOPROTA
LOOPCELL:
For W = 1 To 200 Step 1
If W >= 200 Then GoTo LOOPDAY
CE = ("E") & W
strRotaTop = CE
Range(strRotaTop).Activate
If ActiveCell = ROTA & R Then GoTo NAME
If ActiveCell = ROTA & ("0") & R Then GoTo NAME
NEX:
Next W
GoTo LOOPROTALINE
NAME:
ActiveCell.Offset(0, 1).Activate
ActiveCell = strUserInput
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
If strUserInput = ("") Then GoTo COLOUR
GoTo NEX
LOOPDAY:
Da = Da + 1
If Da = 8 Then GoTo LOOPROTALINE
If Da = 1 Then Sheets("SUNDAY").Activate
If Da = 2 Then Sheets("MONDAY").Activate
If Da = 3 Then Sheets("TUESDAY").Activate
If Da = 4 Then Sheets("WEDNESDAY").Activate
If Da = 5 Then Sheets("THURSDAY").Activate
If Da = 6 Then Sheets("FRIDAY").Activate
If Da = 7 Then Sheets("SATURDAY").Activate
GoTo LOOPCELL
LOOPROTALINE:
Da = 0
R = R + 1
GoTo RESET
LOOPROTA:
RO = RO + 1
If 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 LAST
R = 1
GoTo RESET
COLOUR:
With Selection.Interior
		.ColorIndex = 6
		.Pattern = xlSolid
	End With
	GoTo NEX
LAST:
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 Sub

thanks for your help