View Full Version : VBA - Help me to solve this one, makro who can select between 3 sheets
Headspinn
01-04-2018, 01:52 PM
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
jolivanes
01-04-2018, 02:45 PM
The "3", "2" and "1" are text values, without quotations they become numbers as in 3, 2, 1.
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
Headspinn
01-04-2018, 02:58 PM
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
Headspinn
01-04-2018, 03:49 PM
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....
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
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"
Headspinn
01-04-2018, 10:35 PM
Thank you SamT, i will check that after work today.
Headspinn
01-04-2018, 10:59 PM
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 :hi:
Headspinn
01-06-2018, 03:09 AM
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 :hi:
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
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
YasserKhalil
01-06-2018, 08:56 PM
@SamT
You can use Trim worksheet function instead
Sht.Name = Application.WorksheetFunction.Trim(Nm)
jolivanes
01-07-2018, 09:50 AM
@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.
Headspinn
01-11-2018, 07:54 AM
This is the last edited version, this one work as i want.:yes This Macro choose between 3 sheets depending on the number in cell "N20". And The cell "N20" is "numberic" and sheets name are" text". :bow:
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.