Posts by flee01

  • Sum Criteria With Non-blank Cells

    Re: If And Sum Statement With Nonblank Cells


    The formula has to be an array formula if you want to make it opt out of using certain cells in a range. Array formulas are entered like normal, but after you are thru editing them to tell excel you want the cell to be treated as an array you hit ctrl + alt + enter. The other thing is you canot use only the column reference. The closest that you can come to referencing the whole column is I1:I65535; which is pretty close.


    Here is the formula using just a thousand lines:


    Code
    =sum(If((Data!D1:D1000="162")&(Data!V1:V1000<>"")="truetrue",Data!I1:I1000,0)

  • Add File Name to Cell Of Another Workbook

    Re: File Name To Be Update On Separate Workbook


    It looks as if your request is simply the need to record the new file name of your new data, but at the same time looks as if you are not actually putting your new data in a new file, but rather just on a different worksheet. Or maybe I am just not getting what you are trying to say.


    Please be very specific as to the range that is going to be captured, and it headings/row names and data types. Also, where exactly does it needs to be saved to? Is it going to replace the cell values where it ends up or append existing values?

  • Collate unique cells from many workbooks

    Re: Collate unique cells from many workbooks


    The sample workbooks included are:

    1) master.xls: The master spreadsheet where all data will be going to.
    2) a few test spreadsheets: Blank workbooks with something in cell A1 to import.

    Master.xls imports the value stored in all the A1 cells from every workbook in the directory as long as the file end in .xls, except master.xls. Master.xls imports the data it finds in other speadsheets to a new line in the master spreadsheet. You can have up to 65,536 files in the directory. I ran a preliminary test. On my not-so-great pc, the code was able process about 200 small files per minute.

    To test master.xls without having to modify the code:
    Create a folder called c:\master. Extract the .zip to c:\master directory. That is what the example is coded to. Open master.xls. Hit alt + F8. Highlight myImporter and click run.

    If you want to veiw the code, right click the sheet1 tab of master.xls and select View code. Dbl click module1 in the upper left hand corner.

    If you need help getting the ball rolling on customizing it to your spreadsheets, just upload an example of the spreadsheets you'll be importing, and the order the cells need to appear in the master spreadsheet.

    HTH.

  • Convert text to long number macro

    Re: Convert text to long number macro


    One thing to correct is instead of using "A" in your Cells() reference, use the number one referring to the first row A:A.


    Code
    Sub Print_() 
    ActiveSheet.PageSetup.PrintArea = Range((Cells(65536, 1).End(xlUp)), 24) 
    ActiveSheet.PrintOut 
End Sub

  • hide the page field items

    Re: hide the page field items


    By "page field item", could you be more specific? What do you mean by (ALL)? I don't understand because your terminology is something I am not familiar with.

  • Use text in cell as code.

    Re: Use text in cell as code.


    You'll have to forgive me. I don't know what you could be confused about. I want to put code in a cell that vba will use by extracting the .text or the .value of the cell in question.


    HTH