Posts by mrfitness

    Hi Tom,
    Thank you for the update. I revised the summary a bit to fit my bosses needs, but ran into an issue when I tried my first order. Basically some job #s have length quantities greater than 1, so I broke out the quantity into duplicate rows (ie Qty 2 of length 6071 would break out into 2 rows of 6071). Therefore if I delete the code that removes duplicates it would work. Is there any harm in removing that section?:

    Code
    1. ' Remove the duplicate combinations.
    2. wsBestCombinations.Columns("A:A").Select
    3. wsBestCombinations.Range("A1:C" & LastRow(wsBestCombinations, 1)).RemoveDuplicates Columns:=1, Header:=xlYes


    Also, in the attached file, I ran the code with the job #. Is there any easy way to group the 'order combination' tab so that duplicate order combinations & job#s show up as one light with a quantity?

    Hey Tom
    I am using Windows 7, Excel 2013
    I did remove the X but it was sometimes failing at line

    Code
    1. wsOrder.Sort.SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal


    I would just close the book, not save it, reopen and run it again and it would be fine.


    I am still sorting through your code to figure out how you determine if rngFoundCell is not nothing (blnCombinationMatched = True in essence).I am decent with arrays but the ranges not so much.


    I figure at that point in the loop I could have used the information you put in a comment box to put in cells in a column (and then delete those values if blnCombinationMatched = False). Unless I do it after the loop before the sort?

    Tom you are a gentleman and a scholar! I tried re-running the code an got an error on the sorting, but I can try to figure that out myself.


    One thing I failed to mention which I did not think was important at the time was that each row of material order has an identifier related to the job the piece is needed for. So when I give my boss the best combinations he wants to know which job the pieces are related to. [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]job#[/TD]
    [TD="width: 64"]length[/TD]

    [/tr]


    [tr]


    [td]

    r1

    [/td]


    [TD="width: 64"]2254[/TD]

    [/tr]


    [tr]


    [td]

    r4 r5 r3

    [/td]


    [TD="width: 64"]3694[/TD]

    [/tr]


    [tr]


    [td]

    r6

    [/td]


    [TD="width: 64"]2254[/TD]

    [/tr]


    [tr]


    [td]

    r7

    [/td]


    [TD="width: 64"]3225[/TD]

    [/tr]


    [tr]


    [td]

    r7

    [/td]


    [TD="width: 64"]3225[/TD]

    [/tr]


    [tr]


    [td]

    r8

    [/td]


    [TD="width: 64"]3131[/TD]

    [/tr]


    [tr]


    [td]

    r9

    [/td]


    [TD="width: 64"]2254[/TD]

    [/tr]


    [tr]


    [td]

    r9

    [/td]


    [TD="width: 64"]2254[/TD]

    [/tr]


    [tr]


    [td]

    r10

    [/td]


    [TD="width: 64"]1174[/TD]

    [/tr]


    [tr]


    [td]

    r10

    [/td]


    [TD="width: 64"]1174[/TD]

    [/tr]


    [tr]


    [td]

    r10

    [/td]


    [TD="width: 64"]1174[/TD]

    [/tr]


    [tr]


    [td]

    r10

    [/td]


    [TD="width: 64"]1174[/TD]

    [/tr]


    [tr]


    [td]

    r10

    [/td]


    [TD="width: 64"]1174[/TD]

    [/tr]


    [tr]


    [td]

    r10

    [/td]


    [TD="width: 64"]1174[/TD]

    [/tr]


    [tr]


    [td]

    r11

    [/td]


    [TD="width: 64"]3225[/TD]

    [/tr]


    [tr]


    [td]

    r11

    [/td]


    [TD="width: 64"]3225[/TD]

    [/tr]


    [tr]


    [td]

    r12

    [/td]


    [TD="width: 64"]3131[/TD]

    [/tr]


    [tr]


    [td]

    r13

    [/td]


    [TD="width: 64"]2254[/TD]

    [/tr]


    [tr]


    [td]

    r15

    [/td]


    [TD="width: 64"]4402[/TD]

    [/tr]


    [tr]


    [td]

    r15

    [/td]


    [TD="width: 64"]4402[/TD]

    [/tr]


    [tr]


    [td]

    r15

    [/td]


    [TD="width: 64"]4402[/TD]

    [/tr]


    [tr]


    [td]

    r16

    [/td]


    [TD="width: 64"]2253[/TD]

    [/tr]


    [/TABLE]


    Currently the best combination is put in a comment box (Combination: [3225] [4402] [2254] [2254]), but how would I be able to have that data listed in a cell with the job# included (ie Combination: [3225] [4402] [2254] [2254] r7, r15, r1, r6) or one column for length combination and one column for job #?

    My company makes pieces of tubing from our standard stock lengths of 20ft, 40ft and 60ft. We get a daily order request of tubing pieces of different lengths (less than 20ft) and my job is to figure out what combination of individual pieces can be combined to make up a stock piece with minimal waste (we ideally want to use 40ft stock length tubing to cut up for orders as it is easy to handle.)


    I have a numeric list that represent lengths of piece order (in mm). The number of pieces in the order changes all the time. I need to find the combination of numbers that comes closest to 12,192mm (40ft). Then once I find that, I need to discard those piece orders (as they have been used to make up a stock piece) and then I need to find the next combination, etc until all pieces in the order have been accounted for. If there is a great deal of waste (ie more than 15%) the combination is not ideal and I would want to try to fit pieces to 18,288mm (60ft) or 6,096mm (20ft)


    For example the list below is an order, each row is a piece order request in mm [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64, align: right"]2254[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3694[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2254[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3131[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3131[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2254[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2253[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3225[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2254[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3225[/TD]

    [/tr]


    [tr]


    [TD="align: right"]4402[/TD]

    [/tr]


    [tr]


    [TD="align: right"]1174[/TD]

    [/tr]


    [tr]


    [TD="align: right"]1174[/TD]

    [/tr]


    [tr]


    [TD="align: right"]1174[/TD]

    [/tr]


    [tr]


    [TD="align: right"]1174[/TD]

    [/tr]


    [tr]


    [TD="align: right"]1174[/TD]

    [/tr]


    [tr]


    [TD="align: right"]1174[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3225[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2254[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3225[/TD]

    [/tr]


    [tr]


    [TD="align: right"]4402[/TD]

    [/tr]


    [tr]


    [TD="align: right"]4402[/TD]

    [/tr]


    [/TABLE]

    I could get these broken out into the following groupings: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 76"]ACTUAL LENGTH[/TD]
    [TD="width: 329"]Combinations used from list above[/TD]

    [/tr]


    [tr]


    [TD="align: right"]11333 > [/TD]

    [td]

    2254, 3694, 2254,3131

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]11552 > [/TD]

    [td]

    2254,2254,1174,1174,1174,1174,1174,1174

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]11835 > [/TD]

    [td]

    3225,3131,2254

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]11057 > [/TD]

    [td]

    4402,4402,2253

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]10852 > [/TD]

    [td]

    3225,3225,4402

    [/td]


    [/tr]


    [/TABLE]

    I tried using the SOLVER add-in but couldn't get it to work so wondering if there is another way or in vb script to find what I need it to do?

    So after a few hours I finally was able to figure it out. Here is what I did:
    Open a new excel instance
    From there I click browse to get to the file, and I hold down the shift button and open file
    When "enable macros?" screen comes up I disable macros.
    I do NOT enable content (yellow bar at top). I go to vb code screen and write in Stop in first line of workbook open procedure
    I save and close the file then reopen it normally and enable content. The code will take me to the Stop line in workbook open. I stop the code and remove the 'Stop' line in workbook open procedure. I save the file, close and reopen
    Now all my coding in all modules appear!
    I hope this will help someone else should they come across this weird issue

    I was unable to find someone with the same issue so I am posting it here in case it happens to anyone else.
    It appears something happen to a saved excel file because when I open the file it says "Cant find project or library" but after pressing OK I open to the vb window and I can see all my modules listed. However when I try to click on any module or worksheet to see the code all that comes up is an empty white box (no code). See attached 'no vb showing' image of what I mean. Note there is no option to compile code either.
    Then when I close the file it says the missing library message again and excel crashes (see excel stop working file attached)
    I will continue to try and find a solution, just wondering if this has happened to anyone else?
    Thanks!

    Is it possible in vba to close the current workbook you are using without saving changes, re-open it, update a cell value, then close that workbook this time saving changes?


    I had code that worked, but when I tried it again in another workbook it would close the first time, terminating the code.


    I am using redemption code vba to send emails in my excel application (apparently a safer way to email using vba)


    The code is intended for users and therefore the emails would be sent by the code instead of the user (to avoid any tampering, etc by the user with sensitive information in the email)


    The main issue I am having is getting the excel vba code to resolve email names/groups without having the Outlook security warning pop up, as well as the issue of having the email get sent before the names/groups are resolved (which causes Error Number: -2147418113: "Could not resolve the message recipients" - even though the address is legitimate).


    I have simplified my code and hard coded my email variables for this example, using fake addresses instead of group names. I also replaced the .SEND command with the .DISPLAY command to view if the addresses have been resolved or not. In actuality the email addresses will be different group names that, if not resolved, would cause an error when sending with the .SEND command. (as noted in the previous paragraph)




    However, If i change the "With SafeItem.Item" statement to "With SafeItem" only (removing the ".Item" at the end), the email does display but it does not seem to be resolving, hence no security pop up appears. ("SafeItem.Recipients.ResolveAll" does not seem to be doing anything actually.)

    How can I resolve the addresses without getting the outlook warning?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Ok I figured out how to do it so im posting the answer here for anyone else who is having this problem, as it took me a LOT of trial and error.


    In my example, I have the addresses to email on "sheet1" of the workbook. The TO line addressess are in cell A1, and the CC line addresses are in cell A2. I have semi colons (";") seperating the email address names/groups, without spaces. (You could use cell A3 for email subject and cell A4 for email body but I chose not to in this example)


    ie cell A1 is [email protected];[email protected] (could also be a group name- ie "forum_group")


    You can have as many email addresses as you want in either cell A1 or A2, as I have a loop that uses Instr() function and ubound() to add each one.


    Quote from mrfitness


    How can I resolve the addresses without getting the outlook warning?

    Re: Error Log On Network For Multiple Users


    Quote from Ger Plante

    Use two machines and a shared out text file (on a network, or shared folder). On one machine, just step through your code (or my code) and when the text file is open, go to another machine and try and open that same text file for append again (same code, different machine, accessing the same file).
    My bet is that it will crash again saying the file is already open.
    Ger


    Funny you mention that, I did test it out on 2 computers at the same time with the log file on the network. I ran the same code at the exact same time with the notepad open and did not receive any errors. When I closed notepad and re-opened it, both our logs were there! I did try this test many times, and I even put a toggle break on each of our VBA windows right before it writes to the log file and pressed the 'Play' button at the same time and it still worked.

    Re: Error Log On Network For Multiple Users


    Quote from Ger Plante

    The Open statement will attempt to open the text file. If it is ALREADY open then the Open statment will fail (this is what I was showing you with my code above).


    What I did to test was that I went on the network and opened the text file that the program writes to. Then I ran the program and thought I would get an error on the OPEN statement (as I had the text file opened) but it didn't error out at all (I even put in an error handler in the code)


    Instead, after the program ran, I closed the text file, re-opened it, and it contained the new data just written from the program!


    Please try doing a similar test on your computer with your code using "c:\test.txt", and remove the 2nd open statement in your code above. Let me know if you also do not get an error so I know I am not going crazy.


    Thank you so much!

    Re: Create Error Log On Network For Multiple Users


    Quote from Ger Plante


    So before your Open statement, you need an "on error resume next" statement and keep looping while the err.number = 55


    Well doesn't this part of the code make sure it doesn't error on open:

    Code
    1. FileNum = FreeFile ' next file number
    2. Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist


    So in essence the text file will open no matter what, but only one of the open files will save? Maybe I should change FileNum to a 1?

    I am creating an error log as a text file for my application. Since it is being used by many different users, I decided to keep it logged in a specific folder (that is protected) on our network.
    I have tested the code and it works, even if I have the log file open when I run the module, it still writes to the log file and saves (as long as I don't save the opened log file after the module runs)
    What I am worried about is when 2 or more users are trying to write to the log file at the same time. I wanted to add something that checks if the file is open, and if it is, wait a second or two and then try to write to it again. Maybe try it a few times?


    Below is my sample code.
    Note that I am using the log creation in another module.

    I have certain sheets that I want to copy into a workbook I have saved on the C:drive (C:\Book1.xls). I want to do this numerous times so I want to leave Book1.xls as a template for lack of a better word.


    I want to know how to code the following:
    open book1.xls copy certain worksheets into the book1.xls,
    saving book1.xls as another name (based on a variable in the active workbook) to C:\PATS,
    closing book1.xls, the original workbook that I copied the sheets from, and how to keep open the new book as well as how to close that too.


    thanks in advance


    so far i got this:

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]The reason why I want to do this is because Book1.xls has a password protected VBA project lock, and I figure if I copy sheets into this workbook as compared to a new one, my code on the worksheets I'm copying over into Book1.xls will be protected and not viewable. If that is not the case, please advise.
    Thanks

    Re: Locking Vba Project With Code


    The code is protected in the main book. The issue is trying to protect the code in the new book.
    E.G. VBA Project Protected Workbook 1 contains sheets 1,2,3
    Email creates workbook 2 containing only sheet 3 (carrying over worksheet code containing password text spelled out in the code, along with other code to continue the process)


    How do I hide the code in workbook 2 to keep the user's from seeing the password?

    Re: Locking Vba Project With Code


    I unlock the main workbook in order to select certain sheets and copy them into a new workbook that I save and email. This new workbook is protected and needs to have the ability via code to be unprotected. The issue is that a user could view the worksheet code and see my password="blah" statement.
    I've seen others attempt to solve this by making a sheet veryhidden and hiding a password on this sheet which is referenced in code. However, a user could just as easily run code which retrieves that cell's value.
    I have also found code to copy lines of code from one module to another workbook module, however, if the project isn't locked, the user can still find this code.
    Any ideas?
    Thanks,

    I understand that we can protect the VBA coding through check the "Lock project from viewing in the Tools>VBAProject Properties. However I would like to know if this is possible in code?


    I have a mainworkbook for users that has the VBA coding protected (it contains passwords to unlock protected sheets / main workbook). In my program, the users will be entering data and then push a button which calls a macro to email a "skinny down version" of the main book to other users. Problem is, these users that receive the email will be able to get into the VBA code and see what the password is on the sheets. (even if the new workbook/worksheets are password protected)


    Is there any way when I save the new book to add this protection to the VBA code?


    Thanks in advance

    I have created an application for users that requires the user be connected to the network. I wanted to do a check to see if they are connected, and if the user is not connected they would get a message box telling them that they need to be connected to the network, then the workbook would close.


    Below is the code I have, but when a user is not connected they do not get my messagebox, instead they get the excel error message "excel run-time error '52': bad file name or number", and the debugger line that gets highlighted is in the function DirExists that checks the length of the directory of the file path: Len(Dir(strpath))



    Could anyone please help me figure this out? Thanks in advance[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Anyone out there to help? Thanks