I am in way over my head Need help

[vicC]

I am an nurse with some knowage of excel, but I love working with this Stuff.
I think that using an userform might be what I need.

I have tried to upload my file but it is to big.

I need help adding and editing an table that I made in excel 2007.
table has index column 1, date column 2

It is for staffing meetings that happen two times an day.
need to be able to Add and edit, by date .

field names

PCU census PCU staff
ICU census ICU staff
Date

Any Ideas would be great!
Thanks.

[Aussiebear]

Just post a sample workbook of your workbook so we can see what you are working on. Failing that, have you tried compressing your file?

[vicC]

OK I have some code here and some examples
of worksheets.

The interface sheet is what I think I need to make into user forms.

I tried to explain in workbook ask anything you need.
thanks again.

[Bob Phillips]

Oh come on, that does even start to tell us what you need.

Talk us through what you do/need to do to get the information entered and updated correctly. We haven't a hope of second-guessing our thoughts on that huge table.

[vicC]

//sites.google.com/site/myhelpsiteproject/files

This is a copy of the working file.

This file helps keep track of staffing variances. Too few nurses for the number of patients.

This is the interface.
There are to staffing meetings everyday one in the morning and one at night. The number of patients is added. " The census" than the number of staff for each unit. After that if the difference is a negative number we have to enter an action of how we corrected it.

I need opinions: is the interface the best way or to use user form
if user form is the best way, I have no idea how to make it work. Second opinion : is the "DBtable" ok to hold data?
Opinion 3: the way I copied data into DBtable ok?
I am using 2007 but need it to work also in 2003 which I know is not working very well.

The staffmatrix page is cut paste page. Copy of matrix from our Staffing program.

Sup census just page to get total census for shift to cut and paste into different report that they type up every shift.

Also need reports to print out the % of variances for a month, year, and quarter. That include date of variance and unit.

I know I have not given you what you need to help me. But maybe after you see what I have made you can ask questions to help me?

Thanks

[Bob Phillips]

It is hard to say as you are describing what we see, not what you want to do.

I looked at what you made and after scrolling right for hours on end, I gave up looking. Words help me more than guessing your intentions.

However, I would venture that you don't need a form. Just enter the data on the staffing sheet. You can then either build a report using formulae, or else use VBA to build it.

[Aussiebear]

Vic, This is more like a project rather than a single issue. Having said that why not break it down into a series of issues. Firstly, write down what you think the main issue is.

If as I'm guessing you are questioning the layout then you need to consider, who uses the workbook and how many of the sheets they need to be able to access and more imortantly whether this project might be more suited to a true database rather than Excel. When you are clear in your mind as to what you want, post back to here.

At this stage I'm in full agreement with XLD. The current layout is confusing since we don't yet follow what your doing.

[vicC]

You are right I have no Idea. I am making list.

It is very hard to get out!


thank you for you time, I am getting my pen out.

will post back.

[Aussiebear]

We look forward to your next post

[Aussiebear]

A couple of other things for your consideration, while you are thinking about what it is that you are chasing. I've been looking at your code and I'm somewhat confused. Can you tell me what the following code is meant to be doing?

[vba]
Sub copyintodatabase()

Application.ScreenUpdating = False

Sheets("staffDB").Select
Application.ScreenUpdating = False
Range("C1").Select
Application.ScreenUpdating = False
Dim vicday As Variant

Application.ScreenUpdating = False

vicday = Application.Match(Range("C1"), Range("A11:A2910"), 0) 'finding index date
Dim vicrow As Variant
vicrow = vicday + 10
Worksheets("staffDB").Range("C4:J4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date

Application.ScreenUpdating = False
'PCU DATA
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "C"), Cells(.Row, "J")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "DT"), Cells(.Row, "EA")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'SURGICAL DATA
Worksheets("staffDB").Range("K4:R4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "N"), Cells(.Row, "U")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "EE"), Cells(.Row, "EL")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'REHAB DATA
Worksheets("staffDB").Range("S4:Z4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "Y"), Cells(.Row, "AF")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "EP"), Cells(.Row, "EW")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'MEDICAL DATA
Worksheets("staffDB").Range("AA4:AH4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "AJ"), Cells(.Row, "AQ")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "FA"), Cells(.Row, "FH")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'CVCU DATA
Worksheets("staffDB").Range("AI4:AP4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "AU"), Cells(.Row, "BB")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "FL"), Cells(.Row, "FS")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'ICU DATA
Worksheets("staffDB").Range("AQ4:AX4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "BF"), Cells(.Row, "BM")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "FW"), Cells(.Row, "GD")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'MCU DATA
Worksheets("staffDB").Range("AY4:BF4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "BQ"), Cells(.Row, "BX")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "GH"), Cells(.Row, "GO")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'PEDI DATA
Worksheets("staffDB").Range("BG4:BN4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "CB"), Cells(.Row, "CI")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "GS"), Cells(.Row, "GZ")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'L&D DATA
Worksheets("staffDB").Range("BO4:BV4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "CM"), Cells(.Row, "CT")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "HD"), Cells(.Row, "HK")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'NURSERY DATA
Worksheets("staffDB").Range("BW4:CD4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "CX"), Cells(.Row, "DE")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "HO"), Cells(.Row, "HV")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If
'CDU DATA
Worksheets("staffDB").Range("CE4:CL4").Copy 'coping the data
Rows(vicrow).Select 'selecting row of date
If Worksheets("staffDB").Range("E2") = True Then 'local test for time of meeting

With ActiveCell
Range(Cells(.Row, "DI"), Cells(.Row, "DP")).Select ' selecting cells to place 0300 meeting in
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ' paste the data

'Range(Cells(.Row, "c"), Cells(.Row, "D")).Select
End With

Else
If Worksheets("staffDB").Range("F2") = True Then 'local test for meeting time
With ActiveCell
Range(Cells(.Row, "HZ"), Cells(.Row, "IG")).Select 'selecting cells for 1500 meeting
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False 'paste the data

End With
End If

End If

Application.ScreenUpdating = False

Sheets("interface").Select
Range("D818").Select
Selection.ClearContents
Range("F8:F18").Select
Selection.ClearContents
'Range("H8:H18").Select
'Selection.ClearContents
'Range("I8:I18").Select
'Selection.ClearContents
Range("K8:N18").Select
Selection.ClearContents

Range("A1").Select
Application.ScreenUpdating = True
Application.Save
'Application.Quit

End Sub
[/vba]
My first question is does this code actually work? If it does then great but, I would suggest that it can be improved in a number of ways.

Firstly there are a number lines of code that can be removed or rewritten to shorten the total line count.

For example: In the first 8 lines of code you've got 4 lines of "Application.ScreenUpdating = False". Normally you only require one to turn off Screen Updating and one to turn it on again at the end of the sub as in the following example
[vba]
Sub CopyIntoDatabase()
Application.ScreenUpdating = False
'Relevant code here
Application.ScreenUpdating = True
End Sub
[/vba]
The 2nd example I'd like to bring to your attention is, Declaring the Variables. I had to look through your code to find the Dim statements when I was expecting to find them at the start of the Code. Its a good habit to get into, as well as using the term Option Explicit at the top of your code. Option explicit forces the user to declare all variables and as a result cuts down on any errors resulting from misuse of variables, particularily typo's.

The 3rd example for you to consider is as follows, your 3rd line of code is
[vba]Sheets("staffDB").Select[/vba] Selecting cells, ranges, sheets etc slows down code execution dramaticily, you could try rewriting it as such
[vba]With Sheets("staffDb")[/vba]
And a 4th example to consider is, where lines of code say
[vba]Sheets("interface").Select
Range("D818").Select
Selection.ClearContents
Range("F8:F18").Select
Selection.ClearContents
[/vba] this can be easily shortened into the following
[vba]
With Sheets("interface")
Range("D818, F8:F18").ClearContents
End With
[/vba]
With that in mind I'm going to have a crack at tidying up some of the code for you

[Bob Phillips]

[vba]
With Sheets("interface")
Range("D818, F8:F18").ClearContents
End With
[/vba]

[vba]
With Sheets("interface")
.Range("D818, F8:F18").ClearContents
End With
[/vba]

[Aussiebear]

THis workbook knows how to tie up resources. The more I look at it, the more I'm convinced that using excel alone is not going to be of any use. The formatting alone is massive weightage for the workbook. The code can be tidied up, but then it needs to be useable, and this is where we come unstuck.

Vic, if you are still dead keen on this, then you seriously need to sit down with professional programmers. There are some people here who may be able to assist you for a fee. Best of luck if you wish to continue

[Aussiebear]

[vba]
With Sheets("interface")
.Range("D818, F8:F18").ClearContents
End With
[/vba]

very good point Bob, if you'll pardon my pun

[OBP]

Looking at this Workbook and it's application I think it would be better in Access, especially if it is to be shared by other users.

[Bob Phillips]

Whislt I am struggling to see what he wants to do, I do get a sense of what the data is for, and Access seems a terrible option to me, far better to leave it in Excel.

[OBP]

XLD, not when I see things like this
"The staffmatrix page is cut paste page. Copy of matrix from our Staffing program.

Sup census just page to get total census for shift to cut and paste into different report that they type up every shift."

[vicC]

Thanks you guys for all the imput I am trying to clean up the code. and learn more. I do not think they will pay.Program just makes it easier for us.

[vicC]

The code breaks down data and copies it into table under nursing unit ie: ICU, PCU. Also each unit has AM and PM meetings to save data from I know should have them beside each other. Just learning. I need the "interface to reset for lack of better word back to 0 for each meeting." This are very smart people that want to mess with everything, at the time I did not known how to make userform work in excel and have not made it work yet.

Would like to reinvent the wheel and make userform out of interface.

thanks for any input and everything you have said is taken very greatfully!

[SamT]

Vic, you are a VlookUp King!

Next time, remember;

Input >> Database
Database + Static Tables >> Output Reports

And never mix Db or table with reports on the same sheet.

How urgent is this project?

SamT

[SamT]

There is a chance, how good idunno, that this organic growth will work if all Merged Cells are converted to Horizontal Align = Center Across Selection.