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.

Thanks!

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 mean...it 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.

Sheets("Unique SO").Select
Range("B5").Select


Start:


    current_so = ActiveCell.Text
    current_cell = ActiveCell.Address
    
    Sheets("SO Database").Select
    Selection.AutoFilter Field:=1, Criteria1:=current_so
    Range("A4").Select
    ActiveCell.Offset(1, 0).Select
    
Continue1:
    Select Case ActiveCell.Value
        Case Is <> ""
            Item = Left(ActiveCell.Offset(0, 5).Value, 8)
            Select Case Item
                Case "Pancake "
                    Config = "1"
                    GoTo Continue2
                Case "Scramble"
                    Config = "2"
                    GoTo Continue2
                Case Else
                    ActiveCell.Offset(1, 0).Select
                    GoTo Continue1
            End Select
        Case Else
            Config = "3"
            GoTo Continue2
    End Select


Continue2:
    Sheets("Unique SO").Select
    Range(current_cell).Select
    ActiveCell.Offset(0, 2).Select
    ActiveCell.Value = Config
    Range(current_cell).Select
    ActiveCell.Offset(1, 0).Select
    
    Select Case ActiveCell.Value = ""
        Case True
            GoTo Finish
        Case False
            GoTo Start
    End Select


Finish:


Sheets("SO Database").Select
Selection.AutoFilter


Sheets("Unique SO").Select
Range("B5").Select
MsgBox "Complete"


End Sub

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.

'Get unique sales order list created


    Sheets("EOQ Activity Report").Select
    Range("A4").Select
    Range("A3:A1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "AD3"), Unique:=True
        
    Range("AD4:AD1000").Select
    Selection.Copy
    
    Sheets("Unclassified SO Report").Select
    Range("B5").Select
    ActiveSheet.Paste




'Compare unique SO's to each tab and report location


    Sheets("Unclassified SO Report").Select
    Range("B5").Select
    
For Each my_SO In Unique_SO_List
    current_so = ActiveCell
    SO = ActiveCell.Text
        
        For Each i10k_so_cell In I10K_SO
            If ActiveCell.Text = SO Then
                Location = "I10K"
                GoTo Continue_Loop
            End If
        Next
            
        For Each Cabinet_SO_cell In Cabinet_SO
            If ActiveCell.Text = SO Then
                Location = "Cabinet"
                GoTo Continue_Loop
            End If
        Next
            
        For Each Upgrade_Director_SO_cell In Upgrade_Director_SO
            If ActiveCell.Text = SO Then
                Location = "Upgrade Director"
                GoTo Continue_Loop
            End If
         Next
            
        For Each Switch_SO_cell In Switch_SO
            If ActiveCell.Text = SO Then
                Location = "Switch"
                GoTo Continue_Loop
            End If
        Next
        
        For Each Other_SO_cell In Other_SO
            If ActiveCell.Text = SO Then
                Location = "Switch"
                GoTo Continue_Loop
            End If
        Next
        
        Location = "N/A"
        
Continue_Loop:
current_so.Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Text = Location
Exit For


Next

Can you not use conditional formatting to accomplish this?

I am trying to open the file directory in code and allow a user to select which file they are going to use? what command brings up the directory?

That worked...didn't know the syntax...tried global and universal rather than public.

Thanks

obviously am not much of a programmer...

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

Here is that code:

Sub Password_Control()


Dim Current_Password As String
Dim Answer As String
Dim Incorrect_Response As Integer
Dim Valid_User As String
Dim item As Worksheet


Current_Password = Range("B1").Value


Beginning:


Answer = InputBox("Please enter your Password?", , "***")


Select Case Current_Password = Answer
    Case False
        Incorrect_Response = Incorrect_Response + 1
            Select Case Incorrect_Response
                Case 3
                    Exit_Program
                Case Else
                    MsgBox "You have entered an incorrect password.  The workbook will close after three incorrect attempts."
                    GoTo Beginning
            End Select
    Case True
        Valid_User = "True"
End Select




End Sub

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

Sub Unlock_workbook()


Password_Control


Select Case Valid_User.Value
    Case "True"
        
            For Each item In ThisWorkbook.Worksheets
            item.Select
            ActiveSheet.Unprotect Answer.Value
            Next item
        Sheets("Configurator Options").Select
        MsgBox "Workbook unlocked"
    Case Else
        Application.ScreenUpdating = True
        Exit Sub
End Select


Application.ScreenUpdating = True


End Sub

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

How can I make that happen?

When you create a pivot table using code, the macro recorder does not seem to pick up when you change the field from count to sum, how do I programatically make the table make that change?

Thanks!

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 sequence...ie 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
sheet.select
etc...
next worksheet

Dave!

Thanks that was exactly what I was looking for.

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.

Thanks

I am copying a number of worksheets to a new book. I have them formatted for printing in the original, but the print parameters do not copy over. How can I make this happen programatically?

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?

For Each item In ActiveWorkbook.Worksheets
    item.Select
    
        Select Case item.Name
            Case "Report A"
                With ActiveSheet.PageSetup
                .PrintTitleRows = "$1:$1"
                .PrintTitleColumns = ""
                End With
                ActiveSheet.PageSetup.PrintArea = "$A$1:$J$63"
                With ActiveSheet.PageSetup
                .CenterFooter = "CONFIDENTIAL"
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = 2
                End With
                ActiveSheet.ResetAllPageBreaks
                Set ActiveSheet.HPageBreaks(2).Location = Range("A40")
            Case Else
                ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$55"
                With ActiveSheet.PageSetup
                .CenterFooter = "CONFIDENTIAL"
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = 1
                End With
        End Select
    
    Next item

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 running...is 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
item.Select
ActiveWindow.Zoom = 75
Next item

and so on.....

Thanks in advance...

a in its simplest form is simply a way to record the repetitive actions you do so they can be done automatically for you in the future.