Posts by st8champ3x

    I need to save a file (using vba) with an extension other than xls, txt, or any of the other standard extensions. The file extension needs to be PST (so the filename would be something like "TEST1.pst". The file name is concatenated right before the saveas line so it could be "TEST1.pst" or "TEST2.pst", etc. Normally if I was doing this manually, I would enter the filename (with the quotes included) into the SaveAs dialog box, choose the file type as text, and then click Save. Please let me know if anything is unclear. Thanks in advance!

    Re: Update Userform with Screenupdating off





    At this point in the code, all of the code starts executing but at certain points in the code I was hoping to just update the form and change the labels from "Running..." to "Complete!". The problem is that I can change them in the code, but the user won't see the changes because the screen is not being updated.

    Re: Update Userform with Screenupdating off


    At the moment it is refreshing at all. Basically at the beginning of the code the screenupdating gets turned off. Then the form gets loaded but because the screenupdating is off, it just shows up white (not readable). I would like to keep the excel screen from being updated but refresh the form along the way. Is this even possible? Let me know if I should be more clear. Thanks again!

    I have a form that i am using that I would like to update at certain points in the code. I have the screenupdating set to false but it seems that this is keeping the userform from refreshing as well. Is there a way to refresh the userform without turning the screenupdating back on (because the user cannot see what is happening behind the scenes. Thanks in advance!

    Re: Formula to calculate avg/wtd avg on a subset of a list


    ok - one last try. i want this to all happen in one formula it would sort of like saying something like {=SUM(('Whole List'!A2:A9=Subset!B2)*('Whole List'!C2:C9))} except for just one condition, I want to say if the product on the whole list worksheet is equal to subset 1 OR subset 2 OR subset 3, etc. Keep in mind this is for a huge data set so an array formula is what I need rather than just using vlookups and then averaging the data. Thanks for the help so far, I think we might be getting close :)

    Re: Formula to calculate avg/wtd avg on a subset of a list


    What I am thinking is of someway to use an array to that would do basically what an array sumif, averageif, countif, etc. would do by saying only average the prices of the subset products. This would use just 1 formula and would save a ton of space. I wish I could use the real data but I will have stick to the example that I attached. Let's say out a customer ordered 3 of the "products" listed on the subset page, I want to calculate their average price and average weight. I know I could go lookup the data with a formula from the Whole list worksheet and then perform a simple average but doing this on the scale that I am doing it adds about 4MB to the file.

    I will do my best to explain but just in case I have attached a worksheet to make things easier. I have a list of a few thousand products with data on each product. When I run through a series of cuts, I get a subset list and want to be able to calculated a weighted average by somehow saying to do a weighted average (and/or count, and/or average, etc.) on the characteristics of only the products in the subset. Let me know if this doesn't make sense but hopefully the attached example helps. Thanks!

    I have a list that is sorted based on component cost that combine to form a total cost and I would like to filter out the worst 10% (keep the top 90%) of the list. This way I can filter out 10% of the worst component costs which would lower my total cost by more than 10%. Does anyone know how to do this using an advanced filter (or if all else fails using the autofilter). Thanks in advance!!

    I am looking for a format that will turn a decimal price into ticks such as 94.5 to 94-16, 101.125 to 101-04, etc. Right now the formula I created does this for me but it is pretty long an simplicity is key. My formula is:


    =ROUNDDOWN(J2,0)&"-"&ROUND(((J2-ROUNDDOWN(J2,0))/1)*32,0)&IF(AND((J2-ROUNDDOWN(J2,0))*32-ROUNDDOWN((J2-ROUNDDOWN(J2,0))*32,0)>0.25,(J2-ROUNDDOWN(J2,0))*32-ROUNDDOWN((J2-ROUNDDOWN(J2,0))*32,0)<=0.5),"+",IF(AND((J2-ROUNDDOWN(J2,0))*32-ROUNDDOWN((J2-ROUNDDOWN(J2,0))*32,0)>0.5,(J2-ROUNDDOWN(J2,0))*32-ROUNDDOWN((J2-ROUNDDOWN(J2,0))*32,0)<=0.75),"-",""))


    This takes the number without the decimal and then converts the decimal into 32's (also includes +'s and -'s). If anyone is familiar with something that could help, I'd really appreciate it. Thanks in advance!!

    This might seem a little bit confusing but I'm sure someone out there knows how to do this.


    I need to activate a worksheet with a filename that changes on a daily basis. Here is how it works:


    I have a standard template that gets copied into a folder and run with new information daily. Each day, I create a new folder, copy the template into it, and rename the file to "XXXXX Workbook.xls where XXXXX changes daily. I am doing using the sumproduct function that is linked to another workbook that contains matrices. When I open the matrix workbook in vba, it causes the matrix workbook to be active and I need to activate the "XXXXX Workbook.xls". Can I use a wildcard in the Windows("XXXXX Workbook.xls").Activate like Windows(* & " Workbook.xls").Activate. Let me know if anyone has a way to fix this problem. Thanks in advance!!

    I have a worksheet that is generated by a program that I cannot control its output so I am stuck with 40 different worksheets in 1 excel file that have info I need off each. Each worksheet has its own specific name (comprised of a generic name and then a unique identifier) but I need a way to pull a specific number using vlookup off each worksheet. They all have a similar naming convention so it would be great if I could use something like VLOOKUP(B1,"'Worksheet"&B2&"'"!A1:K100,10,false) where the worksheet name in the formula would be a conactenation of the generic worksheet name + the unique identifier. Does anyone know an easy way to do this because although I could type 40 different vlookup formulas, when I move to over 1000 it would be too much. I hope my question is clear enough. If not let me know. Thanks in advance!

    Re: Using a variable with R1C1 reference in vba


    I just tried running that but got a 1004 error, any suggestions?


    Sub Table()


    intRightR = 0
    intRightC = 2
    intBottomR = 19
    intBottomC = 0

    Range("L28").Select


    Range("L28").Formula = "=MIN(R" & intRightR & "C" & intRightC & ":R" & intBottomR & "C" & intBottomC & ")"
    If Range("L47") = 0 Then ActiveCell.Offset(0, -1).Select
    If Range("N28") = 0 Then ActiveCell.Offset(1, 0).Select



    End Sub

    I am trying to use the R1C1 reference style in my code but intead of numbers I want to use variables. Right now I have the line:


    ActiveCell.FormulaR1C1 = "=MIN(R" & intRightR & "C" & intRightC & ":R" & intBottomR & "C" & intBottomC & ")"


    and depending on an evaluation of two numbers, the variables will change and the loop will start over. The only problem is that this line of code is not working. I saw Derk's post of trying something similar to:


    Range("A1").formulaR1C1="=SUM(R" & row1 & "C" & col1 & ":R" & row2 & "C" & col2 & ")"


    Could anyone help me out with this. Thanks in advance!

    I am trying to change the font to all black, print the worksheet, and then undo the font color change. I need some way of doing this because I am working with many sheets and all are different dimensions so I can't just turn the font to black and then change particular cells, rows, etc back to their original color. Thanks in advance to anyone who can help!


    Steve