Meerdere "cases" seleceteren om verder te gaan

Status
Niet open voor verdere reacties.
K

kruimeltjes

Gebruiker
Lid geworden
30 sep 2009
Berichten
222
Beste Allemaal,

Ik ben bezig om mijn macro voor het sorten van gegevens makkelijker te maken.

1 van de dingen wat er moet gebeuren is nieuwe tabs (excel 2010) aanmaken en deze een naam geven. Als de naam hetzelfde zou blijven: geen problee dat krijg ik voor elkaar, maar mijn naam geven (Tx_naam) is afhankelijk van mij strABName wat ik eerder heb in gegeven.

Nu heb ik onderstaande codering staan, het bovenste stuk (tot het maken van de sheets en deze hernoemen) loopt als een trein. Het stuk daaronder wil die het niet doen. Iemand een idee wat ik verkeerd doe?

Code: 
Sub test()

Dim strABName As String, strISOName As String, strName1 As String, strName2 As String, strName3 As String, strVisit As String

'Enter the antibody that is used
strABName = InputBox("Enter antibody used (as saved in image name; e.g. 'Man106)", "Specify antibody", "Antibody")

'Rename Antibody name
 Select Case strABName
    Case "Man106"
        Columns("A:A").Select
            Selection.Replace "_Man106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "Mandys106"
        Columns("A:A").Select
            Selection.Replace "_Mandys106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "Mandys"
        Columns("A:A").Select
            Selection.Replace "_Mandys-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "M106"
        Columns("A:A").Select
            Selection.Replace "_M106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "m106"
        Columns("A:A").Select
            Selection.Replace "_m106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "AB15277"
        Columns("A:A").Select
            Selection.Replace "_AB15277-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "Ab15277"
        Columns("A:A").Select
            Selection.Replace "_ab15277-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "nNOS"
        Columns("A:A").Select
            Selection.Replace "_nNOS-", "_nNOS-", xlPart, xlByColumns, False, False, False
    Case "NOS"
        Columns("A:A").Select
            Selection.Replace "_NOS-", "_nNOS-", xlPart, xlByColumns, False, False, False
    Case Else
        Exit Sub
End Select

'Enter the isotype that is used
strISOName = InputBox("Enter isotype name used (as saved in image name; e.g. 'IgG2A)", "Specify antibody", "Isotype")

'Rename Antibody name
 Select Case strISOName
    Case "IgG2A"
        Columns("A:A").Select
            Selection.Replace "_IgG2A-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "IgG1"
        Columns("A:A").Select
            Selection.Replace "_IgG1-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "IgG"
        Columns("A:A").Select
            Selection.Replace "_IgG-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "ISO"
        Columns("A:A").Select
            Selection.Replace "_ISO-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "Iso"
        Columns("A:A").Select
            Selection.Replace "_Iso-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case Else
        Exit Sub
End Select

'Sort cells (Iso vs M106)
Cells.Select
ActiveWorkbook.Worksheets("ObjectStatistics_per_Cell").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ObjectStatistics_per_Cell").sort.SortFields.Add Key _
        :=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
            With ActiveWorkbook.Worksheets("ObjectStatistics_per_Cell").sort
                .SetRange Range("A1:BC10000")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

'Enter how many visits are used in this experiment
strVisit = InputBox("How many visit are used in this experiment?", "Specify numbe of visits", "1")

'Enter the visit numbers and replace for T1, T2 or T3
Select Case strVisit
    Case Is = "2":
        strName1 = InputBox("Enter name for first visit (e.g. 'V01)", "Specify T1", "Visit 1")
        strName2 = InputBox("Enter name for second visit (e.g. 'V02)", "Specify T2", "Visit 2")

            If strName1 = "Visit 1" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName1, "T1", xlPart, xlByColumns, False, False, False
            End If

            If strName2 = "Visit 2" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName2, "T2", xlPart, xlByColumns, False, False, False
            End If
    Case Is = "3":
        strName1 = InputBox("Enter name for first visit (e.g. 'V01)", "Specify T1", "Visit 1")
        strName2 = InputBox("Enter name for second visit (e.g. 'V02)", "Specify T2", "Visit 2")
        strName3 = InputBox("Enter name for third visit (e.g. 'V03)", "Specify T3", "Visit 3")

            If strName1 = "Visit 1" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName1, "T1", xlPart, xlByColumns, False, False, False
            End If

            If strName2 = "Visit 2" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName2, "T2", xlPart, xlByColumns, False, False, False
            End If

            If strName3 = "Visit 3" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName3, "T3", xlPart, xlByColumns, False, False, False
            End If
    Case Else
        Exit Sub
End Select

'Create new tabs ands rename them according to the name of the staining that has been done
If strVisit = 2 Then
    Sheets.Add After:=ActiveSheet, Count:=6
        Select Case strABName
            Case Is = "Man106" Or "Mandys106" Or "Mandys" Or "AB15277" Or "Ab15277"
                'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_Dys"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_Dys"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "Sheet5"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "Sheet6"
            Case Is = "nNOS" Or "NOS"
                'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_nNOS"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_nNOS"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "Sheet5"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "Sheet6"
            Case Else
                Exit Sub
        End Select
Else: strVisit = 3
    Sheets.Add After:=ActiveSheet, Count:=8
        Select Case strABName
            Case Is = "Man106" Or "Mandys106" Or "Mandys" Or "AB15277" Or "Ab15277"
                'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_Dys"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_Dys"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "T3_Iso"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "T3_Dys"
                    Sheets("Sheet7").Select
                    Sheets("Sheet7").Name = "Sheet7"
                    Sheets("Sheet8").Select
                    Sheets("Sheet8").Name = "Sheet8"
            Case Is = "nNOS" Or "NOS"
             'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_nNOS"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_nNOS"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "T3_Iso"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "T3_nNOS"
                    Sheets("Sheet7").Select
                    Sheets("Sheet7").Name = "Sheet7"
                    Sheets("Sheet8").Select
                    Sheets("Sheet8").Name = "Sheet8"
        End Select
End If
End Sub
 
Wijzig:
Case Is = "Man106" Or "Mandys106" Or "Mandys" Or "AB15277" Or "Ab15277"

Eens in:
Case "Man106", "Mandys106", Mandys", "AB15277", "Ab15277"

De andere kun je dan zelf wel verzinnen.
 
Laatst bewerkt:
Super dank je ! Werkt helemaal!

Voor alle volledigheid hier dan de volledige code:

Code: 
Sub test()

Dim strABName As String, strISOName As String, strName1 As String, strName2 As String, strName3 As String, strVisit As String

'Enter the antibody that is used
strABName = InputBox("Enter antibody used (as saved in image name; e.g. 'Man106)", "Specify antibody", "Antibody")

'Rename Antibody name
 Select Case strABName
    Case "Man106"
        Columns("A:A").Select
            Selection.Replace "_Man106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "Mandys106"
        Columns("A:A").Select
            Selection.Replace "_Mandys106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "Mandys"
        Columns("A:A").Select
            Selection.Replace "_Mandys-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "M106"
        Columns("A:A").Select
            Selection.Replace "_M106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "m106"
        Columns("A:A").Select
            Selection.Replace "_m106-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "AB15277"
        Columns("A:A").Select
            Selection.Replace "_AB15277-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "Ab15277"
        Columns("A:A").Select
            Selection.Replace "_ab15277-", "_M106-", xlPart, xlByColumns, False, False, False
    Case "nNOS"
        Columns("A:A").Select
            Selection.Replace "_nNOS-", "_nNOS-", xlPart, xlByColumns, False, False, False
    Case "NOS"
        Columns("A:A").Select
            Selection.Replace "_NOS-", "_nNOS-", xlPart, xlByColumns, False, False, False
    Case Else
        Exit Sub
End Select

'Enter the isotype that is used
strISOName = InputBox("Enter isotype name used (as saved in image name; e.g. 'IgG2A)", "Specify antibody", "Isotype")

'Rename Antibody name
 Select Case strISOName
    Case "IgG2A"
        Columns("A:A").Select
            Selection.Replace "_IgG2A-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "IgG1"
        Columns("A:A").Select
            Selection.Replace "_IgG1-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "IgG"
        Columns("A:A").Select
            Selection.Replace "_IgG-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "ISO"
        Columns("A:A").Select
            Selection.Replace "_ISO-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case "Iso"
        Columns("A:A").Select
            Selection.Replace "_Iso-", "_Iso-", xlPart, xlByColumns, False, False, False
    Case Else
        Exit Sub
