Posts by JonathanVH

    But I did not infer that column B did not have formulae; i.e., "compare Col A to Col B" is comparing two different animals.


    Using arrays (even five of them) like this is still much faster than iterating through range objects.

    I didn't really want to do this, but try replacing your code after the End If line and before the PROD.Save line with something like this:

    Code
    PROD.Sheets("Schedules").Range("SchedRecords").ClearContents
    SCHED.Sheets("4-Prod S&Op").Range("D_ProdRecords").Copy
    PROD.Sheets("Schedules").Range("A5").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False


    The code with all the Selects is obviously from the macro recorder, which writes bad code for this sort of thing. ;)

    You didn't mention that column A also holds formulae and column B does not.

    That will overwrite your formulas, so I doubt that you actually want to do that. If so, this will be faster:

    So are you now okay with the zeros in your table in Word? If not, you could make your formula do blanks, e.g.,

    Code
    =IF(INDEX('RISKS Import'!$A$1:'RISKS Import'!$H$77,K16,$N$2)=0,"",INDEX('RISKS Import'!$A$1:'RISKS Import'!$H$77,K16,$N$2))

    Do these formulas evaluate to an empty string("") or to zero but your sheet (Options, Advanced, Display options for this worksheet) or cell number format is set to not display zeros? Using xlPasteValues with formulas that evaluate to "" leaves the copied formula cells empty.

    If the stored procedure updates data but does not return a recordset, then don't use a recordset.

    Code
    con.Open "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";User ID=" & UserID & ";Password=" & Password & ";Trusted_Connection=no"
    With cmd
      .ActiveConnection = con
      .CommandTimeout = 0
      .CommandText = StoredProcedure
      .Execute , Array(SP_Param1, SP_Param2), adCmdStoredProc + adExecuteNoRecords
    End With
    Set cmd = Nothing
    SQL
    SELECT COUNT(ID)
    FROM
    (SELECT ID
     FROM YourTable
     GROUP BY ID
     HAVING COUNT(DISTINCT GameCode) = 5) AS x;


    For code to get one result set showing a row for each number of games, I would need to know what variant of SQL you are using, e.g., MySQL, because the available methods vary.

    As you want to transpose the existing data in place, select the cell with "RECRUITER" and run this macro:

    I'm not sure if your Combo Box is named ComboBox1 or Län1; if it's named Län1, then change "ComboBox1" below to that. I'm also not sure what you mean by "change value," but this should give you the idea:

    Code
    Sub IterateComboBox()
    Dim i As Long
      With Sheet1.ComboBox1
        For i = 0 To .ListCount - 1
    Debug.Print .List(i)
        Next
      End With
    End Sub