So this version doesn't work for you?
Posts by rory
-
-
-
It worked for me doing that.
-
If you do that, you could simplify the item formula to just:
=Pass/(Pass+Fail)
since any errors will now be hidden anyway
-
-
Which version of Excel, do you have to work with? MAXIFS was introduces with 2019 I think, so I'm guessing you have something older?
Try:
=MAX(IF(($M$2:$M$1000>=$E2)*($M$2:$M$1000<=EDATE(E2,1)),$B$2:$B$1000))
array entered using Ctrl+Shift+Enter, not just enter.
-
Glad we could help.
-
You should be able to reset them by deleting the Dock entry in the registry at HKEY_CURRENT_USER\Software\Microsoft\VBA\7.1\Common
-
Untested, but try this:
Code
Display MoreSub CopyFormulasToSelectedSheets() Dim formulaRange As Range Dim ws As Worksheet Dim wb As Workbook Dim folderPath As String Dim fileName As String Application.ScreenUpdating = False ' Define the range with formulas to copy Set formulaRange = ThisWorkbook.Sheets("1st").Range("N7:Q78").SpecialCells(xlCellTypeFormulas) ' Specify the folder path containing the workbooks folderPath = "C:\Users\Selorm\Desktop\CH-GROUP-4 FILES\AUDIT3\RAW-DATA-FROM-STATIONS\ABETIFI\" ' Loop through each workbook in the folder fileName = Dir(folderPath & "*.xlsx") Do While fileName <> "" Set wb = Workbooks.Open(folderPath & fileName) ' Loop through selected sheets in the workbook For Each ws In wb.Sheets(Array("1st", "2nd", "3rd", "4th", "5th", "6th", "7th", "8th", "9th", "10th", "11th", "12th", _ "13th", "14th", "15th", "16th", "17th", "18th", "19th", "20th", "21st", "22nd", "23rd", _ "24th", "25th", "26th", "27th", "28th", "29th", "30th", "31st")) For Each area In formulaRange.Areas ws.Range(area.Address).Formula = area.Formula Next area Next ws wb.Close SaveChanges:=True fileName = Dir Loop Application.ScreenUpdating = True End Sub
-
This thread is over 18 years old, so I suggest you start your own!
-
Since you have 365, you could also use a formula - see attached.
FYI, pecoflyer's solution really just needs amending to split on a linefeed rather than the hyphen.
-
PS You might be able to get something working by adding doevents inside y our loop, but it would probably be a bit clunky.
-
VBA is single threaded. You can't run one code while another is running.
-
If memory serves, the commondialog control is not 64bit compatible.
-
-
-
-
Just add 1:
=IF(C124>TODAY(),1,EOMONTH(TODAY(),0)-TODAY()+1)
-
You could do something like this:
Code
Display MoreSub PrintPDF_Button_Click() Dim saveName saveName = Application.GetSaveAsFilename( _ FileFilter:="PDF Files (*.pdf), *.pdf") If saveName <> False Then Sheets(Array("Sheet1", "Sheet2")).Select On Error Resume Next ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveName, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, openafterpublish:=True If Err.Number <> 0 Then MsgBox "Error occurred during save." & vbLf & _ "Please make sure the pdf is not currently open, then try again." End If End Sub
-
Unfortunately, unlike most filedialogs, the save as one doesn't really allow you to restrict the file types to just the one you might want. I'd suggest you use an alternative approach like:
Code
Display MoreSub PrintPDF_Button_Click() Dim saveName saveName = Application.GetSaveAsFilename( _ FileFilter:="PDF Files (*.pdf), *.pdf") If saveName <> False Then Sheets(Array("Sheet1", "Sheet2")).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveName, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, openafterpublish:=True End If End Sub