Compile error: For without Next

[NewbieVBie]

i am consistantly getting a Compile error:
saying:

For Without Next.
what does this mean and how can i resolve it?
(the debug highlights the End Sub line of the code)

 

[NewbieVBie]

what exactly does this code do? i need it to find the holiday entitlement, holiday remaining, holiday taken from a series columns for each user and then i need to it change the values it finds by the number the user enters in a textbox
will this do that?
and if not what will?

 

[Andrew Poulsom]

If you are patient and answer my questions we will get the code to work.

Tell me the cell references for "accounts" and the relevant holiday entitlement, holiday remaining and holiday taken.

 

[NewbieVBie]

ACCOUNTS, Entitlement, Days to Date, Remaining
A 21 0 21
B 21 0 21
C 21 0 21
D 21 0 21
E 21 0 21
F 21 0 21

that is the format of the columns A-F being the names of the people working in Acounts
the cell references:
'accounts' C1
the following words are the column headers for D,E,F repectively
This message was edited by NewbieVBie on 2003-02-21 09:05

 

[Andrew Poulsom]

I would have thought that your text box contained the name of the person requesting the holiday. So why is DAYREQUESTTB matching with "accounts"? That's the department isn't it?

 

[NewbieVBie]

yes its the department, thats why i cant understand how it could work, the names of the users are all listed in columns below the name of the department they work in, this is necessary for the comboboxes that the department and names are in to work properly.

 

[Andrew Poulsom]

OK, so where is the name being entered in you UserForm? Now we've found the department it should be easy to locate the person.

 

[NewbieVBie]

the name is in cell C2,

as i have multiple columns with names in them the code assumes that the names go from C1-100, G1-100, K1-100 etc (every fourth column from C to BC)

 

[Andrew Poulsom]

The first name is in C2. But we need to look down column C to find the right name. So if department is in TextBox DAYREQUESTTB on your UserForm, what is the name of the TextBox (or other control) containing the person's name?

 

[NewbieVBie]

the department is in DEPARTMENTCB, the name in NAMECB, so i need to change the variable?
also would this code go in the combobox with the department list in it?
This message was edited by NewbieVBie on 2003-02-21 09:50

 

[Andrew Poulsom]

The code is probably best on a button.

First you need to find the column containing the Department:

Dim c As Range
Dim i As Integer
For Each c in Range("C1:IV1")
   If c.Value = DEPARTMENTCB.Value Then 
      i = c.Column 
      Exit For 
   End If
Next c

Then we need to find the person in that column:

Dim j As Integer
For Each c in Columns(i)
   If c.Value = NAMECB.Value Then 
      j = c.Row
      Exit For 
   End If
Next c

Now i contains the column number and j the row number of the cell containing that person.

So:

DaysRemaining = Cells(j, i).Offset(0,3).Value

See if you can write the rest of the code based on that.