How to get all possible permutation in VBA
Hi, We all know Benjamin Franklins Square. How can I Get VBA to export a spreadsheet with all possible permutations for numbers 1-9. Filter out duplicates of the same number Filter out only combinations that the sum matches the criteria below First 3 digits = 15 Second 3 digits = 15 Third 3 digits = 15 1st, 4th, 7th = 15 2nd, 5th, 8th = 15 3rd, 6th, 9th = 15 1st, 5th, 9th = 15 3rd, 5th, 7th = 15 I hope you are following what I'm trying to do. Thanks in advance, Uncle Bear
EXCEL Crashes while opening External Data connections workbook
Hi, We have a custom COM Add-in for Excel, and it connects to an external data connection. While opening the custom workbook, the connection was established successfully, and data was refreshed. But, Excel crashed immediately without any error message. Checked event viewer logs and found the error below: Using the link below, I reverted the MS Office to an earlier version (16.0.19029.20136) from the recent release on my laptop. After reverting the Excel, the issue was resolved. But I don't want to turn off MS Office automatic updates. https://support.microsoft.com/en-us/topic/how-to-revert-to-an-earlier-version-of-office-2bd5c457-a917-d57e-35a1-f709e3dda841#:%7E:text=Revert%20to%20the%20previous%20version%20of%20Office%201,3%20Step%203%3A%20Disable%20automatic%20updates%20for%20Office Please suggest any other resolution to resolve the issue. Are there any known issues in the recent release (16.0.19231.20216)?
Hide Sheet after missusing shortcut Alt+H+W
I was working on an inventory and frequently using the shortcut mentioned above. Unfortunately, during one of those times, I must have misused it, and my sheet became hidden. Since then, I’ve tried multiple methods to unhide it, but none have worked. I would really appreciate your assistance in resolving this issue.
Excel VBA Updates not functioning as intended
Afternoon all, Attaching a deidentified copy of a metrics file. I built a macro which does two main things once a user clicks the "Update Sheet" icon in the ribbon. asks for a date in the format "MM/DD/YYYY" Compares the date entered to the format requirement, & if appropriately formatted, proceeds to make updates to the two tabs of the sheet. As a safeguard, the macro also checks if the entered date is found in Data tab, row 2. If yes, a msgbox should notify the user of the existence of the date, and exit sub. However with a user entered date which already exists, my macro continues on to make updates of inserting a new column C on Data tab, as well as inserting a new row 2 on Slide Prep tab. I've checked all the formatting and can't find why it doesn't find a match. Also, on the Slide Prep tab it when inserting new formulas, the formulas added to I2:M2 all present as text, and do not auto calculate - again, nothing i can locate as the root source of the surprise. I've attached a copy with the incorrect duplication of 10/01/2025 as two columns on Data tab, and as two rows on Slide Prep tab. VBA Code below: Option Explicit Sub CMLUpdateV2() Dim Wb As Workbook Dim Ws As Worksheet Dim ICount As Integer Dim iRow As Integer Dim Row As Object Dim RowAddress As String Dim RowRange As String Dim NM As String Dim DR As Range 'Data Date Tab Date Row(2) Dim DC As Range 'Date Found Range Dim strDateInput As String Dim blnValidDate As Boolean Application.ScreenUpdating = False For Each Wb In Application.Workbooks If Not (Wb Is Application.ThisWorkbook) Then Wb.Close End If Next blnValidDate = False 'Determine if input value is valid format, re-enter if not Do strDateInput = InputBox("Please Enter File Submission Month as MM/DD/YYYY:", "Date Entry") 'If strDateInput = "" Then ' MsgBox "No Date Entered, update cancelled.", vbInformation ' Exit Sub ' End If If IsDate(strDateInput) Then If Format(CDate(strDateInput), "MM/DD/YYYY") = strDateInput Then blnValidDate = True Else MsgBox "Invalid Date Format or No Date Entered. Please Re-Enter in MM/DD/YYYY format." blnValidDate = False End If Else MsgBox "Invalid Date Format or No Date Entered. Please Re-Enter in MM/DD/YYYY format." blnValidDate = False End If Loop Until blnValidDate NM = strDateInput 'Determine if valid input is already present on sheet Set DR = ThisWorkbook.Worksheets("Data").Range("2:2") Set DC = DR.Find(What:=NM, LookIn:=xlValues, LookAt:=xlWhole) If Not DC Is Nothing Then MsgBox "Date entered is already present. Please Review Date.", vbOKCancel Exit Sub Else 'Update Data Tab with new column and formula updates With ThisWorkbook.Worksheets("Data") .Columns("C:C").EntireColumn.Insert .Range("D:D").Copy .Range("C:C").PasteSpecial xlPasteAll .Range("C2").Value = NM .Range("C3:C6,C16:C19,C29:C32,C42:C45,C55:C58,C68:C71").ClearContents .Columns("O:O").Copy .Columns("O:O").PasteSpecial xlPasteValues End With 'Update Slide Prep Tab with New row and formula updates With ThisWorkbook.Worksheets("Slide Prep") .Rows("2:2").EntireRow.Insert .Range("A2").Value = NM .Range("A2").NumberFormat = "m/d/yyyy" .Range("B3:H3").Copy .Range("B2:H2").PasteSpecial xlPasteAll .Range("B2").Formula = "=XLOOKUP(B$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("C2").Formula = "=XLOOKUP(C$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("D2").Formula = "=XLOOKUP(D$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("E2").Formula = "=XLOOKUP(E$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("F2").Formula = "=XLOOKUP(F$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("G2").Formula = "=XLOOKUP(G$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("H2").Formula = "=XLOOKUP(H$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("I2").Formula = "=XLOOKUP(I$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("J2").Formula = "=XLOOKUP(J$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("K2").Formula = "=XLOOKUP(K$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("L2").Formula = "=XLOOKUP(L$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("M2").Formula = "=XLOOKUP(M$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Rows("14:14").EntireRow.Copy .Rows("14:14").EntireRow.PasteSpecial xlPasteValues End With MsgBox "New File Date " & NM & " added and sheets updated. Proceed to Data Tab to record Enrollment Line Counts." End If End Sub
VBA trouble with Export as Fixed Format
Hi folks - I'm struggling to get this code for export as fixed format to work. My aim is to use it to churn through 150 names in a dropdown (data validation using a reference to a named range called "Names") and print PDF earning statements for each person. I have the file stored on Sharepoint and when I run it there it seems to churn through the names, but not produce the PDFs (at least not that I can find). When I move the file to my local device (Mac), it throws an error and highlights this part as the issue. Any help is most appreciated. I'd also love to learn how to fire this from the version hosted on Sharepoint, but from this article it seems I'll need some more VBA. Sub PrintAllVariablePayoutPDFs() Dim DropDown As Range 'xRg Dim IndivName As Range Dim NamesList As Range 'List of COMPANY Employees Dim PDFName As String Dim PDFPath As String Dim FullPath As String ' Turn off screen updating Application.ScreenUpdating = False ' Set where dropdown resides Set DropDown = Range("G2") ' Set where list of names resides Set NamesList = Evaluate(DropDown.Validation.Formula1) ' Set PDF path every quarter PDFPath = "/Users/dominiccronshaw/Library/CloudStorage/OneDrive-COMPANY/GTM Operations/Commissions/2025 Q3/Payout PDFs/" 'Set PDFName PDFName = Range("K4").Value ' Construct the full file path ' FullPath = PDFPath & PDFName & ".pdf" 'Steps through options in the dropdown with people's names For Each IndivName In NamesList DropDown = IndivName.Value 'Prints each sheet as PDF with unique file name ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ FileName:=PDFName, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False Next 'Turn on screen updating Application.ScreenUpdating = True End SubSolver in Excel - Variable Limitation
Hi All, I built a model in Excel to solve 1d Cutlist optimization, model works great and gives optimal results But when there is complex cutting patterns that results in more than 250 cutting combinations model totally fails fails due to the limitation of solver to handle more than 250 variables with integer constraints. using simplex LP, I created model years ago but unable to use it Hoping Microsoft has this in the list to fix this limitation in coming versions ?
Excel/VBA Worksheet_Change function running old version
I wrote a Worksheet_Change function initially. It worked. If I entered an incorrect value I got an error message, let's say the msgbox said "Error: A". Then I changed the error message to read "Error B". No matter what I have tried to do, rename the sheet, Debug>Compile VBA Project, etc, nothing can remove the old error message from appearing. I have tried every suggestion Copilot has made without success. I rebooted my machine. I am using Microsoft Office for Home and Student 2021 running on Windows 11 Pro. Does anyone know what might be the matter? Microsoft chat was totally useless and I am pretty sure Copilot is masquerading as a human in chat support because the wording and suggestions mirrored exactly what I encountered with Copilot, so that was a giant waste of my time. If there's anyone who can help, I would welcome hearing from you with suggestions.
