New sheet -> Choose it

Question

Sorry for posting a lot of questions, i found out now, what my sub should do.

I want a sub which CountIf a lot of different variables, and save these. Then it should check if a sheet named "Statistics" already exists. If it does, it should ask "Do you want to overwrite" with yes/no. If it does not already exist, it should create it, and put in the information in the "Statistics" sheet.

It's supposed to be in 1 sub, but i made it in 2 for a start to not get confused - But i cant seem to get my Sheet-checker to work.

Is it possible for one of you to put my 2 subs into 1 sub, and maybe explain what i should do?

Thank you so much

Sub1

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets("Statistics")
 If Not ws Is Nothing Then
MsgBox "A Sheet named 'Statistics' already exists"
Else
MsgBox "'Statistics'-sheet does not already exist"
End If
      If Err.Number = 9 Then
        ans = MsgBox("The file, " & save_name & " exists, do you want to overwite it?", vbYesNo + vbQuestion)
            Select Case ans
        Case vbYes
            'overwrite the file
            wb.SaveAs FileName:=WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name, FileFormat:=51
        Case vbNo
            'exit
            MsgBox "Ensure the data is correct." & vbCrLf & "The process will end.", vbOKOnly + vbExclamation
            Exit Sub
        End Select

        Set ws = Worksheets.Add(After:=Sheets(Worksheets.Count))
        ws.Name = "Statistics"

    End If
    With ws
        'do stuff
    End With
End Sub

Sub2

Sub Opgave3Dim()

'Dim'er de forskellige fakulteter 1-5 som Long
Dim information1 As Long
Dim information2 As Long
Dim information3 As Long
Dim information4 As Long
Dim information5 As Long

'Sætter informationerne til et eller 2 af tallene hvor 2*x = Kandidat, 1*x = Bachelor
information1 = WorksheetFunction.CountIfs(Columns("I"), "1", Columns("K"), "Bachelor")
information11 = WorksheetFunction.CountIfs(Columns("I"), "1", Columns("K"), "Master")
information2 = WorksheetFunction.CountIfs(Columns("I"), "2", Columns("K"), "Bachelor")
information22 = WorksheetFunction.CountIfs(Columns("I"), "2", Columns("K"), "Master")
information3 = WorksheetFunction.CountIfs(Columns("I"), "3", Columns("K"), "Bachelor")
information33 = WorksheetFunction.CountIfs(Columns("I"), "3", Columns("K"), "Master")
information4 = WorksheetFunction.CountIfs(Columns("I"), "4", Columns("K"), "Bachelor")
information44 = WorksheetFunction.CountIfs(Columns("I"), "4", Columns("K"), "Master")
information5 = WorksheetFunction.CountIfs(Columns("I"), "5", Columns("K"), "Bachelor")
information55 = WorksheetFunction.CountIfs(Columns("I"), "5", Columns("K"), "Master")

End Sub

All your `Columns` inside the `WorksheetFunction.CountIfs` are not qualified with a worksheet, it should be `Sheets("YourSheetName").Columns("I")` , just change the sheet's name according to your sheet — Shai Rado, Apr 15 '18 at 07:42
Could you provide me with an example? I dont understand where to put what you just wrote? :) — Ashreen Ali, Apr 15 '18 at 07:57
what is the source of data for the `CountIfs` functions ? `Columns("I")` belongs to which what sheets name ? — Shai Rado, Apr 15 '18 at 07:58
Then use `information1 = WorksheetFunction.CountIfs(Worksheets("Base").Columns("I"), "1", Worksheets("Base").Columns("K"), "Bachelor")`, etc - or, even better, use the whatever the VBA object name for that Worksheet is, such as `information1 = WorksheetFunction.CountIfs(wsBase.Columns("I"), "1", wsBase.Columns("K"), "Bachelor")` in case the worksheet gets renamed :P Also, is `, "1",` supposed to be text, or a number (if the latter, lose the quotes: `, 1,` — Chronocidal, Apr 15 '18 at 08:40

score 1 · Answer 1 · answered Apr 15 '18 at 08:23

As Shai mentioned, fully qualify your objects else they will refer to the active sheet and the active sheet may not be the one which you actually want.
Do not declare so many variables. You can use an array in your case and then use a loop to fill them up.
Avoid the use of .Select. You may want to see How to avoid using Select in Excel VBA

Is this what you are trying? (Untested)

Sub Opgave3Dim()
    Dim InfoAr_A(1 To 5) As String
    Dim InfoAr_B(1 To 5) As String

    Dim i As Long
    Dim ws As Worksheet, wsNew As Worksheet

    '~~> Change this to the sheet where you want to do Countifs
    '~~> I have moved this before you add the sheet because if
    '~~> you do not qualify your range, your code will refer to
    '~~> the new sheet
    Set ws = Sheet1

    With ws
        For i = 1 To 5
            InfoAr_A(i) = Application.WorksheetFunction.CountIfs(.Columns("I"), i, .Columns("K"), "Bachelor")
            InfoAr_B(i) = Application.WorksheetFunction.CountIfs(.Columns("I"), i, .Columns("K"), "Master")
        Next i
    End With

    '~~> Use this to check what is stored in the array
    'For i = 1 To 5
    '    Debug.Print InfoAr_A(i)
    '    Debug.Print InfoAr_B(i)
    'Next i

    '~~> Add a new sheet
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Statistics"
        Set ws = ActiveSheet
    End With

    Sheets(2).Range("I17").Value = "aaa"
End Sub

Maybe :) and hence I left ot at `'~~> Change this to the sheet where you want to do Countifs...` — Siddharth Rout, Apr 15 '18 at 08:28

New sheet -> Choose it

1 Answers1