Jump to content

find(what:= ?


discarnet
 Share

Recommended Posts

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

Link to comment
Share on other sites


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

Link to comment
Share on other sites

thanks and sorry about the code tags :blushing:

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

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.


×
×
  • Create New...