Cells(.Rows.Count, SampleName.Column).End(xlUp).Row Set QSResultFileWS = QSResultFileWB.Sheets("Results") Set QSResultFileWB = Workbooks.Open(ResultFile) Range("O10").Value = "Estimated Copy Number"įor Each ResultFile In FileToOpen '-Import Result Files (Start). Set ClearStartRange = FormattingWS.Range("D11:O" & ClearStartRangeLastRow) Set FormattingWS = ThisWorkbook.Sheets("OpenArray Raw Data")įileToOpen = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select all files needing analyzed", MultiSelect:=True) 'if file types change to csv or something else, this needs changedĬlearStartRangeLastRow =. PTC5Arr = Array("Proteus vulgaris", "Acinetobacter baumannii", "Klebsiella pneumoniae", "Pseudomonas aeruginosa", "Klebsiella oxytoca", "Enterobacter cloacae", _ "Escherichia coli", "Klebsiella aerogenes", "Coagulase-negative staphylococcus", "Candida albicans", "Xeno", "Proteus mirabilis", _ "Enterococcus faecium", "Morganella morganii", "Providencia stuartii", "Citrobacter freundii", "Streptococcus agalactiae", "Enterococcus faecalis", _ NTCArr = Array("Proteus vulgaris", "Acinetobacter baumannii", "Klebsiella pneumoniae", "Pseudomonas aeruginosa", "Klebsiella oxytoca", "Enterobacter cloacae", _ PEC1Arr = Array("Candida albicans", "Xeno")
MyPath = "C:\Users\j\OneDrive\Documents\Excel\UTM Open Array\Validation Files" ****** EDIT ****** Call OptimizeCode_Beginĭim FileToOpen As Variant, ResultFile As Variant, xRet As Boolean, Name As String, SDInterpretation As Integer, CrtThresholdCutoff#ĭim QSResultFileWB As Workbook, QSResultFileWS As Worksheet, FormattingWS As Worksheet, ImptPtInfo As Worksheet, PullReruns As Worksheetĭim SampleName As Range, QSTarget As Range, sampleArrayIK As Variant, sampleArrayDE As Variant, FormattingWBCrtLastRow As Long, FlaggedSpecimensLastRow As Longĭim CrtAverage As Range, FlaggedSpecimens As Range, FinalResult As Range, FirstTarget As Range, SecondTarget As Range, AccessionNumber As Rangeĭim FirstTargetCrtValue As Range, SecondTargetCrtValue As Range, CrtSDValue As Range, FinalCrt As Range, twenty As Integerĭim NameChange As Range, NameChangeMatch As Variant, ElastRow As Long, UlastRow As Long, PreConvertedNames As Range, SampleNameLastRow As Longĭim DEColumnRng As Range, IKColumnRng As Range, DLastRow As Long, QSNameMatch As Variantĭim Counter As Long, SampleNameStart As Range, TotalRows As Integer, QCProblems As Range, QCProblemsLastRow As Longĭim PostImportDLastRow As Long, EndPlateQSMatch As Variant, FindFullNameAndPosition As Rangeĭim myPath As String, XenoControl As String, ColumnCRerunsLastRow As Long, ColumnBRerunsLastRow As Longĭim ColumnBReruns As Range, ColumnCReruns As Range, RerunPatient As Range, RedBorderSearchRng As Rangeĭim PositiveExtractionControl As String, NegativeExtractionControl As String, NegativeTemplateControl As String, PositiveTemplateControl5 As Stringĭim NTCArr As Variant, PTC5Arr As Variant, PEC1Arr As Variant, NEC1Arr As Variantĭim DERange As Range, IJKRange As Range, r As Range, ClearStartRangeLastRow As Long, ClearStartRange As Rangeĭim SerialNumberRng As Range, OpenArraySerialNumber As String, PlateSerialNumber As Rangeĭim LowLow As String, Low As String, Moderate As String, High As String, HighHigh As String, EstCopyNumber As Rangeĭim PTC123Arr As Variant, PTC4Arr As Variant, PositiveTemplateControl2 As String, PositiveTemplateControl3 As String, PositiveTemplateControl4 As String, PositiveTemplateControl1 As String Not sure if this is helpful information, but these are being used at work so the PC's are networkedĪm I missing something? Any suggestions are greatly appreciated! I'm not sure what's going on with 2016 vs 365 but I've been reading around and all the factors I can find, pertain to either the operating system or the project references.
I took the same exact macro file and opened it on a PC that had Office 365 and the macro ran perfectly. I commented out erasing the arrays and tried again, and it gave me another mismatch error on the first if statement of my code.
They used the macro to import a file and encountered a Mismatch error, and the error line made no sense to me because it was just erasing a loaded array.
Yesterday someone tried using the macro on a different PC (Still Windows 10), but had Excel 2016. This entire time the macro has been created, edited, and used with office 365. I made a macro that others have been using the last 6 months with 0 unexpected errors until yesterday.