End Select

'Sort cells (Iso vs M106)
Cells.Select
ActiveWorkbook.Worksheets("ObjectStatistics_per_Cell").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ObjectStatistics_per_Cell").sort.SortFields.Add Key _
        :=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
            With ActiveWorkbook.Worksheets("ObjectStatistics_per_Cell").sort
                .SetRange Range("A1:BC10000")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

'Enter how many visits are used in this experiment
strVisit = InputBox("How many visit are used in this experiment?", "Specify numbe of visits", "1")

'Enter the visit numbers and replace for T1, T2 or T3
Select Case strVisit
    Case Is = "2":
        strName1 = InputBox("Enter name for first visit (e.g. 'V01)", "Specify T1", "Visit 1")
        strName2 = InputBox("Enter name for second visit (e.g. 'V02)", "Specify T2", "Visit 2")

            If strName1 = "Visit 1" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName1, "T1", xlPart, xlByColumns, False, False, False
            End If

            If strName2 = "Visit 2" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName2, "T2", xlPart, xlByColumns, False, False, False
            End If
    Case Is = "3":
        strName1 = InputBox("Enter name for first visit (e.g. 'V01)", "Specify T1", "Visit 1")
        strName2 = InputBox("Enter name for second visit (e.g. 'V02)", "Specify T2", "Visit 2")
        strName3 = InputBox("Enter name for third visit (e.g. 'V03)", "Specify T3", "Visit 3")

            If strName1 = "Visit 1" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName1, "T1", xlPart, xlByColumns, False, False, False
            End If

            If strName2 = "Visit 2" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName2, "T2", xlPart, xlByColumns, False, False, False
            End If

            If strName3 = "Visit 3" Or strName1 = vbNullString Then
                Exit Sub
                Else
                    Columns("A:A").Select
                        Selection.Replace strName3, "T3", xlPart, xlByColumns, False, False, False
            End If
    Case Else
        Exit Sub
End Select

'Create new tabs ands rename them according to the name of the staining that has been done
If strVisit = 2 Then
    Sheets.Add After:=ActiveSheet, Count:=6
        Select Case strABName
            Case "Man106", "Mandys106", "Mandys", "AB15277", "Ab15277"
                'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_Dys"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_Dys"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "Sheet5"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "Sheet6"
            Case "nNOS", "NOS"
                'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_nNOS"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_nNOS"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "Sheet5"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "Sheet6"
            Case Else
                Exit Sub
        End Select
Else: strVisit = 3
    Sheets.Add After:=ActiveSheet, Count:=8
        Select Case strABName
            Case "Man106", "Mandys106", "Mandys", "AB15277", "Ab15277"
                'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_Dys"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_Dys"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "T3_Iso"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "T3_Dys"
                    Sheets("Sheet7").Select
                    Sheets("Sheet7").Name = "Sheet7"
                    Sheets("Sheet8").Select
                    Sheets("Sheet8").Name = "Sheet8"
            Case "nNOS", "NOS"
             'Rename sheets
                    Sheets("Sheet1").Select
                    Sheets("Sheet1").Name = "T1_Iso"
                    Sheets("Sheet2").Select
                    Sheets("Sheet2").Name = "T1_nNOS"
                    Sheets("Sheet3").Select
                    Sheets("Sheet3").Name = "T2_Iso"
                    Sheets("Sheet4").Select
                    Sheets("Sheet4").Name = "T2_nNOS"
                    Sheets("Sheet5").Select
                    Sheets("Sheet5").Name = "T3_Iso"
                    Sheets("Sheet6").Select
                    Sheets("Sheet6").Name = "T3_nNOS"
                    Sheets("Sheet7").Select
                    Sheets("Sheet7").Name = "Sheet7"
                    Sheets("Sheet8").Select
                    Sheets("Sheet8").Name = "Sheet8"
        End Select
End If
End Sub
 
Haal svp al die overbodige 'selects' weg.


Dit vervangt 31 regels

Code: 
      c00=InputBox("Enter antibody used (as saved in image name; e.g. 'Man106)", "Specify antibody", "Antibody")
      columns(1).replace("_"& c00 &"-",iif(instr(c00, "NOS"),"_nNOS-","_M106-"), xlPart
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan