Evalis

The function I am trying to create is supposed to measure employees name vs the name in the spreadsheet for any given day, and compare it to the code in the adjacent column. "L" in this case.

The two problems I have are:
The macro refuses to perform this function for 'each selection'
The function continues to provide a "name?" error

Please let me what this code 'should' be, and where the errors are. I am stumped!

Thanks much,

-John

Attached Images

	Spreadsheet.JPG (225.9 KB, 4 views)
	Code.JPG (132.7 KB, 6 views)

Glaswegian

Hi and welcome to TSF.

I'm not really understanding what you are doing here, partly because I can't see all your code. If you can explain a bit more, that may help - when you say "measure employees name" what do you mean? A #NAME? error usually happens when Excel can't recognise some text in a formula.

Evalis

Thanks for your reply,

And that is odd.. I think you have to doubleclick on the picture as it is in .jpg and reduced format. All the code that I'm using is in picture number two. All I'm doing in the 'name?' column on the spreadsheet is attempting to run the function =sumlate(X5) (or bob in this case).

The function code is

function sumlate(name as string)
x=0
for i = 5 to 70 ***this is meant to scan all rows
for j = 2 to 20 skip 3 *** this is meant to scan all days of the week in the column
if activesheet.cells(i,j) = name AND Cells(i,j+2) = "L" Then
x=x+1
end if
next j
next i
Activecell = x
End function


Hmm.. in retrospect I think I need to change the second 'cells' to 'activesheet.cells' .. but that still doesn't explain the name error..

Evalis

Yeah I just tried changing this and still no dice. Any help?

Glaswegian

Hi again

Sorry for not replying sooner - real life got in the way.

Can you upload a sample spreadsheet, removing any data you consider sensitive, but including all your code? I find it easier to 'play' with the real thing in front of me. And also please try and explain what you are doing and why - as always with VBA code there are many ways to skin a cat.

Evalis

Okay it's uploaded now. I am attempting to Sum all of the cases in which the selected name appears in the spreadsheet along with the designation "L" for late. Note that the designation may be MA (maternity leave) E (Early) or S (Sick). I also intend to calculate the average and total times of lates in the previous column.

So it might look like this:

| Bob |7|L|

I'm certain that I can figure out the formulas for the other functions once the problems with the current ones are isolated. I 'suspect' that I run into an error with the function because it is loading a string, but returning a number. I'm not sure how to trick the computer into returning the number as a string. Of course, it could be something else entirely.

Again, any assistance you can provide would be appreciated.

-John

Attached Files

Attendance_2008.zip (10.0 KB, 3 views)

Glaswegian

Hi John

I think you're over complicating this. Having looked at your sheet, I believe you are simply trying to record the number of L, E or S entries against each person in a month. If that is correct, then a simple COUNTIF formula will do the job - no need to come up with complex functions when Excel already has the built in tools for the job. I've re-attached your workbook - note that I've added a sheet with what I think is a much simpler and easier layout, although I appreciate this may not be suitable for you. Something to remember with Excel - to take the best advantage of all the functions that Excel has to offer, keep your data layout simple and in contiguous columns and rows as far as possible.

Let me know if this is OK.

Attached Files

Attendance_2008 v2.zip (9.2 KB, 2 views)

Evalis

First of all, thank you for putting this together. I may end up using this format, but I would prefer to do so as a last resort, as the previous format goes several months back and is currently in use by other staff members.

Primarily however.. I'm interested in why this code didn't work, as I plan to use macros and create functions in the future, and this is coming accross as a brick wall in logic that I'm sure to encounter in other documents. (I also want to run statistical checks on company reports with excell, and need to ensure the calculations they are performing are accurate, without using a duplicate set of cells).

ShosMeister

One thing that I'm seeing, and it's just a quick look, should you be returning a value? I think the way you do that is by assigning the value you want to return to the function name.

Also, in order for you to define your own functions, they have to be in a module and not attached to the worksheet.

Evalis

I've given this a try and it works.. however I am still stuck with the issue that after entering the function in the cell, it will only return a value -once-

Explaining here.. if I enter bob as being late 2 times and type in the function to make latesum(=bob), the cell will return a value of 2. However, if I later type in bob as being late again, the cell value will still remain 2. I would have to delete the function and retype it in to have it perform the calculation again..

Formulas are typically supposed to update themselves if there are any changes to their parent cells are they not? How do I get this function to do this as well?

ShosMeister

Add "Application.Volatile" as the first line of your function.

Evalis

Awesome! This worked like a charm! Thanks!