Copy Excel Formula or rewrite to VBA?

[scottishmovies]

Hi,

I have a spreadsheet with some formulas that work great in calculating the hours between two date/time entries and another which does the same but in number of days rather than hours. They use the networkdays function.

But now that I've added a userform would it be better to copy the formulas using the userform update routine or rewrite into vba?

Formula 1: =VLOOKUP(NETWORKDAYS(A2,F2)*9-((A2-(INT(A2)+(8.5/24)))*24)-(((INT(F2)+(17.5/24)-F2))*24),{0,"A";4,"B";8,"C";45,"D"},2,1)

Formula 2: =LOOKUP(NETWORKDAYS(F2,G2),{1,2,3,4,5,6,7},{"A","B","C","D","E","F","G"})

They both apply a code - A to D or A to G depedning on which one it is.

Is it possible to calculate the time between two dates, within working hours, and do this?
OR if I can't, how do I copy the formula from a previous entry on the sheet within the confines of the userform???

Regards,
Pat

 

[Boller]

I don't follow what you want to do, but does this help ? :-

http://www.mrexcel.com/forum/showthread.php?t=545837

 

[Norie]

What do you actually want to do with the userform?

If you want to use values from the userform you might be able to use Application.WorksheetFunction in the code.

You'd probably need to do some conversion of values to get the right data types.

 

[scottishmovies]

Hi Guys,

Thanks for the quick replies!

Now that I re-read my post, things were perhaps not that clear - to say the least! But the suggestion from Boller covers what I need to do - although as it's in my userform now it seems to take AGES to save to the sheet. The calculating indicator show it doing it SEVEN times! Is this normal?
It's only copying the formula into a specified cell on the active row so why it's calculating anything I don't know.
Any ideas?
Pat

 

[Norie]

Pat

So what is it you want to do?

Can you post the code you've got?

Perhaps someone can spot something that is causing calculation to be so slow .

 

[scottishmovies]

Hi Norie,

The formual on the spreadsheet is as above.
what it does is compare two date & times and calculates a severity level A-D based on

0-4 hours A
>4 <8 B
>8 <45 C
>45 D

Over a working week, ie Mon-Fri, 0830-1730, no weekends.

The formula in the sheet works great but now that I have a userform for data entry I realise that I've created some other problems with it!

I think it could be fixed with soemthign like a Do While but the logic behind it just baffles me and that's why I wondered if the formula could jsut be copied in to the cell via the userform. I've got it to do that but I'm just testing it as I don't know if it'll work for every line in the spreadsheet yet.

HTH explain it??

Do you think a better solution, like a do while, would be better?

Regards,
Pat

 

[Norie]

Pat
It helps a bit, but you still don't seem to be explaining the 'overall' picture, as far as I can see anyway.

Do you have code? If you do it might be an idea to post it to let use see what's happening.

As for some sort of loop, maybe if you mean you want to work with mutliple cells.

One part of it, the severity, could be done with a Select Case structure.

Something like this perhaps.

Select Case hrs
 
Case 0 To 4
   Severity = "A"
 
Case 5 To 8 
   Severity = "B"
 
Case 9 To 45
   Severity = "C"
 
Case >45
   Severity ="D"
 
End Select

 

[scottishmovies]

Hi Norie,

The thing is I don't have any code - other than a standard userform with textboxes and a few combos. They really are standard, enter text, or a date etc then save it to a spreadsheet.

In fact I got the code from another posting on here! by BrianB

http://www.mrexcel.com/forum/showthread.php?t=262104

But it doesnt really do anything other than the code on the posting - yet. Which is where the calculation of the dates comes in, I hope. Using the code above, I hoped to put in some dates, the difference between them used to calculate the severity and then to be output to another cell in the same row. All as part of the userform.

Does that help? Apologies for not explaining it very clearly.

Pat

 

[Norie]

Pat

No problem, sorry for asking so many questions.

I'm afraid though I'm perhaps more confused - you don't have any code?

There's a fair bit of code in the thread you posted a link too.

 

[scottishmovies]

Sorry Norie, yes I do have code, basically as it appears in that thread.

I think I need more coffee

Pat