Posts by dsrt16

I have a setup table where users will indicate how many tables they need.

I tried this vba code

Application.ScreenUpdating = False
    
        If Sheet7.Range("B63").Value = "0" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table, Service4Table, Service5Table").Hidden = False
        
        ElseIf Sheet7.Range("B63").Value = "1" Then
            Sheet1.Range("ServiceTable1").Hidden = False
            Sheet1.Range("ServiceTable2, Service3Table, Service4Table, Service5Table").Hidden = True
        
        ElseIf Sheet7.Range("B63") = "2" Then
            Sheet1.Range("ServiceTable1, Service Table2").Hidden = False
            Sheet1.Range("Service3Table, Service4Table, Service5Table").Hidden = True
    
        ElseIf Sheet7.Range("B63") = "3" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table").Hidden = False
            Sheet1.Range("Service4Table, Service5Table").Hidden = True
        
        ElseIf Sheet7.Range("B63") = "4" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table, Service4Table").Hidden = False
            Sheet1.Range("Service5Table").Hidden = True
        
        ElseIf Sheet7.Range("B63") = "5" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table, Service4Table, Service5Table").Hidden = False
    
        End If
    
  
  Application.ScreenUpdating = True
      
    
End Sub

But it seems you can't use .Hidden with a table. Any ideas?

Re: VBA code runs in excel 2003, but gives error message in excel 2010

Your answer seems to be another question unless I am reading it wrong, or I am just cluless, but I don't understand how what you wrote relates to my question. Clarify if you were attempting to answer my question.

I wrote a line of code to rename worksheets in my excel spreadsheet, and it executes perfectly at home where I have 2003 software, but at work where I need it to run, I get the following message: System errror -2147417843 An outgoing call cannot be made since the application is dispatching an input synchronous call.

The code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Sheets("Set-up Page").Range("B2").Value
End Sub

Each worksheet has that code referencing a different cell. It works perfectly at home, but not at work, and I am suppose to show my boss the program I created today...help!

Re: Protect all worksheets except one

True! Sorry about that. Great code. Thanks. Such a simple solution, but I haven't done vba in so long, I couldn't even remember how to do elseif statements! Thanks for the help!

Re: Protect all worksheets except one

So simple! However, there now isn't a line of code to unprotect every worksheet. Would I need to embed another if statement. Because I still need the line, if protect contents is true then unprotect.

There are 30 worksheets in the workbook, and as the creator, I need a way to unprotect all of them. Your code has taken out that option. I am sure there is a way to have your if statement to unprotect that one sheet, and the old if statement to unprotect all of them, but I don't know how to do that.....

I have used the code from ozgrid website to protect all worksheets, but then I have worksheet in the workbook where users need to insert objects, and they will not be able to if that worksheet is protected.

Is there a way to program an exception for that one worksheet (so it will NOT protect that one) into the following code:

Private Sub CommandButton1_Click()


Dim wSheet As Worksheet


    For Each wSheet In Worksheets


        If wSheet.ProtectContents = True Then


            wSheet.Unprotect Password:=TextBox1.Text


        Else


            wSheet.Protect Password:=TextBox1.Text


        End If


    Next wSheet


Unload Me


End Sub

Re: Lookup Nth Occurence In Range & Limit Rows To Search Based On Cell Value

Dave-
I see how that would work. That is awesome that you can just build the arguments of the function like that (but I guess that would make sense seeing how it is a UDF). I never imagined that was possible. Thanks! I will test it when I get home from school, but I am sure it will work.

Is there a course I can take that teaches writing UDFs?

Mikerickson-- I am sure yours works too, but I have to admit I do not understand all the arguments of the offset and Mid functions.

So I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.

I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.

I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.

If value I5 says Period_1 it will only look in rows 12:46.

If value I5 says Period_2 it will only look in rows 48:82 etc.

I believe I need to modify the xl look part of the code with if statements.

Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.

Function Lookup_Occurence(To_find, Table_array As Range, _
    Look_in_col As Long, Offset_col, Occurrence As Long, _
    Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
     
    Dim lLoop As Long
    Dim rFound As Range
    Dim xlLook As XlLookAt
     Dim lOcCheck As Long
     
     
     
    If Part_cell_match = False Then
        xlLook = xlWhole
    Else
        xlLook = xlPart
    End If
     
    Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1)
     
    On Error Resume Next
         lOcCheck = WorksheetFunction.CountIf _
    (Table_array.Columns(Look_in_col), To_find)
     
    If lOcCheck < Occurrence Then
        Lookup_Occurence = vbNullString
    Else
        For lLoop = 1 To Occurrence
            Set rFound = Table_array.Columns(Look_in_col).Find _
            (What:=To_find, After:=rFound, LookAt:=xlLook, LookIn:=xlValues, _
            MatchCase:=Case_sensitive)
        Next lLoop
         
        On Error GoTo 0
         
        Lookup_Occurence = rFound.Offset(0, Offset_col)
         
         
        End If
         
         
         
    End Function

Re: Lookup Array/Range Based On Cell Value

Yes, thank you I got it to work. It was a layout issue with my table. I am learning

Thanks!

Re: Lookup Array/Range Based On Cell Value

It didn't work; it is still just repeating the loop. Right now I have the value "Katie" as the 1st occurrence of high, and the value "Randy" as the 2nd. With my lookup_occurrence formula I went all the way to the 35th occurrence, and it just returned Katie, Randy, Katie, Randy, etc... all the way down.

It's weird because it seemed to work the first time I tested it. Any thoughts?

Maybe I have some spacing issue on the coding; that has certainly been a problem with me before.

Here is the Module coding:

Function Lookup_Occurence(To_find, Table_array As Range, _
    Look_in_col As Long, Offset_col, Occurrence As Long, _
    Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
     
    Dim lLoop As Long
    Dim rFound As Range
    Dim xlLook As XlLookAt
     Dim lOcCheck As Long
     
     
     
    If Part_cell_match = False Then
        xlLook = xlWhole
    Else
        xlLook = xlPart
    End If
     
    Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1)
     
    On Error Resume Next
         lOcCheck = WorksheetFunction.CountIf _
    (Table_array.Columns(Look_in_col), To_find)
     
    If lOcCheck < Occurrence Then
        Lookup_Occurence = vbNullString
    Else
        For lLoop = 1 To Occurrence
            Set rFound = Table_array.Columns(Look_in_col).Find _
            (What:=To_find, After:=rFound, LookAt:=xlLook, LookIn:=xlValues, _
            MatchCase:=Case_sensitive)
        Next lLoop
         
        On Error GoTo 0
         
        Lookup_Occurence = rFound.Offset(0, Offset_col)
         
         
        End If
         
         
         
    End Function

And the formula =Lookup_Occurence(B11, INDIRECT($N$5), 1,1,3). Right now the formula is returning "Katie" but it should be empty as there is no 3rd occurrence.

Re: Lookup Array/Range Based On Cell Value

Thank you,; it is working fine! The only other thing is it loops clear back to the beginning. So if I am the formula reference the 7th occurrence, and the 7th occurrence does not exit, it returns the first occurrence again. Is there anyway to have it remain empty if there is no 7th occurrence instead of re-looping all over again.

Re: Lookup Array/Range Based On Cell Value

I have that custom function; is something different with this one?

And what attachment; I did not see attachment.

Re: Lookup Array/Range Based On Cell Value

I did see that. Your example however is referencing cats, which is only a value in your table.

My references are formulas that return high, med, low, and the lookup_occurence is not recognizing the values as high, med, low unless I actually type them in. Even though the cell says high (the result from the formula), it doesn't recognize it. It is seemingly only recognizing typed-in values.

PS: I went into your sample and changed cat to the formula IF(E2=2, "Cat", "") and it returned Cat as it should,but your workbook had the same problem if a formula returned cat, then the lookup_occurence formula did not recognize it said cat.

Re: Lookup Array/Range Based On Cell Value

Well I figured out why it was returning #value, but I need a way to fix it.

The lookup_occurence formula is looking for the first occurrence of B8 (which currently says High), and returning the name in the column next to the first High. It kept returning a #value, which didn't make sense because there were certainly plenty cases of high in the table.

The table currently returns high, med, or low based on a formula. I went into one cell and deleted the formula and just typed the word high; when I did that, the lookup_occurence formula returned the name next to the first high.

If the formula returns high, it won't read it as a high value in the lookup, but if I enter high, it does.

How can I get it to read that the cell DOES say high even though it is the result of a formula?

Re: Lookup Array/Range Based On Cell Value

This the lookup Custom Function.Lookup Occurence

