Posts by Keithcornett

Re: Max Function By Account Number

That works perfectly! Thank you so much for the help...

I have a file that has multiple account numbers. I'm trying to get the largest value of the code column (see attached) to align with the account number that has the patient count of "1". Is there a way to use the Max function to do this? Thanks for any help on this.

Re: Sum Visible Cells Only

Richard, you are awesome! That works perfectly and thanks so much!

I have various spreadsheets that have rows hidden with data that I do not want included in the totals. Because of multiple header lines in the worksheets, the use of filters is not practical. Is there a formula that I can use that will sum only the visible cells? Thanks in advance for any help on this.

Hi,

I have a worksheet that imports data from a text file which vary's in size each time from 100 to 1000 rows. I want to subtotal only rows with data. The current macro I use has a set range up to 1000 rows which creates the grand total at the bottom of the page and requires deletion of the blank rows.

Is there a way for VBA to check for non-blank rows instead of the pre-defined range and set the sub-total function to only calc those rows?

Thanks for any help on this.

KC

Re: Inserting Blank Rows Within Subtotalled Data

Wow, that worked perfectly! Thank you so much...

Re: Inserting Blank Rows Within Subtotalled Data

Absolutely! The first spreadsheet shows what the data currently looks like. The second shows the view after inserting the blank rows. Each time the medical record number changes (column A), I need to insert a blank row.

Hi,

I've got a spreadsheet that I prepare for a group that has patient data subtotalled by physicians. The problem is that the group uses this data for data entry and wants a blank row between each different patient name within the physicians subtotals and I have no clue how to get there. Any chance there might be some VBA code that could accomplish this?

I appreciate any help on this one.

Keith

Re: Open Files With Number Extensions

I just want to open the one other file which will be imported into that directory daily.

It could be 010707.xls, 021607.xls or any other date.

I guess I just want to avoid having to re-map the macro each time and my VBA skills are pretty abysmal at trying to come up with a variable which would say "open any file with a numeric name".

Re: Open Files With Number Extensions

Actually they are date driven but will be at least a month behind and not always in sequence when we receive the files so I need some sort of variable that will open any excel file not already open within that directory.

(thanks for the quick reply)

Hi,

I have a workbook that will need data from another which will be located in the same directory. I need to open this workbook however the filename will be imported daily and be different each time (i.e. 010707.xls, 010807.xls, etc.) The only two files residing in the directory will be the active file and the one that needs to be opened.

Is there a way to assign a variable for the 010707? Currently I have the following and need to manually modify the file name:

Workbooks.Open Filename:="C:\My Documents\template\010707.xls"

Thanks for any help on this,

Keith

Re: Automatically Run Macro Before Saving

Thanks so much dr! It worked perfectly.

I know that there is a 'before save' event that I can use to run a macro whenever I save or save as the file. I've tried the following code but it doesn't appear to work and my VB skills are pretty abysmal. I've tried looking it up using a search (that's where I got this code from) but haven't had much luck getting it to work.Here's the code:

Private Sub Workbook_ThisWorkbook(ByVal SaveAsUI As Boolean, cancel As Boolean)
Application.Run "'Data Entry2.xls'!CopyData"
End Sub

Thanks for any help with this.

Not sure if anyone else has this problem but my macro security settings are all set to 'medium' however if I try to open a file via Explorer, the macro's will not run and a message comes up saying the security is set too high even though when checking the file, they are still set on meduim.

When opening the file with Excel, the enable macros option comes up and then they will run.

I have a file with a physician name and title in the cell as follows:

"James A. Jones, M.D."

I need a formula to reverse the last name, drop the title "M.D.", get rid of any additional periods after the middle initial and put it in upper case:

"JONES, JAMES A"

There are some names without middle initials also so this may present an additional problem.

Thanks for any help on this.

Re: Opening a file from any active directory

Yes, both files are in the same directory. I've tried moving them into different directories all with the same result, error message 424 'object required'.

Re: Opening a file from any active directory

Hi Tony,

It was a saved file. Both the excel file and the text file are on my desktop.

Keith

Re: Opening a file from any active directory

Hi Tony,

Sorry it took so long to get back on this, I was out of town for a while.

I tried the following code in a blank workbook but still got the 424 runtime error code. Both the text file and the excel workbook are residing on my desktop.

Here's what I had:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/7/2006 by keith
'
Workbook.OpenText Filename:=ThisWorkbook.Path & "/p51.txt"
'
End Sub

Thanks for any help on this.

Re: Opening a file from any active directory

Run time error '424': Object required

Re: Opening a file from any active directory

Here's what I had:

ChDir "C:\My documents-2\Monthend Supplemental Reports\Deficiency Reports\current"
    Workbooks.OpenText Filename:= _
        "C:\My documents-2\Monthend Supplemental Reports\Deficiency Reports\current\C37.txt", _
        Origin:=437, StartRow:=39, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(12, 1), Array(16, 1), Array(28, 1), Array(37, 1), Array(43, 1), Array(48 _
        , 1), Array(75, 1), Array(82, 1), Array(88, 1), Array(96, 1), Array(102, 1), Array(110, 1), _
        Array(128, 1), Array(130, 1), Array(150, 1), Array(156, 1), Array(161, 1), Array(190, 1)) _
        , TrailingMinusNumbers:=True

When I replaced the code, this is how it was set up:

Workbook.OpenText Filename:=ThisWorkbook.Path & "\c37.txt", _ 
        Origin:=437, StartRow:=39, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(12, 1), Array(16, 1), Array(28, 1), Array(37, 1), Array(43, 1), Array(48 _
        , 1), Array(75, 1), Array(82, 1), Array(88, 1), Array(96, 1), Array(102, 1), Array(110, 1), _
        Array(128, 1), Array(130, 1), Array(150, 1), Array(156, 1), Array(161, 1), Array(190, 1)) _
        , TrailingMinusNumbers:=True