Replies

Another option w/o VBA:

Paste: =WEEKDAY(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)))=2
into a NAME called isMonday see screen capture below
Then use formula =isMonday (will return True or False)

Assumptions:
1. Formula (=isMonday) is in cell immediately to the right of the one holding the date string in yyyymmdd format.

Note: screen capture below shows formulas B1 the inline test, B2 Named formula in use. Both return True when Monday.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Beethoven,

Just a couple of thoughts.

1. How are you saving the file? I would suggest always saving the file as an .xls file rather than converting it back and forth between .xls & .xlsx (2003 – 2007)

2. Is there something in the data that indicates the status, i.e. new, inactive, etc. If so you could simply apply a conditional format to the entire worksheet to do the row highlighting.

Hope this helps some.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Penny,

This seems like a perfect application for the SubTotals function.
If you can sort the data on the registration numbers then goto Data->Subtotals…
In the dialog box select “Registration NO” in the “At each change in” field then
check what ever heading you have in Col E in the “Add subtotal to:” field and click OK.

Hope this helps.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

MNN,

Sorry, I just can’t resist a chance to write some VBA.

Attached is a copy of your worksheet (including macro).

I had the macro create a sheet called Realigned-Data to be different from yours.
I also noticed that the macro had 1 more row than your REALIGNED DATA sheet.
I counted and it looks like the macro got it right-always a problem with manual manipulations.

Enjoy,

Rg

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

MNN,

I tried using this range: ‘INITIAL DATA’!$C$1:$I$15,’INITIAL DATA’!$K$1:$Q$15,’INITIAL DATA’!$S$1:$Y$15,’INITIAL DATA’!$AA$1:$AG$15 but Excel came back with a “Reference Not Valid” message.

You might want to reconfigure your “Initial Data sheet” to have multiple lines per employee# {one for each facility he/she works at} then creating the pivot would be no problem. This realignment would also make it easier to verify that you didn’t have more/less than 100% allocation for an employee as the percentages would all be right there together w/o scrolling. This is what you have done with the “Realigned Data” tab except you didn’t sort it by Employee# & Facility. Just a thought.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Ed,

I did a little poking around and it looks like you’ve found the best way already.
I tried skipping the leading 0 for hours and it displayed ok but when I tried to =Sum()
a column of numbers they wouldn’t add up.

Depending on the volume of numbers you have to enter you could set up a form and some VBA code to streamline the operation where you’d have separate input areas for hours, minutes, seconds and just use the tab key to move between them then have the VBA code assemble it for entry into the cell. You could even have the form default to the minutes field because I doubt if you have many entries where Hours are a factor.

The form would also be good for error checking because if you enter 0:59:80 into a time field formatted [h]:mm:ss it will convert to 1:00:20.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

In Access you can drag & drop the fields in Design view. I don’t know about doing this in a linked DB.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Howard,

Try this:

Code:

    With ActiveSheet.UsedRange
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
         Application.CutCopyMode = False
    End With

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Cindy,

One way to do it is to open Excel
Press Alt+F11 to get into the VBA Editor
In the immediate window use the workbooks.open command to open the workbook.

Ex: workbooks.open “d:pathfilename.xls”

If the Immediate window isn’t visible use the View menu or press Ctrl+G

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Joel,

Good backup software will allow you to breakup the backup into files of varying sizes to fit different media, e.g. CDs, DVDs, etc. Check out Acronis TrueImage. I’ve been using it for quite a while now and it has all the options you’ll need.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Jim,

Have you edited your shortcut that starts Excel?

If you place the /e switch after the path filename for Excel it will open w/o Book1.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

I am very used to CTRL+F6 to switch between workbooks within a single Excel window.

I just tried opening 2 workbooks by double clicking them in Windows Explorer they both opened in the same window and I had no problem switching using the Ctrl+F6. I checked the “The Windows in Task Bar” setting and it was checked. I unchecked this option and tried the same process and again it worked as above. So unless I am missing something I can’t reproduce the problem on my Win 7 machine with Excel 2003 SP-3.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Gee, that’s a new on on me…Thanks it will certainly change how I code things. I did a little searching and here’s a link to an article that explains how to handle events in forms, etc. Events in Forms

Any day you learn something new is a GOOD day.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Another Itteration…just so you have options.

Code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

       If Right(TextBox1.Value, 4) Like "####" Then
            Exit Sub ' The last 4 characters are all numeric, Exit the after update event.
     Else
           ' The last 4 characters are not all numeric.
           MsgBox ("This field requires the value to be entered in a specific format."), vbCritical, "Invalid format"
           Application.EnableEvents = False
           TextBox1.Value = " "
           TextBox1.SetFocus
           Application.EnableEvents = True
           Cancel = True
     End If
     
End Sub

I tested this on A Win-7 machine running Excel 2003 SP-3 and all worked as you wish.
Note: if you copy the text notice that I changed your TextBox number to 1 from 10.

It’s probably not necessary to say this but just in case you’re new to interface design your error message should state that the last 4 characters need to be numeric…unless of course you work for some 3 lettered agency

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Assuming all your layouts have the same (Excel) page layout you can add the following to the code above to print it.

Code:

 ActiveWindow.SelectedSheets.PrintOut Copies:=1  'Print It!

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs