PDA

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.

Dave
01-04-2018, 02:54 PM
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....

Dave
01-04-2018, 05:19 PM
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

SamT
01-04-2018, 05:22 PM
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

SamT
01-06-2018, 10:00 AM
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