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
    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
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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
    Should look something like...
    ActiveSheet.Range("N" & 20).Value= 3 Then
    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

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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 Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    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
    See help on "CLng" and "CInt"
    Last edited by SamT; 01-04-2018 at 05:33 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    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 Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    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)

  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
  •