Consulting

Results 1 to 14 of 14

Thread: VBA - Help me to solve this one, makro who can select between 3 sheets

  1. #1

    VBA - Help me to solve this one, makro who can select between 3 sheets

    Can anyone see why a get a " time error 9 " when a try to activate the last sheet whit selection "1" ?
    Sub TEST() '
         ' TEST Makro
         
        If Range("N20") = "3" Then 
            Sheets("Sammenligning pf og pg").Select 
        ElseIf Range("N20") = "2" Then 
            Sheets("Sammenligning s og pg").Select 
        ElseIf Range("N20") = "1" Then 
            Sheets("Sammenligning s og pf").Select 
             
             
        End If 
         
    End Sub 
    
    
    Formatting tags added by mark007
    Last edited by SamT; 01-04-2018 at 05:09 PM. Reason: Formatted Code via Hash Tag icon

  2. #2
    The "3", "2" and "1" are text values, without quotations they become numbers as in 3, 2, 1.

  3. #3
    Not sure why you are selecting anything... it's usually not necessary. There is no activate sheet code? Is range N20 numeric or text? Anyways, the likely error is that U are not specifying the sheet where the test range came from along with the numeric evaluation...
    If Range("N20") = "3" Then 
    
    
    Formatting tags added by mark007
    Should look something like...
    ActiveSheet.Range("N" & 20).Value= 3 Then 
    
    
    Formatting tags added by mark007
    HTH. Dave

  4. #4
    Okey, but when i choose "2" and "3" in the cell "N20" then it moves to the sheets like i want, but only problem when i choose "1"...

    This part i working.....

    If Range("N20") = "3" Then
    Sheets("Sammenligning pf og pg").Select
    ElseIf Range("N20") = "2" Then
    Sheets("Sammenligning s og pg").Select

  5. #5
    Here's my work. maybe you understand what i have done .. of course is with Norwegian text.
    Press " ANDRE TESTER" and play with the sheet "Sammenligningtest", choose different tires to comparing test.
    The only test with error is the test "1" (s+pf)

    I surely will solve this problem my self in time, but this is a school work i have to deliver at Sunday night, so i dont have time enough....
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  6. #6
    I'm pretty sure we're not here to do your school work for you. It's apparent that you don't want to use the help that you are given anyways. Good luck. Dave

  7. #7
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,311
    Location
    I would first look at spelling. Copy the name from the Sheet Tab or from the Properties Window in the VBA editor, then paste it into the code.

    Make sure that the cell "N20" is actually formatted as Text when there is a 1 in it.

    Try converting Range("N20")) to a number value in you code before testing it. Then it won't matter if C20 is Text or numbers.
    Personally, I would use a Variable to store the worksheet for use elsewhere

    Dim Ws As Worksheet 
     
    If (Convert To number(Range C20)) = 1 Then 
        Set Ws = the appropriate sheet 
    ElseIf... 
    ElseIf... 
    End If 
    
    
    Formatting tags added by mark007
    See help on "CLng" and "CInt"
    Last edited by SamT; 01-04-2018 at 05:33 PM.
    Please take the time to read the Forum FAQ

  8. #8
    Thank you SamT, i will check that after work today.��

  9. #9
    I understand, I will look at the different suggestion from everyone here and work with this after work to day and trough the weekend...This is a side course at school, so we have just learn some basic with VBA. My Project is my own and i try to do things and learning in the same time...so thanks for all the help so long

  10. #10
    Solved! I copy the text like SamT suggested and change the order. to (1,2,3), not (3,2,1) now it works !! Thanks for help

    Sub TEST()

    '
    ' TEST Makro

    If Range("N20") = "1" Then
    Sheets("Sammenligning s og pg").Select
    ElseIf Range("N20") = "2" Then
    Sheets("Sammenligning s og pg").Select
    ElseIf Range("N20") = "3" Then
    Sheets("Sammenligning pf og pg").Select


    End If

    End Sub

  11. #11
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,311
    Location
    A spelling difference, especially a leading or trailing Space, is a frequent problem in the VBA projects I review/troubleshoot.
    So frequent that I recommend keeping this code in your "Personal" Workbook
    Public Sub TrimSheetNames() 
        Dim Sht As Object 
        Dim Nm As String 
         
        For Each Sht In Sheets 
            Nm = Sht.Name 
            Nm = Replace(Nm, "  ", " ") 'Note: Doesn't "Fix" triple spaces. Run Code twice if Triples are suspected
            Sht.Name = Trim(Nm) 
        Next 
    End Sub 
    
    
    Formatting tags added by mark007
    Please take the time to read the Forum FAQ

  12. #12
    @SamT
    You can use Trim worksheet function instead
    Sht.Name = Application.WorksheetFunction.Trim(Nm) 
    
    
    Formatting tags added by mark007

  13. #13
    @YasserKhalil
    By the looks of it, SamT uses the Trim statement. Just Trim won't have any effect on double spaces between two words though while SamT's code does.

  14. #14
    This is the last edited version, this one work as i want. This Macro choose between 3 sheets depending on the number in cell "N20". And The cell "N20" is "numberic" and sheets name are" text".


    Sub Sammen()

    '
    ' Sammen Makro 'Flytter bruker til ønsket sammenligningstest
    ' ut fra valg av dekktyper.

    If Range("N20") = 1 Then
    Sheets("SogPF").Select
    ElseIf Range("N20") = 2 Then
    Sheets("SogP").Select
    ElseIf Range("N20") = 3 Then
    Sheets("PogPF").Select


    End If

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •