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

 

[scottishmovies]

Hi Norie,

Hope this helps? I've stripped out all the irrelevant stuff to keep things simple (for me!)



Date 1 Time Date 2 Time Severity 1 Date 3 Severity 2
01/04/2011 09:00 01/04/2011 10:00 A 01/04/2011 A
01/04/2011 09:00 01/04/2011 14:00 B 02/04/2011 B
01/04/2011 09:00 02/04/2011 10:00 C 04/04/2011 C
01/04/2011 09:00 08/04/2011 10:00 D 11/04/2011 D


'==================================================================
'- USERFORM TO UPDATE EXCEL WORKSHEET DATABASE
'- basic info. for table navigation only - will need improvement
'- by Brian Baulsom
'- ** NEXT MESSAGE** added button to FIND a record **
'-----------------------------------------------------------------
'- This example has 3 textboxes named TextBox1, ...etc
'- + 3 buttons - [Next],[Previous],[New]
'- Uses variable 'CurrentRow' to refer to worksheet table
'==================================================================
Public Cancelled As Boolean
Dim Datasheet As Worksheet
Dim CurrentRow As Long
Dim LastRow As Long

'==========================================================
'- INITIALISE FORM WITH FIRST RECORD
'==========================================================

Private Sub UserForm_Initialize()
Set Datasheet = Worksheets("Data")
CurrentRow = ActiveCell.Row
LastRow = Datasheet.Range("A65536").End(xlUp).Row

End Sub

'==========================================================
'- SUBROUTINE : FORM DATA TO WORKSHEET TABLE
'- changes all cells even though data may be the same
'==========================================================

Private Sub UpdateRecord_Click()

' TextBox1 contains a date in format dd/mm/yy
Datasheet.Cells(CurrentRow, 1).Value = TextBox1.Value
' TextBox2 contains a time in format hh:mm

Datasheet.Cells(CurrentRow, 2).Value = TextBox2.Value
' TextBox3 contains a date in format dd/mm/yy
Datasheet.Cells(CurrentRow, 3).Value = TextBox3.Value
' TextBox4 contains a time in format hh:mm
Datasheet.Cells(CurrentRow, 4).Value = TextBox4.Value

' Code here for Severity 1 Levels??
Datasheet.Cells(CurrentRow, 5).Value = Severity1

' TextBox3 contains a date in format dd/mm/yy
Datasheet.Cells(CurrentRow, 6).Value = TextBox5.Value

' Code here for Severity 2 Levels??
Datasheet.Cells(CurrentRow, 7).Value = Severity2

UserForm1.Cancelled = False
Unload UserForm1
End Sub


What I need to do now is use the date from TextBox1 with the time from TextBox2 and compare them with TextBox3 and TextBox4.

If the difference between them is less than 4 hours (on a working day, even across working days) then Code A would be applied. More than 4 and less than 8 would be Code B.
Greater than 8 hours but less than 45 (a working week) then Code C. Finally if more than a working week apply Code D.

The date in TextBox5 is compared to TextBox3 and applies a similar code = Less than 1 day (ie same date) Code A, 2 days=Code B, 3 Days = Code C, and greater than 3 days = Code D.

Is that any better to explain it? There is more to the userform than the above but this is my main problem area.

Thanks again,
Pat

 

[scottishmovies]

Also, although I tried the copy formula method I think I need to explore a different route to get it working as I need to turn off calculations within Excel as it takes ages to save the data to the sheet now (26 items) I assume this is normal but if calc is off then I need to calculation down with VBA to help speed it all up.

Thanks,
Pat

 

[Boller]

To Norie :

 

[scottishmovies]

Am I missing something?