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


    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

    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

    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

    I have a spreadsheet that has about 1000 rows with formulas.


    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

    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

    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.


    Thanks again for your help.

  • 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

  • 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.