VBA array Function help

**robwebber** · 03-10-2015, 04:06 PM

Hi All,

I'm struggling with a particular issue that I dont seem to be able to resolve.

in the attached workbook there is a simple time sheet arrangement whereby there is a name, which is arranged in a column, a date row across the top, and hours are inserted against each day worked.

there is a rather tricky chargeout structure which I'm trying to accommodate in a function. I've managed to do a formula but it is very large, and also needs a second row at the minute because Ive not managed to convert it into an array.

the target is to have one cell contain a total charge out value, in a column next to the column containing the name, without having to have subtotals etc

I thought I'd just about cracked the code for a single calculation (which would need a subtotal) but the function just returns a value error. i'm new to VBA and really struggling to see where I've gone wrong, and where to start to turn this into an array using the date and hours as arrays, and the name as a fixed value.

any help would be very gratefully received

regards

**TMS** · 03-10-2015, 04:41 PM

OK, this gives an answer, not sure if it's the right answer.

Please Login or Register  to view this content.

Regards, TMS

**robwebber** · 03-10-2015, 05:43 PM

Thanks TMS, its not giving the right answer, but you dont seem to have changed my code that much so maybe its something I've caused. - the answer that the code should be delivering is in row 11 in the file I uploded, Would you advise what the DBl1 is for in the code? it doesnt seem to change anything whether it is there or not.

**TMS** · 03-10-2015, 06:56 PM

No, no intentional changes to your code. The only "big" change was to the check for a holiday date. You probably need to do some more exhaustive testing

Right, this version works and gives the right answers for the sample data.

Please Login or Register  to view this content.

Regards, TMS

**robwebber** · 03-11-2015, 06:13 AM

Thank you for that.

it didnt quite work when I tested the calc on days which were holidays, but changing the statement " IF vbhol <>"" Then" TO "IF vbhol = 0 Then" did the trick. I assume the "on error goto 0" statement means that if the result (of the check against the holiday days) is an error then return zero?

I'm very grateful for the help you have provided. I've a bit of reading up to do on the code used to deal with the holidays.

If you fancy a challenge, would you know how to alter the code to make it work with an array of dates and times worked? so that the total could (for example) go in B10, and then I could do away with the total price for the day line altogether.

thanks again !

**robwebber** · 03-11-2015, 06:22 AM

OOps,

Spoke too soon, holidays work but all other days now use OT2 rate too,

**TMS** · 03-11-2015, 07:31 AM

I assume the "on error goto 0" statement means that if the result (of the check against the holiday days) is an error then return zero?

No. It switches off the error handling. When you say "On Error Resume Next" you are using one of the resume options which is simply to carry on with the next statement as if the error didn't happen. You can then test the error condition, if you wish, and switch error trapping off, again, if you wish.

Problem was with the holiday lookup. Fixed.

Please Login or Register  to view this content.

If you fancy a challenge, would you know how to alter the code to make it work with an array of dates and times worked? so that the total could (for example) go in B10, and then I could do away with the total price for the day line altogether.

No, not me, I'm afraid. Could be just another function that loops through the array passing name, date and hours to the existing function. But, for simplicity, I think you'd need to repeat the name for each date/hours pair.

Regards, TMS

**robwebber** · 03-11-2015, 08:23 AM

Thanks very much..... Genius. all working now.

(Thanks for the array advice too.)

**TMS** · 03-11-2015, 10:25 AM

You're welcome. Thanks for the rep.

And here is the function you asked for:

Please Login or Register  to view this content.

Not sure how it is an improvement on what you already have but, what do I know do?

Regards, TMS

**robwebber** · 03-11-2015, 04:31 PM

Thats almost there,

I wanted to use it in the horizontal plain rather than vertical, and with a single reference to the name of the employee but that gives me a good starting point.

without a price per day row, i can have one row for each employee, which will look pretty much like a time sheet but the total column reflects the total charge out price for all time spent rather than total hours. total hours giving me the problem of categorising the hourly rates for all of the differing potential combinations of day and working time etc.

now that its working i've realised that if I add to the ranges (add new employee's or holidays) after the sheet has initially been set up, I have a refresh problem. hitting recalculate doesn't seem to incorporate the new data into the calculation only entering the worksheet cell containing the individual formula and hitting enter refreshes it fully. I don't know how to dealt with that!

**TMS** · 03-11-2015, 04:45 PM

Please Login or Register  to view this content.

Because the function refers to cell/range references, I would have expected it to adjust and recalculate automatically. However, you could try adding

Please Login or Register  to view this content.

as the first line of code in the function. That should cause it to recalculate when Excel feels a recalculation is in order.

Regards, TMS

**durgaprasadzone** · 03-11-2015, 04:54 PM

Thanks. I was searching the same information since many months. Thanks for your hard work.

SEO Freelancer
Camino Inca Peru
Camino Inca Machupicchu

**robwebber** · 03-11-2015, 04:54 PM

This minor alteration allows me to use a single cell for the name and an array for date and hours.

not fully tested but it gives me an answer and it matches that which i would have expected.

your replies have been a great help. I'm pretty sure I'd have been here next year trying to work this one out!!

Option Explicit

Function MyPayTotals(ByRef rNames As String, _
ByRef rDates As Range, _
ByRef rHours As Range)

Dim dTotal As Double
Dim i As Long

For i = 1 To rDates.Cells.Count
dTotal = dTotal + MyPay(rNames, _
rDates(i), _
rHours(i))
Next 'i

MyPayTotals = dTotal

End Function

**TMS** · 03-11-2015, 05:19 PM

@robwebber: and is that different to the code I posted?

**robwebber** · 03-11-2015, 05:48 PM

Trevor You are a true Guru.

Target very much achieved in Full. - I'm very grateful!

**TMS** · 03-11-2015, 06:26 PM

Thank you

Please note that you should add Code Tags to post #13.

Regards, TMS

VBA array Function help

LinkBack

Thread Tools

Rate This Thread

Display

VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Re: VBA array Function help

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a function

[SOLVED] IF Function referencing IsNumber, Match, Left function on separate sheets

[SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result

Excel NORM.DIST function gives results greater than 1 with probability mass function. How

Inserting an INDEX,MATCH function into a HYPERLINK function instead of cell reference

Bookmarks

Bookmarks

Posting Permissions