VBA requirement
Hi everyone, I’ve written a VBA script to dynamically retrieve and display data based on user interactions in an Excel workbook. The script triggers when I select a cell in Sheet1 (which contains product names) and performs the following tasks: Searches for the selected product in Sheet2, retrieves its category and price. Looks for matches in Sheet3 based on the category or price. Copies matching rows from Sheet3 into Sheet4, grouping results by category, and organizes them in separate column groups (e.g., Columns A-J for one category, K-T for the next, etc.). Additionally, the script clears previous results in Sheet4 and displays a message if no matches are found. What I Need Help With: Optimizing the script to handle larger datasets more efficiently. Suggestions for improving readability and maintainability of the code. Advice on handling edge cases or potential bugs I might have missed. Any recommendations to enhance the functionality or logic. I’d really appreciate your feedback and suggestions! Let me know if you need more details about the logic or the structure of my workbook. Thanks in advance! 😊
Improve performance of VBA macro
Hi, What can I do to improve the performance of the attached macro? The different sections of the code ('LOOP THROUGH SUBJECTS FOR SCREENING TEST CASE', 'LOOP THROUGH SUBJECTS FOR C1D1 TEST CASE', 'LOOP THROUGH SUBJECTS FOR C1D8 TEST CASE', 'LOOP THROUGH SUBJECTS FOR C2D1 TEST CASE' and 'LOOP THROUGH SUBJECTS FOR END OF TREATMENT TEST CASE') are all a little different in nature. So it's not possible to combine them in one loop. Thank you for your help! Both the code of the macro, and the spreadsheet on which I'm running the macro, are attached.
Dax measure not considering the filter context and not aggregating
Hi, I have the below visual and highlighted Dax is having problem: Below is Dax: noofdays = COUNTROWS(Calendar_) For the below selected filters, noofdays dax should display 60 for month of September 2024 and 62 for month of October 2024. The total aggregate value should be 122 days. But this dax is giving wrong values now. FYI, when I bring in DBName-Points_Id into the visual,it is giving correct values row by row but when removed it doesn't: FYR, My expected output should be like below: PFA file here Portfolio Performance - v2.15 (1).pbix Please let me know if you need further info! Thanks in advance! SergeiBaklan
VBA Code: ensuring users fill out specific cells
Hi all, I'm looking for some help with the following: My team has a excel spreadsheet with a table in it. This spreadsheet gets passed around different users as they each need to fill in a row with their data. The problem is, not all users are filling in the "mandatory" columns. So, when I open up the document after everyone has supposedly filled it in, I see blanks where I should see data. I have used CoPilot to help write a VBA code that basically stops the document from being closed or saved until a row in the table has been filled in: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Not IsTableComplete() Then MsgBox "You must complete at least one row in the table before saving.", vbCritical, "Incomplete Data" Cancel = True End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If Not IsTableComplete() Then MsgBox "You must complete at least one row in the table before closing.", vbCritical, "Incomplete Data" Cancel = True End If End Sub Function IsTableComplete() As Boolean Dim ws As Worksheet Dim tbl As ListObject Dim row As ListRow Dim isComplete As Boolean ' Set the worksheet and table name (update "Sheet1" and "Table1" to match your spreadsheet) Set ws = ThisWorkbook.Worksheets("Sheet2") Set tbl = ws.ListObjects("Table14") isComplete = False ' Default to incomplete ' Check if any row is fully filled out For Each row In tbl.ListRows Dim allFilled As Boolean allFilled = True ' Assume the row is complete Dim cell As Range For Each cell In row.Range If isEmpty(cell.Value) Then allFilled = False Exit For End If Next cell If allFilled Then isComplete = True Exit For End If Next row IsTableComplete = isComplete End Function However, when I fill in the first row, save and close the document, upon re-opening it, the warning messages disappear and I can save and close the document. I need the code to reset itself each time a user opens the document. So that it checks when its been newly opened for the next blank row, if that has not been filled in then the user should not be able to save or close the document until it is. Any help on this would be much appreciated :) The 'practice' table looks like this:
VBA: Save file as .csv with sequential numbering
Good day Community, I am trying to save one sheet from a macro-enabled file as a .csv document. I will need to save it every month so each month I would like to save the file with a new number (e.g. Test File 1.csv; Test File 2.csv; Test File 3.csv etc.). I have tried the following code but it always gets stuck at the part that I have highlighted in yellow below and I get the attached error message. Any ideas on how to move past this would be appreciated! (I am using Excel on Mac) Sub SaveSheetAsCSVWithSequence() Dim ws As Worksheet Dim filePath As String Dim fileName As String Dim fileExtension As String Dim newFileName As String Dim fileSuffix As Integer Dim fullFilePath As String ' Set the worksheet you want to save Set ws = ThisWorkbook.ActiveSheet ' Set the file path (current workbook path) filePath = "https://accionvegana.org/accio/QjMt92YuQnZvN3byNWat5Se0lmb11WbvNGajVGd6MHc0/Users/xxxx/Desktop/Testing Save As.xlsm" & "\" ' Set the base file name (current workbook name without extension) fileName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) ' Set file extension to .csv fileExtension = ".csv" ' Initialize suffix counter fileSuffix = 1 ' Loop to check if a file with the same name and suffix already exists Do ' Construct new file name with sequence number suffix newFileName = fileName & "_" & fileSuffix & fileExtension fullFilePath = filePath & newFileName ' Increment suffix if file already exists fileSuffix = fileSuffix + 1 Loop While Dir(fullFilePath) <> "" ' Save the active sheet as a CSV file with the new name ws.Copy ActiveWorkbook.SaveAs fileName:=fullFilePath, _ FileFormat:=xlCSVUTF8, CreateBackup:=False ' Close the CSV file after saving ActiveWorkbook.Close False ' Notify the user MsgBox "File saved as: " & fullFilePath, vbInformation End SubFormula or Other Solution?
I have an accounting client who wants to have Excel automatically add a category to a transaction He wants Excel to search B2 and if the word salary is found in any part of the field, place the word INCOME in e2. For B3, if Excel finds GMF in any part of the cell, add the "car payment" to the category. Can this be done using a function or do you have an alternate method? Thanks.Getting Run Time Error 2302 Access can't save output to file you've selected
I have an Event Procedure that runs off of a control button being clicked. The below screenshot shows what's in the event procedure. I'm getting a run time error 2302 stating Access can't save the output data to the file you've selected. Any idea why? Private Sub Command230_Click() 'Define output location variables Dim strFullPathMatches As String Dim strFullPathNoMatches As String strFullPathMatches = "I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification" strFullPathNoMatches = "I:\Customer Operations-Core\Operational QA\Dispute Services Verification\2025 DPP Verification\Correspondence Verification" Me.Dirty = False 'Run queries and output to Excel DoCmd.OutputTo acOutputQuery, "QryCorrespondenceMatches", acFormatXLS, strFullPathMatches, True DoCmd.OutputTo acOutputQuery, "QryCorrespondenceNoMatches", acFormatXLS, strFullPathNoMatches, True 'Run delete queries to clear out the MS Access tables to prepare for next run DoCmd.OpenQuery "QryDeleteLetterStatusRecs" DoCmd.OpenQuery "QryDeleteCorrespondenceRecs" End Sub
Excel for Mac - Run-time error '1004': Method 'Name' of object 'Addin' failed
Dears, Whenever I open any Excel File (existing or blank), I get the following error message: "Visual Basic for Applications Run-time error '1004': Method 'Name' of object 'Addin' failed" Note: I don't have any VBA code in the file - again, this message also appears when opening a blank/new/empty file. I thought it could be related to one of the Add-ins that I had installed, but even after removing all of them, the message still appears. Does anybody know what it means and how to fix it? I'm running Excel for MacVersion 16.91 (24111020) from Microsoft 365 in macOS SequoiaVersion 15.1.1. Thanks in advance, Andre
