Posts by Jason_Moffitt

    I have two sheets that have a particular field with a many to one relationship with one another.

    I want the code to read through every line in the database (that sheet that has the 'many') and assign a category based on a value in that line, then go over to the other sheet and populate a field with that category on the corresponding line that has the relationship.

    If the sheet already has a category assigned, the program will determine if its current value is higher or lower and then either re-populate that field, or move on to the next item in the database.

    Re: fixing select case code issue

    Sorry if this wasn't clear. Don't want to waste everyone's time.

    I will try to explain:
    For each SO, I want it to look in the database and read the first line item.

    If the first line item is a category 1, then, I want to put category 1 on the Unique SO sheet as its classification, and move to the next SO.

    If the first line item is a category 3, then I want to see if there are additional line items in the SO on the SO database....

    If yes, then I want to see if that line item is a category 1 or 2, if yes, then call it that on the Unique SO sheet as its classification, and move to the next SO.

    If no, then call it category 3, and move to the next SO...

    Until all SO's are classified.

    Ultimately, on the Unique SO list, each SO will be categorized in one category only.

    so, for 93666, the first time it would be read, it would show as category 3, but, since it has multiple lines, we would read it again, see it is in category 2, and classifiy it on the Unique SO list accordingly.

    Right now, my code is reading everything as a 1, as you can see in the test sheet, so my select case is not working, and I am not sure if the way I have written the code will allow me to go through the multiple iterations the way I am describing.


    Re: fixing select case code issue

    I have attached a workbook as requested....It seems that it is going through the code, but the select case function is not doing what I was intending and I don't know if the select case will enable it to review the multiple lines in each SO as I was looking for.....look at SO 93666 in the example workbook to see what I has multiple line items, and should be read multiple times...before being categorized.

    Thanks in advance to anyone helping on this!

    I have 2 worksheets. One is a unique list of sales orders. The second is a database that holds sales order data. In the second sheet, these sales orders could have one or several line items. I am trying to assign a category on the unique SO sheet by reading through the items in the database assigned to that SO and giving it the 'highest ranking' category based on my criteria.

    Not knowing how to do this, I decided to use the advanced filter function and try to create a loop to read the value in the cells and then create a select case to assign the category (I am trying to use the first 8 characters in the cell to do the characterization)...however, I am not getting what I planned out of this and don't know why. I am up for suggestions as to scrapping it and doing something else, or fixing what I have created below.

    Hopefully, this makes sense. Ultimately, I want to go through all the line items tied to that SO...if the first one I read is a category 3, then I want to see if there are other line items...if no, then assign 3 and move on, if yes, then check it. If it is 1 or 2, then assign that number, otherwise see if additional lines, if no, assign three, if yes, check again....and so on. Any help would be appreciated.

    I have variable ranges of numbers on 5 different pages.

    I have a single list of unique numbers that I want to cycle thru those ranges and then return the worksheet it exists on , if it exists. If it does not, I want it to return "N/A"

    I imagine this is simple, but I am running into bugs with the methodology I am using in my code. So I am probably missing something simple or incorrectly using some of the nested loops. See below. And thanks in advance for any help anyone can offer.

    obviously am not much of a programmer...

    I am trying to validate a password and run a set of commands if they are there are multiple things they can do, I have segmented the password validation code from everything else.

    Here is that code:

    now....I want the Valid_User answer available in other code such that I can do the following...

    Everytime I have run this though "Valid_User" has not retained its value between routines.

    How can I make that happen?

    Is it possible to make my the sheet that I am referencing in a formula change based upon a value in a cell on a page....example:

    formula in cell c5 ='meg'!A1
    and I have pages for Meg, John, Kristen, etc...

    in cell A1 have the person's name....

    and instead have the calculation do something like this... =sheet(A1)!A1

    and get the answer so long as the sheet exists?

    Basically I want to have a group of sheets that are defined in an array? that code can run against in for each worksheet in array do ..... I am apparently missing something in my code and could use some sanity...Thanks

    group = Sheets(Array("1", "2", "3", "4"))
    for each worksheet in group
    next worksheet

    This really should be pretty easy, but, I am a little slow...

    I am creating a database of 'deals'. Each record gets a unique identifier based upon the customer, the qtr or date, and the sequential number of deals done with that customer...

    so...the next record in the database will not easily give you an indication as to whether it is a duplicate or not as you may not have done a deal with that customer in a while....

    thus far, we have used the auto filter to scroll through the records and find the last entry for that customer and then increment it by one, however, we have now reached a limitation with the autofilter in that it does not show all the records any longer...

    What would be the easiest way to ensure integrity in the process? VBA to run through every entry and validate that it is not identical after someone has entered a value in the cell? Is there a data validation function I can use?

    Any help would be appreciated.


    I am trying to format a report I generate every day...I copy into new workbooks and send via email...I want to format the sheets prior to saving and sending them so that everyone who receives it can just hit the print button and get the reports...I have two formats that I will need in the multiple sheets...this is the code I came up with but it is not working...can you help?

    My apologies....I had already included the declarations outside of the subroutine as they were classified as public...

    I still have my issue...any other ideas?

    Am I missing something in the syntax that I am overlooking?

    I have used the following code before...but now, on a new pc it is not working...I have a feeling that the reference libraries I had incorporated in the past are not activated in the new version of excel I am this true, or do you see some other issue with this code?

    Sub Adjust_Zoom()
    ' Adjust_Zoom Macro

    vidWidth = GetSystemMetrics32(SM_CXSCREEN)
    vidHeight = GetSystemMetrics32(SM_CYSCREEN)

    ScreenResolution = vidWidth & " X " & vidHeight

    Application.ScreenUpdating = False

    Select Case ScreenResolution

    Case "1024 X 768"

    For Each item In ThisWorkbook.Worksheets
    ActiveWindow.Zoom = 75
    Next item

    and so on.....

    Thanks in advance...