# Posts by Keithcornett

• ## Sort by cell format

Re: Sort by cell format

Hi Rich,

Thanks for your help on this. I copied the macro into a module in my file but when I ran it, I got a run time error (1004). I ran it again using F8 and saw that it put all the true/false statements in column G, but once it was done, that's when the error occured. The list I have has info and headers in columns A through E. Any suggestions?

Keith

• ## largest number formula

Re: largest number formula

Dan,

Try this formula in cell J4 and drag it down:

=IF(SUM(B4:H4)=0,"",INDEX(\$B\$3:\$H\$3,MATCH(I4,B4:H4,0)))

The "" will leave the cell blank.

Hope this helps,

Keith

• ## largest number formula

Re: largest number formula

Guess it would have helped if I sent the right file (sorry). Here it is!

• ## largest number formula

Re: largest number formula

Hi Dan,

Check out the worksheet and see if it's what you need. You can multiply the countif formulas by a number to get a final result in the same cell (i.e. number of weeks at max times 3 would be =COUNTIF(\$J\$4:\$J\$29,B3)*3 )

• ## largest number formula

Re: largest number formula

Lets say your data is contained in cells A1 through A20 and you want to multiply the highest number found by 4, then try this formula in cell A21:

=max(a1:a20)*4

Just change the cells and multiplier to make it work for you.

• ## Sort by cell format

Re: Sort by cell format

It's in column "B".

• ## Sort by cell format

I received a worksheet that has strikethrough formatting on certain cells.

I want to sort all rows by the strikethrough formatting so these rows will be at the top of the worksheet.

Any help on this would be most greatly appreciated.

• ## Message Box to identify erroneous entry's

Re: Message Box to identify erroneous entry's

That's exactly what I needed! Thanks...

• ## Message Box to identify erroneous entry's

Is there a way to have a message box pop up that would indicate the data entered in a cell is incorrect, not allow the entry and ask the person to please re-enter the data

Example: if a person enters a date in cell g4 that is prior to the "=today() formula in cell f1, then the message box would appear saying "Date entered cannot be prior to today's date"

• ## Deleting Specific Rows

Re: Deleting Specific Rows

Thank you so much! It works perfectly....

You Sir are a Genius!!! (and so are a lot of other people on this site)

• ## Deleting Specific Rows

I import data into another spreadsheet and the formulas extract what I need which may range from 100 to 500 rows.

I need to delete the remaining rows with formulas once a zero value is found in column A.

Is there a way that once the zero value is found, the next 500 or more rows could be selected and immediately deleted without having to further check for the zero value condition in each row? I thought this might speed up the processing time.

• ## Saving file as range / value

Re: Saving file as range/value

Thank you sir! It worked perfectly.

• ## Saving file as range / value

I need to save a file to be named as the value of cell A2.
This line of code works perfectly except that it does not define where the file is saved.
[vba]
ActiveWorkbook.SaveAs Filename:=Range("a2").Value
[/vba]
I need to specify the directory in which the file is saved. I tried the following code but I always get a compile error.
[vba]
ActiveWorkbook.SaveAs Filename:=B:\CPU\Range("a2").Value
[/vba]
Any help would be greatly appreciated.

• ## Deleting multiple files

Re: Deleting multiple files

I double-checked my code and found that I had a statement that closed the file prior to the kill statement being able to run (boy do I feel like a dummy) Once I got rid of the line of code that was the problem the kill statement worked like a charm.

Thanks for everyone's help! By the way, this is absolutely the best website I have ever been on when it comes to excel & VBA help. You guy's are awesome!

Keith

• ## Deleting multiple files

Re: Deleting multiple files

Hi Barry,

I'm not familiar with that function. How would I set the property file attribute flag for "archive" to true?

Thanks,

Keith

• ## Deleting multiple files

I placed the following code prior to the end of a sub routine to delete a particular excel file and any text files within the directory but after running the macro, the files were still intact and had not been deleted.

Kill "C:\My Documents\EGO Forms\Banking Logs\CPU\*.txt"
Kill "C:\My Documents\EGO Forms\Banking Logs\CPU\CPU.xls"
end sub

Any ideas or suggestions as to what I'm doing wrong would be greatly appreciated.

• ## Naming A File Based On Cell Content

Re: Naming A File Based On Cell Content

Boy do I feel like a dummy... I realized that I had accessed the file from Explorer so when the macro ran, it saved it to another location. The code works perfectly as long as excel is opened in the directory that the file resides.

• ## Naming A File Based On Cell Content

Re: Naming A File Based On Cell Content

Hi Jack,

Thanks for your help but when I tried the code:

ActiveWorkbook.SaveAs Filename:=Range("A2").Value

the file was saved as "Range(A2).Value"

I need it to save as the value of the cell and as an excel file instead of text, i.e. 9.xls, 10.xls, etc.

Thanks for any ideas you have on this

• ## is there an over-riding formula...

Re: is there an over-riding formula...

Try this formula. I used an assumption that cell b45 is the cell where there might be a value (you can change it to whatever cell you need)
=if(b45>0,"",sum(a45,a53))

Hope this helps out.

• ## Naming A File Based On Cell Content

I've placed a formula in cell A2 that looks at todays date and converts it to a 1 through 31 depending on the day of the month.

I need to save the file and name it as the content of that cell (i.e. 1.xls, 2.xls, etc.) in order to link it to other daily files.

Is there some VBA code that could possibly do this as part of a macro? Thanks for any assistance, I greatly appreciate the help.