Function Lookup_Occurence(To_find, Table_array As Range, _
    Look_in_col As Long, Offset_col, Occurrence As Long, _
    Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
     
    Dim lLoop As Long
    Dim rFound As Range
    Dim xlLook As XlLookAt
    Dim lOcCheck As Long
     
     
     
    If Part_cell_match = False Then
        xlLook = xlWhole
    Else
        xlLook = xlPart
    End If
     
    Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1)
     
    On Error Resume Next
     lOcCheck = WorksheetFunction.CountIf _
        (Table_array.Columns(Look_in_col), To_find)
        
        If lOcCheck < Occurrence Then
           Lookup_Occurence = vbNullString
        Else
            For lLoop = 1 To Occurrence
                Set rFound = Table_array.Columns(Look_in_col).Find _
                (What:=To_find, After:=rFound, LookAt:=xlLook, LookIn:=xlValues, _
                    MatchCase:=Case_sensitive)
            Next lLoop
     
    On Error GoTo 0
     
    Lookup_Occurence = rFound.Offset(0, Offset_col)


      
    End If
    
    
     
End Function

On my real workbook (where this code resides) it returned the #value error when I attempted to nest the indirect formula into the lookup_occurence.

Re: Group Data With Changing Criteria

Sorry, I am not trying to assume a means to an end at all.

The teacher puts in the students score (didn't mean imported sorry).

I thought I have explained what I want. Everything is fine and working just fine except for the creating groups part. Bills post captured the end result of what I want. I don't know how else to say it.

And the set-up page works fine for putting the students names on the spreadsheets although I will admit it is slow since when you enter a students name on the set up page it is essentially putting that name in over 100 different cells.

And I don't know what you mean by my new problems create problems for past solutions. My past solutions are fine. They were solved and still are solved.

Re: Group Data With Changing Criteria

Here is the updated attachment with made up data in the cells.

Re: Group Data With Changing Criteria

Well then I don't know how to do that if I can't have formulas in the data.

I don't know whether a student will be high, low, or medium....until a score is imported for them. The formula then divides their score by points possible to determine what they are and returns high, med, low.

Also it is ridiculous to ask the teachers to enter the name of the students over and over and over for every exit card; for every test (on the test spreadsheet---not shown in sample). That is A LOT of times entering in the same names.

So I created a set-up page where the teacher enters in the names of their students grouped by periods, and then it automatically puts their names on all the sheets in ALL the spots.

So column one is referenced by points divided by points possible, and column two returns the student name referencing the set-up page.

It will return raw data, so that all that will be displayed is raw data, but the formula creates the data.

I am putting in data for you, but again it will be a formula in the cell that returns that data.

Re: Group Data With Changing Criteria

Okay I have attempted to layout the data as you requested. I put 2 exit cards and 3 periods on the sample sheet. (Keep in mind it will be 8 periods and 10 exit cards per unit with 5 units total on the spreadsheet).

I have already created every worksheet in this workbook except for the create groups page. Every worksheet is functioning the way I want with all the macros and codes working. If I reformat my data, I will need to change a lot of cell references in a lot of macros and sheet codes. Now do not misunderstand me: I do not mind doing so AT ALL if it will benefit the workbook and help me get the create groups page working. This is an awesome learning experience for me, and I am taking it as such. I will take whatever time it takes and however many times I have to recode and recode and recode until I have finished it because it is a need in the teaching profession, and I love to tackle new and big projects.

All I am asking is how vital is it that I do-over my entire layout considering everything is done and working except for the create groups page?

I have attached the sample. The first sheet is the layout as it stands currently the second sheet is my attempt to present the data in the way you asked. Thank you so much; you are such a big help. I definitely respect that.

Re: Group Data With Changing Criteria

I apologize in advance for my ignorance, but I don't see what you are referring to. I have all related data together.

I guess I do not understand what you mean by the terms raw data and data used for presentation. I am attempting to teach myself excel skills; I really have no prior knowledge, so these terms are foreign to me.

I read the excel best practices in the beginning as part of teaching myself. And for this reason, I put ALL related data together, which is why all the exit card data is on the same worksheet. (even though it covers 5 units and 8 periods).

How can I have all the headings in row one when I have 8 periods I have to put underneath each other? The periods contain the same data headings as the first period, but I need a way to separate the periods visually.

I have not ventured into pivot tables although I have read about them. If I am reading it correctly, which I possibly am not, the user manipulates the data with the pivot table. But I am creating a program for people even more excel illiterate than me, and they would not be able to do so. But again perhaps I am misunderstanding the functionality of a pivot table.