Enhancing Email Efficiency: A VBA Approach

In today's fast-paced business environment, the ability to communicate efficiently and effectively with clients is paramount. For many professionals, this involves sending personalized, multi-paragraph emails that not only convey the right message but also reflect the brand's identity through formatting, such as colored text, bolding, and hyperlinks. The challenge, however, lies in streamlining this process, especially when the task requires integrating data from tools like Excel and Word. Traditionally, mail merge has been a go-to solution, yet it falls short when it comes to maintaining formatting in the transition to email clients like Outlook.
This is where Visual Basic for Applications (VBA) comes into play, offering a powerful solution to automate and customize email composition directly from Excel. By leveraging VBA, it's possible to create a script that not only inputs data such as names, invoice numbers, and account details into a pre-designed email template but also preserves the desired formatting. This method promises a significant reduction in manual effort and time spent on copying and pasting document contents, thus enhancing team productivity and ensuring consistency in client communications.

Command	Description
CreateObject("Outlook.Application")	Creates an instance of Outlook Application.
outlookApp.CreateItem(0)	Creates a new email item.
.HTMLBody	Sets the HTML formatted body of the email.
.Display / .Send	Displays the email draft in Outlook or sends it directly.

VBA Scripting for Enhanced Email Automation

The VBA script provided automates the process of generating an email with customized content directly from Excel, targeting Microsoft Outlook as the email client. The core of this script revolves around creating an instance of the Outlook application and manipulating it to create a new email item. By employing the `CreateObject` function with the parameter "Outlook.Application", the script dynamically interacts with Outlook, bypassing the need for manual operation. This automation streamlines the workflow, particularly for users who regularly send emails with standardized but personalized content. The `CreateItem(0)` method is crucial as it initializes a new mail item, setting the stage for content insertion. The flexibility of VBA allows for dynamic content insertion, making it possible to personalize emails with client-specific data, such as names, invoice numbers, and account details.
The script's pivotal feature is its ability to insert HTML-formatted text into the email body via the `.HTMLBody` property. This method ensures that the email retains desired formatting, including bold text, hyperlinks, and colored text, directly reflecting the user's specifications. Such capability is especially significant in maintaining brand consistency and enhancing the readability of emails. By concluding the script with either the `.Display` or `.Send` method, users are given the choice to review the email before sending or automate the sending process entirely. This dual functionality provides flexibility, catering to different user preferences and scenarios. Overall, the script exemplifies how VBA can be leveraged to simplify repetitive tasks, reduce errors, and save time, all while maintaining high standards of communication.

Streamlining Email Template Filling with Excel and VBA

VBA Script for Excel

Sub GenerateEmailContent() Dim outlookApp As Object Dim mailItem As Object Dim cell As Range Dim emailTemplate As String Set outlookApp = CreateObject("Outlook.Application") Set mailItem = outlookApp.CreateItem(0) emailTemplate = "Hello [Name], 

" & "Your invoice number [InvoiceNumber] with account number [AccountNumber] is ready. 

" & "Best regards, 
Your Company" For Each cell In Range("A1:A10") 'Adjust the range accordingly With mailItem .To = cell.Value .Subject = "Your Invoice is Ready" .HTMLBody = ReplaceTemplate(emailTemplate, cell.Row) .Display 'Or use .Send End With Next cell End Sub Function ReplaceTemplate(template As String, row As Integer) As String Dim replacedTemplate As String replacedTemplate = template replacedTemplate = Replace(replacedTemplate, "[Name]", Cells(row, 2).Value) replacedTemplate = Replace(replacedTemplate, "[InvoiceNumber]", Cells(row, 3).Value) replacedTemplate = Replace(replacedTemplate, "[AccountNumber]", Cells(row, 4).Value) ReplaceTemplate = replacedTemplate End Function 

Exporting Formatted Email Content to Excel Cell

Excel Formula Approach

'Note: This is a conceptual representation. Excel formulas cannot inherently 'maintain rich text formatting or execute complex scripting for emails. 'Consider using VBA or integrating with an external application for 'advanced formatting needs. The below "formula" is a simplified 'approach for concatenation purposes. =CONCATENATE("Hello ", A1, CHAR(10), CHAR(10), "Your invoice number ", B1, " with account number ", C1, " is ready.", CHAR(10), CHAR(10), "Best regards,", CHAR(10), "Your Company") 'To achieve actual formatting, consider using the VBA method above 'or an external software solution that supports rich text formatting in emails. 

Automating Email Generation and Formatting from Excel

Utilizing VBA for Email Automation

Dim outlookApp As Object Dim mailItem As Object Set outlookApp = CreateObject("Outlook.Application") Set mailItem = outlookApp.CreateItem(0) With mailItem .To = "client@email.com" .Subject = "Your Subject Here" .HTMLBody = "This is your email body with " & _ "bold, " & _ "hyperlinks, and " & _ "colored text." .Display ' or .Send End With Set mailItem = Nothing Set outlookApp = Nothing 

Expanding Email Automation with VBA

While the initial solution provided outlines how to automate email composition using VBA in Excel, directly embedding formatted content into Excel cells remains a complex challenge. Excel, primarily designed for data analysis and manipulation, offers limited support for rich text formatting within cells. This limitation becomes apparent when attempting to maintain specific text styles, colors, or hyperlinks, as Excel cells do not natively support HTML or similar markup languages. The core issue lies in Excel's data presentation layer, which prioritizes numerical and text data without the intricate formatting options found in word processors or email clients.
To address this, one might consider alternative approaches that leverage Excel's strengths. For instance, generating the email content in a Word document using VBA, which supports rich text formatting, and then automating the process to send this document as an email body or attachment via Outlook. This method utilizes the full range of Word's formatting capabilities before interfacing with Outlook, thereby ensuring that the email's visual appeal is not compromised. Furthermore, exploring third-party tools or add-ins that enhance Excel's functionality could offer a workaround, enabling more sophisticated formatting options directly within Excel spreadsheets. These solutions, while requiring additional steps or resources, provide a pathway to achieving the desired outcome of sending beautifully formatted emails without manual intervention.

Email Automation FAQs

1. Question: Can Excel cells support HTML formatting directly?
2. Answer: No, Excel cells cannot natively interpret or display HTML formatting. They are primarily designed for plain text and basic numerical data.
3. Question: Is it possible to send emails from Excel without using Outlook?
4. Answer: Yes, it is possible by using third-party services or APIs that can be integrated with Excel through VBA, although Outlook provides the most seamless integration.
5. Question: Can I automate email sending with attachments using VBA?
6. Answer: Yes, VBA allows you to automate sending emails with attachments by manipulating the Outlook application object model.
7. Question: How can I ensure my email retains its formatting when copied from Word to Outlook?
8. Answer: Using Word as the source for your email content ensures that formatting is preserved when using the ‘Send to Mail Recipient’ feature or when programmatically accessing Outlook via VBA.
9. Question: Is it necessary to have programming knowledge to automate emails in Excel?
10. Answer: Basic knowledge of VBA is required to write the scripts for automation, but many resources and templates are available for beginners.

VBA and Email Automation: A Synthesis

Throughout the exploration of using VBA for email automation, it's clear that while Excel's native capabilities for handling rich text formatting within cells are limited, VBA scripts provide a powerful workaround. By leveraging Outlook's application object model, VBA scripts can automate the creation of emails that incorporate Excel data, preserving the intended formatting. This method not only saves significant time but also maintains the professional appearance of communications sent to clients. Challenges such as integrating rich text formatting and hyperlinks can be effectively addressed through this programming approach. Moreover, the potential to expand Excel's functionality through third-party tools or additional VBA scripting presents a valuable avenue for enhancing workflow efficiency. Ultimately, VBA stands out as an indispensable tool for professionals looking to streamline their email communication processes directly from Excel, underscoring the importance of automation in today's business environment.
https://www.tempmail.us.com/en/vba/automating-email-composition-in-excel-with-vba

created a VBA script that basically takes a database export file(single worksheet) and then formats it into more easily read workbook that can be printed out correctly. its ~700 lines with many sub routines being executed inside of one module.
​
When i open up a newly exported excel file and IMPORT the module file and run it, the script works as intended. creates exactly what i need with no errors.
When i click the add-in button that is using the same exact script it will then try to open another excel file. One where i workshopped most of this, but not all of it. i've used about 8 different excel files over the history of creating this script that i gave a revision number in the file name and quick description so i can trace back to different parts. It will fail to run the script and give me an error.
​
Has anyone else run into this? I don't notice anything in my code that references that old file or anything like that. Again, when i use Import module and run the code it works as intedned. it only fails when i try to use it as an Add-In. i've also created a new add-in from a fresh xlam file and it still runs into the same error.
​
Help?
EDIT: I WAS UNSUCCESSFULLY ABLE TO MAKE THIS WORK AS AN ADD-IN. I AM NOW DISTRIBUTING IT AS A PERSONAL WORKBOOK INSTEAD AND THAT WORKS.
​
Code as requested:
​

Sub CreateSheetsAndPopulateData() Dim wsDefault As Worksheet Dim lastRow As Long, currentRow As Long Dim wsIndex As Worksheet Dim wb As Workbook ' Set reference to the workbook Set wb = ThisWorkbook ' Copy the "Default" worksheet wb.Sheets("Default").Copy Before:=wb.Sheets(1) ' Rename the copied worksheet to "Raw Data" Set wsDefault = wb.Sheets(1) ' The copied worksheet is now the first sheet wsDefault.Name = "Raw Data" ' Set the worksheet where your data is located Set wsDefault = ThisWorkbook.Sheets("Default") ' Format the Default Worksheet FormatDefaultSheet wsDefault ' Find the last row with data in column A lastRow = wsDefault.Cells(wsDefault.Rows.Count, "A").End(xlUp).Row ' Trim leading spaces from column B TrimLeadingSpaces wsDefault, lastRow 'Convert columns A, C and F to numbers and column D to text SetDataTypes wsDefault, lastRow 'Find Duplicates and sum up their quantities SumDuplicatesInColumnE wsDefault, lastRow 'Create new column of total quantities required MultiplyQuantitiesByLevel ' Add a new worksheet and name it "INDEX" Set wsIndex = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) wsIndex.Name = "INDEX" ' Apply the template to the index sheet CreateIndexTemplate wsIndex ' Start populating from row 9 Dim newRow As Long newRow = 9 ' Loop through each row and create sheets as needed For currentRow = 2 To lastRow ' Check if DRAWINGTYPE is 34 or 35 If wsDefault.Cells(currentRow, 3).Value = 34 Or wsDefault.Cells(currentRow, 3).Value = 35 Then Dim sheetName As String sheetName = Trim(wsDefault.Cells(currentRow, 5).Value) ' Remove leading and trailing spaces ' Check if sheet with the same name exists If Not SheetNameExists(sheetName) Then ' Create a new sheet Dim newSheet As Worksheet Set newSheet = Sheets.Add(After:=Sheets(Sheets.Count)) newSheet.Name = sheetName ' Create the template for the new sheet CreateTemplate newSheet ' Populate data in the new sheet PopulateData wsDefault, currentRow, lastRow, sheetName ' Sort rows in the new sheet SortRows newSheet Else ' Sheet with the same name already exists, skip creating new sheet 'MsgBox "Sheet with name '" & sheetName & "' already exists. Skipping creation." End If ' Populate the index sheet with data PopulateIndexSheet wsIndex, wsDefault.Cells(currentRow, 5).Value, wsDefault.Cells(currentRow, 7).Value, newRow ' Populate index sheet with data newRow = newRow + 1 ' Move to the next row for populating the next data End If Next currentRow End Sub Sub FormatDefaultSheet(wsDefault As Worksheet) Dim i As Long Dim spaceCounter As Integer Dim originalLastRow As Long Dim rng As Range Dim cell As Range ' Check if column A already contains the label "Structure Level" If wsDefault.Cells(1, 1).Value <> "Structure Level" Then ' Insert a new column at column A wsDefault.Columns("A:A").Insert Shift:=xlToRight ' Rename A1 to "Structure Level" wsDefault.Cells(1, 1).Value = "Structure Level" End If ' Find the last row in column B (or A if a new column was inserted) originalLastRow = wsDefault.Cells(wsDefault.Rows.Count, "B").End(xlUp).Row ' Loop through each cell in column B (previously column A) For i = originalLastRow To 2 Step -1 ' Start from the last row to avoid issues with deleting rows ' Check if Class is X or C in column H If wsDefault.Cells(i, 9).Value = "X" Or wsDefault.Cells(i, 9).Value = "C" Then ' Delete the entire row if Class is X or C wsDefault.Rows(i).Delete Else ' Reset space counter for each cell spaceCounter = 0 ' Count leading spaces in the cell value Do While Mid(wsDefault.Cells(i, 2).Value, spaceCounter + 1, 1) = " " spaceCounter = spaceCounter + 1 Loop ' Write the count of leading spaces in column A wsDefault.Cells(i, 1).Value = spaceCounter \ 4 End If ' Check if the value in column E starts with ".", ".", ".", ".", ".", ".", ".", or "." If Left(wsDefault.Cells(i, 5).Value, 3) = "." Or _ Left(wsDefault.Cells(i, 5).Value, 4) = "." Or _ Left(wsDefault.Cells(i, 5).Value, 3) = "." Or _ Left(wsDefault.Cells(i, 5).Value, 3) = "." Or _ Left(wsDefault.Cells(i, 5).Value, 3) = "." Or _ Left(wsDefault.Cells(i, 5).Value, 4) = "." Or _ Left(wsDefault.Cells(i, 5).Value, 3) = "." Or _ Left(wsDefault.Cells(i, 5).Value, 3) = "." Then ' Change the value in column D to "PE" wsDefault.Cells(i, 4).Value = "PE" Else ' For any other value in column E, change the value in column D to "MS" wsDefault.Cells(i, 4).Value = "MS" End If Next i ' Specify the range of cells to process (column A in this case) Set rng = wsDefault.Range("E2:E" & wsDefault.Cells(wsDefault.Rows.Count, "E").End(xlUp).Row) ' Loop through each cell in the specified range For Each cell In rng ' Check if the cell value does not contain "-" If InStr(cell.Value, "-") = 0 Then ' Check if the cell value does not start with "W" or "M" If Left(cell.Value, 1) <> "W" And Left(cell.Value, 1) <> "M" Then ' Add "-A" to the cell value cell.Value = cell.Value & "-A" End If End If Next cell End Sub Sub SetDataTypes(wsDefault As Worksheet, lastRow As Long) Dim rngNumbers As Range Dim rngText As Range Dim cell As Range Dim rowNum As Long ' Define the range for numbers (Columns A, C, and H) Set rngNumbers = Union(wsDefault.Range("A1:A" & lastRow), wsDefault.Range("C1:C" & lastRow), wsDefault.Range("H1:H" & lastRow)) ' Define the range for text (Column F) Set rngText = wsDefault.Range("F1:F" & lastRow) ' Convert columns A, C, and H to numbers and column F to text For Each cell In rngNumbers If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then cell.Value = Val(cell.Value) End If Next cell For Each cell In rngText If Not IsEmpty(cell.Value) Then cell.Value = CStr(cell.Value) End If Next cell End Sub Sub SumDuplicatesInColumnE(wsDefault As Worksheet, lastRow As Long) Dim i As Long Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") ' Copy values from Column H to Column K wsDefault.Range("H1:H" & lastRow).Copy Destination:=wsDefault.Range("K1") ' Loop through each row in Column E For i = 1 To lastRow ' Check if the value in Column B is not empty If Not IsEmpty(wsDefault.Cells(i, "E").Value) Then ' Check if the value in Column C is 35 If wsDefault.Cells(i, "C").Value = 35 Then ' Check if the value already exists in the dictionary If dict.exists(wsDefault.Cells(i, "E").Value) Then ' If it exists, add the value from Column H to the existing sum dict(wsDefault.Cells(i, "E").Value) = dict(wsDefault.Cells(i, "E").Value) + wsDefault.Cells(i, "K").Value Else ' If it doesn't exist, add the value to the dictionary dict(wsDefault.Cells(i, "E").Value) = wsDefault.Cells(i, "K").Value End If End If End If Next i ' Loop through the dictionary and update the values in Column H with the summed values For i = 1 To lastRow If Not IsEmpty(wsDefault.Cells(i, "E").Value) Then If wsDefault.Cells(i, "C").Value = 35 Then If dict.exists(wsDefault.Cells(i, "E").Value) Then wsDefault.Cells(i, "H").Value = dict(wsDefault.Cells(i, "E").Value) dict.Remove wsDefault.Cells(i, "E").Value ' Remove the item from dictionary after updating End If End If End If Next i End Sub Sub MultiplyQuantitiesByLevel() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Dim currentLevel As Long Dim prevLevel As Long Dim prevQuantity As Double ' Set the worksheet Set ws = ThisWorkbook.Sheets("Default") ' Change "Default" to your actual sheet name ' Find the last row with data lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Initialize variables currentLevel = 0 prevLevel = 0 prevQuantity = 1 ' Loop through each row For i = 2 To lastRow ' Assuming row 1 is header ' Get the structure level If IsNumeric(ws.Cells(i, 1).Value) Then currentLevel = ws.Cells(i, 1).Value End If ' Calculate total quantity based on structure level If currentLevel > 1 Then If prevLevel = currentLevel - 1 Then ' Multiply by the previous quantity ws.Cells(i, 10).Value = ws.Cells(i, 8).Value * prevQuantity Else ' Reset previous quantity if structure level gap prevQuantity = 1 ' Find the previous row with the matching structure level For j = i - 1 To 2 Step -1 If ws.Cells(j, 1).Value = currentLevel - 1 Then prevQuantity = ws.Cells(j, 8).Value Exit For End If Next j ' Multiply by the previous quantity ws.Cells(i, 10).Value = ws.Cells(i, 8).Value * prevQuantity End If Else ' For level 1, use the quantity directly ws.Cells(i, 10).Value = ws.Cells(i, 8).Value End If ' Update previous level and quantity prevLevel = currentLevel prevQuantity = ws.Cells(i, 10).Value Next i ' Add a header for the total quantities ws.Cells(1, 10).Value = "Total Quantities" End Sub Sub CreateIndexTemplate(ws As Worksheet) ' Set column widths ' Merge cells ' Set font and alignment ws.Cells.Font.Name = "Arial" ' Set alignment for specific cells ' Set wrap text for specific cells ' Set font size for specific cells ' Fill in the text ws.Range("A1").Value = "." ws.Range("A2").Value = ws.Previous.Range("E2").Value ' Set border weight With ws.Range("A8:I8").Borders ' Set bottom border to medium weight ' Set top, left, and right borders to thin weight ' Set inside borders to thin weight End With ' Set page setup properties With ws.PageSetup .Orientation = xlLandscape .Zoom = False ' Turn off zoom to enable "Fit To" scaling .FitToPagesWide = 1 ' Fit to 1 page wide .FitToPagesTall = False ' Do not fit to specific number of pages tall .LeftFooter = "&""Arial,Bold""&10Index" .CenterFooter = "&""Arial,Bold""&14&K000000DUPONT CONFIDENTIAL" .RightFooter = "Page &P" & Chr(10) & "Date Printed: &D" End With End Sub Sub PopulateIndexSheet(wsIndex As Worksheet, dataColumnE As Variant, dataColumnG As Variant, newRow As Long) ' Populate the index sheet with data wsIndex.Cells(newRow, "E").Value = dataColumnE ' Populate Column E of Index sheet with Column B value of source sheet wsIndex.Cells(newRow, "D").Value = dataColumnG ' Populate Column D of Index sheet with Column F value of source sheet ' Center align the value in Column E wsIndex.Cells(newRow, "E").HorizontalAlignment = xlCenter ' Apply borders to the entire row from column A to column I With wsIndex.Range(wsIndex.Cells(newRow, "A"), wsIndex.Cells(newRow, "I")).Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub Sub CreateTemplate(ws As Worksheet) ' Set column widths ' Merge cells ' Set font and alignment ' Set alignment for specific cells ' Set wrap text for specific cells ' Set font size for specific cells ' Fill in the text ws.Range("A2").Value = ws.Previous.Range("A2").Value ' Set border weight ' Set page setup properties With ws.PageSetup .Orientation = xlLandscape .Zoom = False ' Turn off zoom to enable "Fit To" scaling .FitToPagesWide = 1 ' Fit to 1 page wide .FitToPagesTall = False ' Do not fit to specific number of pages tall .LeftFooter = "&""Arial,Bold""&10Section - " & ws.Name & Chr(10) & "Section Name - " & ws.Range("L9").Text ' Add worksheet name and value from cell L9 to the left footer .CenterFooter = "&""Arial,Bold""&14&K000000." .RightFooter = "Page &P" & Chr(10) & "Date Printed: &D" End With End Sub Sub PopulateData(wsDefault As Worksheet, startRow As Long, lastRow As Long, newSheetName As String) Dim newRow As Long newRow = 9 ' Start populating from row 9 ' Get the value in column A of the start row (the value that created the sheet) Dim currentSheetValue As Variant currentSheetValue = wsDefault.Cells(startRow, 1).Value ' Get the structure level of the preceding row Dim precedingStructureLevel As Variant If startRow > 2 Then ' Loop through each row from startRow - 1 to 2 (backward) For i = startRow - 1 To 2 Step -1 If wsDefault.Cells(i, 1).Value = wsDefault.Cells(startRow, 1).Value - 1 Then precedingStructureLevel = wsDefault.Cells(i, 5).Value ' Assuming column B contains the Drawing Number Exit For End If Next i Else precedingStructureLevel = "" End If ' Create a reference to the new sheet Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(newSheetName) ' Populate the first row with data from the start row InsertDataRow ws, newRow, wsDefault.Cells(startRow, 5).Value, wsDefault.Cells(startRow, 3).Value, wsDefault.Cells(startRow, 6).Value, wsDefault.Cells(startRow, 8).Value, wsDefault.Cells(startRow, 7).Value, wsDefault.Cells(startRow, 10).Value, True, precedingStructureLevel ' Pass True for start row ' Increment newRow for the subsequent row newRow = newRow + 1 ' Insert a blank row after the startRow ws.Rows(newRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' Define the range for the inserted blank row (columns A to O) Dim blankRowRange As Range Set blankRowRange = ws.Range(ws.Cells(newRow, "A"), ws.Cells(newRow, "O")) ' Apply borders to the defined range With blankRowRange.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With ' Loop through each row from startRow + 1 to lastRow For i = startRow + 1 To lastRow If wsDefault.Cells(i, 1).Value = currentSheetValue + 1 Then ' If the value in column A is exactly one greater than the current sheet value, populate the data for subsequent rows InsertDataRow ws, newRow + 1, wsDefault.Cells(i, 5).Value, wsDefault.Cells(i, 3).Value, wsDefault.Cells(i, 6).Value, wsDefault.Cells(i, 8).Value, wsDefault.Cells(i, 7).Value, wsDefault.Cells(i, 10).Value, False, wsDefault.Cells(i - 1, 5).Value ' Pass False for subsequent rows and the structure level of the preceding row newRow = newRow + 1 ' Increment newRow for the next subsequent row ElseIf wsDefault.Cells(i, 1).Value = currentSheetValue Then ' If the value in column A matches the current sheet value, exit the loop Exit For End If Next i End Sub Sub InsertDataRow(ws As Worksheet, rowNum As Long, rowData1 As String, rowData2 As Variant, rowData3 As String, rowData4 As String, rowData5 As String, rowData6 As String, isStartRow As Boolean, ByVal precedingStructureLevel As String) If isStartRow Then ' Populate columns for the start row Else ' Populate columns for subsequent rows End If ' Apply borders to the new row (columns A to O) ' Apply alignment settings ' Column L - middle and left alignment End Sub Sub TrimLeadingSpaces(wsDefault As Worksheet, lastRow As Long) Dim rng As Range Dim cell As Range ' Define the range to trim (assuming data starts from row 2 in column B) Set rng = wsDefault.Range("B2:B" & lastRow) ' Loop through each cell in the range and trim leading spaces For Each cell In rng cell.Value = Trim(cell.Value) Next cell End Sub Sub SortRows(ws As Worksheet) Dim lastRow As Long Dim sortRange As Range Dim insertRow As Long Dim firstDataRow As Long ' Find the last row with data in column B lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' Define the range to sort Set sortRange = ws.Range("A11:O" & lastRow) ' Sort the range based on the values in column B With ws.Sort .SortFields.Clear .SortFields.Add Key:=ws.Range("B11:B" & lastRow), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange sortRange .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Find the first row that needs a blank row before it (first occurrence of 36) ' Ensure it is not directly after the initial start row (e.g., row 11) On Error Resume Next ' In case there's no match insertRow = Application.Match(36, ws.Range("B:B"), 0) On Error GoTo 0 ' Turn back on regular error handling ' Check if there's a match and it's not directly after the initial data row If Not IsError(insertRow) And insertRow > 0 Then ' Assuming row 11 is your start row ' Insert a row at insertRow ws.Rows(insertRow).Insert Shift:=xlDown ' Specify the range for the borders from A:O of the inserted row With ws.Range("A" & insertRow & ":O" & insertRow).Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If ' Check if both row 10 and row 11 are empty, if so, delete row 11 If WorksheetFunction.CountA(ws.Rows(10)) = 0 And WorksheetFunction.CountA(ws.Rows(11)) = 0 Then ws.Rows(11).Delete End If End Sub Function SheetNameExists(sheetName As String) As Boolean Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name = sheetName Then SheetNameExists = True Exit Function End If Next ws SheetNameExists = False End Function 

​

I have an Excel workbook in which I insert daily data to be printed at the end of the month in a Word document daily report. I am aware of the mail merge features, but they do not fit what I am trying to accomplish. I would like to embed a Word document within the Excel workbook as a template, and by running a macro, it will print out all requested daily reports. I need a VBA code method to replace placeholders or bookmarks or whatever could be used to print that document. Instead of creating 31 report pages in Excel and creating a reference for each cell, I would like to create one Word document and replace data in a flexible way with VBA script.

I am creating a macro currently that takes an excel that contains exported responses of a microsoft form, and goes through each response, row by row and creates a word document using the repondent ID as the name of the word doc, and fills in the word doc with that individual's form responses
This is the rough format:
ID [Taken from column A header]
Response [Responder ID - taken from Cell A2]
​
Question 1 [Taken from column B header]
Response [Taken from Cell B2]
​
Question 3[Taken from column C header]
Response [Taken from Cell C2]
​
So on and so forth till the last populated column of the excel
​
This is the current VBA code i have:
Sub CreateIndividualWordDocs()
​
' Add reference to Microsoft Word Object Library
Dim wdApp As Object, wdDoc As Object
​
Set wdApp = CreateObject("Word.Application")
​
' Define Excel and Word file paths
Dim ExcelFilePath As String
Dim WordFileOutputPath As String
​
ExcelFilePath = "C:\Users\bryan.ang\OneDrive - L'Oréal\Documents\Form Mail Merge Test\VBA Test form macro.xlsm" ' Replace with your file path
WordFileOutputPath = "C:\Users\bryan.ang\OneDrive - L'Oréal\Documents\Form Mail Merge Test\Word" ' Replace with your desired folder path
​
' Open the Excel file
Dim xlSheet As Object
Set Workbooks = Workbooks.Open(ExcelFilePath)
Set xlSheet = WorkbooI have made ks.Sheets("Form1") ' Replace "Sheet1" with actual sheet name
​
' Get header row and data range
Dim HeaderRow As Long, LastRow As Long
​
HeaderRow = xlSheet.Cells(1, 1).End(xlUp).Row
LastRow = xlSheet.Cells(Rows.Count, 1).End(xlUp).Row
​
' Loop through each data row
For i = 2 To LastRow
Dim RespondentID As String, Question As String, Response As String
​
' Get respondent ID
RespondentID = xlSheet.Cells(i, 1).Value
​
' Create Word document
Set wdDoc = wdApp.Documents.Add()
​
' Add heading with respondent ID
With wdDoc.Range
.InsertAfter "ID: " & RespondentID & vbNewLine
End With
​
' Loop through questions and responses
For j = 2 To xlSheet.UsedRange.Columns.Count
Question = xlSheet.Cells(1, j).Value
Response = xlSheet.Cells(i, j).Value
​
' Add formatted question and response
With wdDoc.Range
.InsertAfter Question & vbNewLine & Response & vbNewLine
.Bold = False ' Optional: Unbold response
End With
Next j
​
' Save and close Word document
With wdDoc
.SaveAs WordFileOutputPath & "\" & RespondentID & ".docx"
.Close SaveChanges:=wdSaveChangesYes
End With
​
' Set focus to Excel
' wdApp.Visible = False ' Optional: Hide Word application
Workbooks.ActiveWindow.SetFocus
​
Next i
​
' Close Excel
Workbooks.Close SaveChanges:=wdSaveChangesNo
​
' Quit Word
wdApp.Quit
​
MsgBox "Individual Word documents created successfully!", vbInformation
​
End Sub
​
Upon running the code, i already get a Compile error: Invalid use of property, and the first line gets highlighted, i doubt the issue is within the first line.
would anyone know what i am doing wrong and how to fix it?
​
Am a VBA noob so looking through the help and reading up on the error documentation isn't really clearing much up for me, any help or advice is appreciated!

Hello all,
For work, we create a series of reports using Excel. They maintain the same template, so all like information is always located in the same cells. I'm trying to write a macro that will take the values of certain cells (they contain scores like '+', '-', '0', and others), and paste them into one singular Excel workbook to create an easy-to-read summary of each of the reports. I have saved all the reports in the same folder, and am looking for this code to iterate over all .xlsx files in said folder, copy the values from the cells I have listed, and paste them into the new workbook horizontally, creating rows.
I'll add that some of the cells that contain the score are merged cells vertically. In those cases, I have used the top-left-most cell as the targeted cell in the code. As well, these cells currently use "Data Validation" (I think it's called that, they have drop-down boxes with set options).
Currently, when the code I have written is run, it causes Excel to very briefly process something (the mouse pointer turns into the processing circle), then return the message I added at the end to inform me it's done. However, when I go to look, there is nothing in the desired file, which is obviously a problem.
I would really appreciate it if I could get some help or pointers as to where I might be going wrong to achieve my goals. You'll find my attempt at the code below. For privacy's sake, I have input example text into the folder paths, I know to input the proper locations when I'm running the macro.
Thank you in advance.

Sub CopyDataToTargetWorkbookWithMergedCells() Dim FolderPath As String Dim Filename As String Dim TargetWorkbook As Workbook Dim SourceWorkbook As Workbook Dim TargetSheet As Worksheet Dim SourceSheet As Worksheet Dim Cell As Range Dim CopyRange As Range Dim LastColumn As Long Dim TargetRow As Long Dim MergedCell As Range On Error GoTo ErrorHandler ' Set the path to the folder containing the Excel files FolderPath = "Folder" ' Open the target workbook Set TargetWorkbook = Workbooks.Open(FolderPath) Set TargetSheet = TargetWorkbook.Sheets(1) TargetRow = 1 ' Initialize the target row ' Loop through all files in the folder Filename = Dir(FolderPath & "*.xlsx") Do While Filename <> "" ' Open each workbook in the folder Set SourceWorkbook = Workbooks.Open(FolderPath & Filename) Set SourceSheet = SourceWorkbook.Sheets(1) ' Get the value of C2 and remove "Name: " Dim NameValue As String NameValue = Replace(SourceSheet.Range("C2").Value, "Name: ", "") ' Find the last column in the target sheet LastColumn = TargetSheet.Cells(TargetRow, TargetSheet.Columns.Count).End(xlToLeft).Column ' Paste the modified C2 value as the left-most value TargetSheet.Cells(TargetRow, LastColumn + 1).Value = NameValue ' Loop through the specified cells and copy the values For Each Cell In SourceSheet.Range("E5, E7, E8, E12, E13, E14, E17, E19, E20, E24, E29, E31, E33, E36, E43, E47, E50, E51, E53, E55, E57, E60, E62, E64, E67, E69, E72") ' Check if the cell is part of a merged area If Cell.MergeCells Then ' Copy the value from the top-left cell of the merged area Set MergedCell = Cell.MergeArea.Cells(1, 1) If CopyRange Is Nothing Then Set CopyRange = MergedCell Else Set CopyRange = Union(CopyRange, MergedCell) End If Else If CopyRange Is Nothing Then Set CopyRange = Cell Else Set CopyRange = Union(CopyRange, Cell) End If End If Next Cell ' Paste the values into the next available column in the target sheet CopyRange.Copy TargetSheet.Cells(TargetRow, LastColumn + 2) ' Close the source workbook without saving changes SourceWorkbook.Close False ' Reset the CopyRange for the next iteration Set CopyRange = Nothing ' Move to the next row in the target sheet TargetRow = TargetRow + 1 ' Get the next file in the folder Filename = Dir Loop ' Save the changes to the target workbook TargetWorkbook.Save TargetWorkbook.Close MsgBox "Data successfully copied to the target workbook.", vbInformation Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbExclamation End Sub 

I'm working on a project to unpack a cluster of workbooks full of merged cells and randomly pasted pictures. The pictures are NOT embedded in cells; they are just pasted on top of the sheets, I'm guessing as shapes (I haven't verified in VBA)
It looks like openpyxl_image_loader only works for images embedded in cells. I tried and debugged that and it didn't find anything. I did find ws._images and was able to see the image objects on the worksheet, and I've seen the function ws.add_image, but I am looking for something like ws.save_image. I also just found the win32 package that seems to be able to address Excel almost like VBA but I want to see if I can do it in openpyxl first.
My basic gameplan is to get a count of all the images on each worksheet, then iterate through that count by copying and saving each picture to a path with the sheet name and photo index number. But I need a way to save the photos.
(EDIT)- so I am looking at the openpyxl image object in the PyCharm debugger. Am I crazy or does this image object not have the actual image data? It has all the image attributes (dimensions and format) and a BytesIO "ref" object that ChatGPT says is the anchored location of the image. But no actual image.

Hello together,
I am building a financial model and want to follow standard conventions of color codings (i.e. hardcoe should be colored blue, linkt to other sheet as green etc.)
Since I am merging my model with a lot of sheets that colleauges worked on who did not color code consistently I am looking for a quick fix that allows me to color code without manually checking every cell.
I've already done google research and it seems this is only possible with VBA? I could swear that I've seen one financial modeling course on YT where the presenter did this with plain vanilla excel.
Would be very grateful for any hints.
Best

Given a selected range on the active sheet in a workbook, e.g.: A1:E10
A1 and A2 are merged. C4 and D4 are also merged.
https://i.imgur.com/MOwjtyC.jpg
Expected output on debug console:

Merged rows in selected area: 2 Merged columns in selected area: 2 

Tried to achieve above with code:

Sub Checker() For Each loopRow In Selection.rows If loopRow.MergeCells Then Debug.Print "Merged rows in selected area: " & loopRow.MergeArea.rows.Count End If Next loopRow For Each loopCol In Selection.Columns If loopCol.MergeCells Then Debug.Print "Merged columns in selected area: " & loopCol.MergeArea.Columns.Count End If Next loopCol End Sub 

There is no output on the console

As mentions on the topic, here is my code:
It works perfectly until paste new merged file into my final file, instead of append next to the previous, it just replace that one. I just start learning VBA so please help, thank you guys very much!

'Automate contract participant sponsorship Sub AutomateContractHCPsponsorship() 'Khai bao bien Dim wapp As Word.Application Dim wdoc As Word.Document Dim owapp As Word.Application Dim owdoc As Word.Document Dim numOfRow, numOfColumn, iRow, iColumn As Long 'Gan gia tri cho cac bien Set wapp = CreateObject("word.application") wapp.Visible = True Set owapp = CreateObject("word.application") owapp.Visible = True With ThisWorkbook.Sheets("CME participant sponsorship") numOfRow = Excel.WorksheetFunction.CountA(.Columns(1)) numOfColumn = Excel.WorksheetFunction.CountA(.Rows(1)) Set owdoc = owapp.Documents.Add For iRow = 2 To numOfRow Step 1 Set wdoc = wapp.Documents.Open("My excel data path") For iColumn = 1 To numOfColumn Step 1 wdoc.Content.Find.Execute _ FindText:=.Cells(1, iColumn), _ ReplaceWith:=.Cells(iRow, iColumn), _ Replace:=wdReplaceAll ' Replace text in footers For Each wsection In wdoc.Sections For Each wfooter In wsection.Footers wfooter.Range.Find.Execute _ FindText:=.Cells(1, iColumn), _ ReplaceWith:=.Cells(iRow, iColumn), _ Replace:=2 ' wdReplaceAll Next wfooter Next wsection Next ' Copy the content from the source document wdoc.Content.Copy ' After copying the content from the source document ' Append the copied content to the end of the target document owdoc.Content.InsertAfter vbCrLf ' Add a new line before appending content owdoc.Content.Collapse Direction:=wdCollapseEnd owdoc.Content.Paste Application.CutCopyMode = False wdoc.SaveAs2 Filename:=ThisWorkbook.Path & "\Final files\Final contract\" & _ "Contract BS " & .Cells(iRow, 3) & ".docx" wdoc.Close Next wapp.Quit Set wdoc = Nothing Set owdoc = Nothing End With End Sub 

As mentions on the topic, here is my code:
It works perfectly until paste new merged file into my final file, instead of append next to the previous, it just replace that one. I just start learning VBA so please help, thank you guys very much!

'Automate contract participant sponsorship Sub AutomateContractHCPsponsorship() 'Khai bao bien Dim wapp As Word.Application Dim wdoc As Word.Document Dim owapp As Word.Application Dim owdoc As Word.Document Dim numOfRow, numOfColumn, iRow, iColumn As Long 'Gan gia tri cho cac bien Set wapp = CreateObject("word.application") wapp.Visible = True Set owapp = CreateObject("word.application") owapp.Visible = True With ThisWorkbook.Sheets("CME participant sponsorship") numOfRow = Excel.WorksheetFunction.CountA(.Columns(1)) numOfColumn = Excel.WorksheetFunction.CountA(.Rows(1)) Set owdoc = owapp.Documents.Add For iRow = 2 To numOfRow Step 1 Set wdoc = wapp.Documents.Open("My excel data path") For iColumn = 1 To numOfColumn Step 1 wdoc.Content.Find.Execute _ FindText:=.Cells(1, iColumn), _ ReplaceWith:=.Cells(iRow, iColumn), _ Replace:=wdReplaceAll ' Replace text in footers For Each wsection In wdoc.Sections For Each wfooter In wsection.Footers wfooter.Range.Find.Execute _ FindText:=.Cells(1, iColumn), _ ReplaceWith:=.Cells(iRow, iColumn), _ Replace:=2 ' wdReplaceAll Next wfooter Next wsection Next ' Copy the content from the source document wdoc.Content.Copy ' After copying the content from the source document ' Append the copied content to the end of the target document owdoc.Content.InsertAfter vbCrLf ' Add a new line before appending content owdoc.Content.Collapse Direction:=wdCollapseEnd owdoc.Content.Paste Application.CutCopyMode = False wdoc.SaveAs2 Filename:=ThisWorkbook.Path & "\Final files\Final contract\" & _ "Contract BS " & .Cells(iRow, 3) & ".docx" wdoc.Close Next wapp.Quit Set wdoc = Nothing Set owdoc = Nothing End With End Sub 

​
​
​
​
​
​
​
​

Saturday, September 30 - Friday, October 06

Top 5 Posts

score	comments	title & link
114	72 comments	[Discussion] How to Reduce Size of 28.5GB Excel File
97	98 comments	[Discussion] Is it worth it to learn VBA?
79	111 comments	[unsolved] My boss wants pretty spreadsheets, but without merged cells. I like to create several little columns to have the freedom to make different sizes, but this breaks data validation. How do you deal with that?
77	59 comments	[Discussion] Is Microsoft still actively supporting VBA?
34	12 comments	[solved] Need to determine if any of the words found in one cell are found anywhere within a broad range.

 

Unsolved Posts

score	comments	title & link
27	19 comments	[unsolved] Is there a way to select the color of an Excel cell and have it run a specific action automatically?
22	36 comments	[unsolved] Need to compare two 5000 row spreadsheets, monthly.
17	29 comments	[unsolved] How do I convert to a date "MM/DD/YYYY"
17	15 comments	[unsolved] How to make sure a cell with 2 values adds a leading 0 to either value if it is under 4 digits?
13	16 comments	[unsolved] I need to list the top three of a name in a list, what formula do I use?

 

Top 5 Comments

score	comment
401	RealAmerik said Select across the cells you want to look merged and centered. Right click, select "Format Cells", select the alignment tab, open the horizontal drop down and select "Center Across Selection". It will ...
274	Big_lt said Fire whoever formatted those sheets (instead of just hiding grid lines) Fire whatever dumbass middle manager said they need that many tabs in a single workbook Ask a bunch of people who uses...
184	OphrysApifera said Merging cells is a crime against humanity.
147	bp92009 said If Microsoft eliminated support for VBA in Office 365, there would be at least a handful of S&P 500 companies that would have nearly their whole valuation drop to negative numbers due to how essential...
116	ZestyBeer said It's always worth learning how to record basic macros and script them together for sure, especially if you spend a lot of your worklife with Excel & friends. But I'd only really recommend it for use i...

 

Hi, I'm strong in Excel VBA and now trying to create Word documents from Excel, working well by continually selecting end of document and writing new text
Part of this includes writing tables of text, again OK because I can create the table, apply formatting to table borders, refer to individual cells when writing text, merge cells when needed, for example:

tbl.Range.Cells(1).Range.InsertAfter "My text for cell 1" 

Now I want to create generic code for formatting individual rows, columns, or continuous groups of 1 or more cells. Ideally I don't want to select these cells, I just want to refer to them directly, I've already got the table object tbl and the document object wordDoc

Sub cellFormatHeader1(tbl As Word.Table, Optional iRows As Integer, Optional iColumns As Integer, Optional iIndex1 As Integer, Optional iIndex2 As Integer) Dim wdRng As Word.Range If iRows > 0 Then Set wdRng = tbl.Rows(iRows).Range ElseIf iColumns > 0 Then Set wdRng = tbl.Range.Columns(iColumns).Range ElseIf iIndex1 > 0 Then If iIndex2 > 0 Then Set wdRng = wordDoc.Range(Start:=tbl.Range.Cells(iIndex1).Range.Start, End:=tbl.Range.Cells(iIndex2).Range.End) Else Set wdRng = tbl.Range.Cells(iIndex1).Range End If Else Stop ' error End If With wdRng .Font.Color = wdColorWhite .Shading.BackgroundPatternColor = GetLongFromHex("#2F5496") End With End Sub 

I'm getting errors when trying to create the wdRng range object. My weakness is understanding how the range object works in Word and/or whether I can even create ranges from table cells/columns etc. For example, Set wdRng = tbl.Rows(iRows) doesn't work because I'm referring to a row not a range, Set wdRng = tbl.Rows(iRows).Range doesn't work because the range object doesn't exist at this level - but I could select so some kind of range object exists. I don't want to select though, so where the hell is the range object?! Why does everything have to use Selection, I'm from Excel and I hate selecting things in VBA!
Please advise anything I'm misunderstanding, thank you

I have a worksheet template where the last section is a long merge cell of 1000 rows. The user can enter free text in this large merged cell.
I then have an Export to PDF macro button on the top of the sheet.
However, on export since Excel automatically defines the print area as the end of the merge, I will get a long trail of blank pages.
Does anyone know of a way to export to PDF without blank pages?

Hello Excel wizkids! I'm scheduling people to work in various cities. In row 5, I'm trying to see how many people are available each day. In this case, Bob is available on January 3rd and everyone is available on January 4th. Is there any way for COUNTBLANK or another formula/VBA to see merged cells? If not, is there an alternative to merged cells? Thank you!
https://preview.redd.it/eong0o78g2ob1.png?width=402&format=png&auto=webp&s=8c050d2ceee389bb27fa9d6bf176355d33303f8d

Firstly, I Am Not A LAWYER (IANAL) and this is NOT legal advice!
It sounds like most people will soon be contacted by either Archer, your Attorney or both with settlement details. If you hear from Archer first, you would be wise to consult with your Attorney prior to signing anything with Archer.
10/6 Update:
New settlement information hit the comments section of the Lawsuit Information Center's Blog last night, heres a link. Some of the things have changed from the 9/28 update, and we have a much more complete picture of the "points system".
https://www.reddit.com/VeteransBenefits/comments/171que9/3m_settlement_details_sent_yesterday_106_long_and/
10/3 Update:
There seems to be a groups of folks interesting in opting out attempting to do something, see links below if you are interested (be sure to scroll down to the respective comments at the bottom of each link):
http://disq.us/p/2w1avnc
http://disq.us/p/2w20t40
9/29 Update:
I will make BOLD the "TLDR" highlights of yesterday's post.
A poster on the Lawsuit Information Blog is claiming AWKO is willing to represent "opt outs". I have no idea if this is true, but noted that this flies in the face of what lead Attorney Bryan Aylstock mentioned in Court during the the 9/8 hearing video. (Also, if true, unsure if they will take on clients from other firms.) For folks that want to inquire, you can find him posting over in the veterans sub by clicking here:
https://www.reddit.com/Veterans/comments/164g193/601_billion_settlement_reached_in_3m_earplug/
One of the Attorney's at the Lawsuit Information Blog has been kind enough to map out most/many of the legal hoops one will have to jump through once "opted out". See here:
https://www.lawsuit-information-center.com/when-will-your-3m-earplug-lawsuit-settle.html
If you decide to "opt out", the statute of limitations (varies state by state) will probably be invoked by 3M to throw your case out. So before going down the rabbit hole too far, figure this point out for your particuar situation.
9/28 Update:
I will leave this update here, but this now appears outdated (see 10/6 update above).
WELP, it's official, this letter hit a claimant today, you should be getting one soon too. Contains details of the settlement - much like Atty Mike Burns spelled out a month ago.

Settlement Registration Process
The next step is the settlement registration process. We anticipate that this process may begin as early as October 9, 2023.
During this process, you will be contacted directly by ARCHER. This is not a scam. ARCHER is a neutral third party that has been appointed by the Court as the Settlement Administrator to process claims.
ARCHER has created an online portal for claimants to register. ARCHER will contact you directly via email and/or text with login instructions. The portal will contain a settlement program disclosure as well as the registration packet. Through portal access, you will be able to electronically complete and sign the forms to complete the registration process. ARCHER will communicate directly with you regarding your settlement award and information needed to process your 3M CAEv2 litigation payment.
The registration information will be sent out on a rolling basis until all Combat Arms claimants have received it. Unfortunately, we cannot control the timing of when ARCHER reaches out to you.
The registration packet will be pre-filled with information that you have already provided. You will fill in other required information and update the pre-filled information if you see anything that is inaccurate. Our office will also review your registration packet in an effort to ensure its accuracy before its final submission.
You must promptly reply to all ARCHER communications to expedite your claim. ARCHER will communicate deadlines to you. Additionally, it is very important that you update ARCHER with any changes to your address, telephone number, or email address in a timely manner. Do not reach out to them until you have received your first communication from them.
You must fill out the registration packet to participate in the settlement.
It is required to be submitted to ARCHER by January 12, 2024. We encourage you to complete your registration packet as soon as possible as claims will be paid out on a first-in, first-out basis.
(My edit: If you want paid first, don't forget the FIFO aspect here!)
Payment Programs & Timeline
On the registration form, you will choose from two options: (1) the Expedited Payment Program; and (2) the Deferred Full Evaluation Program. You must choose from these options; you cannot wait until you receive your award to decide whether you wish to participate in the settlement.
(My edit: You will have a third option to OPT OUT!)
Please rest assured that our attorneys and staff will be available during the registration process to guide you through. ARCHER has indicated that there will be a point calculator and other tools available so we can help you choose the payment program that best fits your unique circumstances. We know that the payment timeline may not be as quick as some may have hoped. However, it is a result of 3M’s financial condition.
Expedited Payment Program

 Eligibility 

To be eligible for the Expedited Payment Program you must have either: (1) claimed tinnitus; (2) recorded tinnitus; or (3) hearing loss as defined in one of the below categories.

 Categories 

Payments for the Expedited Payment Program will fall into the below categories. We anticipate that the tools that will become available during the registration process will allow us to determine which category you will likely fall into, and we ask that you wait until these tools are available before asking us to guess.
Category Value Details
Tinnitus Only $5,000 Tinnitus - but no contemporaneous records to corroborate or confirm. No compensable hearing loss.
Recorded Tinnitus $10,000 - Records of diagnosis of tinnitus or sought treatment for symptoms within two years of last use of earplugs.
Slight Hearing Loss $10,000 - Defined as 15db Hearing Loss (shift) in a least one testing frequency.
Mild Hearing Loss $16,000 - Defined as a least 20db but no more than 35 db Hearing Loss (shift) in at least one testing frequency.
Moderate or Greater Hearing Loss $24,000 - Defined as 40db or more Hearing Loss (shift) at least one testing frequency.
If you qualify for more than one category and choose the expedited payment program, you do not add the values of each category together. You would instead receive the higher value of the two awards. For example, if you have undocumented tinnitus and mild hearing loss, you would receive $16,000.
Individuals who have at least slight hearing loss, as defined above, with documented tinnitus will also qualify for the Extraordinary Injury Fund, which will entitle them to payments beyond the values listed in the above chart.

 Timing 

The timing of payment depends on how many claimants decide to take part in the Expedited Payment Program.
It is anticipated that the majority of claimants who choose the expedited payment program will be paid before December 31, 2024. However, this is just an estimate, and we cannot guarantee this will be the case.
Full Evaluation Program
A Full Evaluation Program will also be an option for those who qualify. We anticipate that for some claimants this program will provide payouts that are far greater than the dollar values described above.

 Eligibility 

To be eligible for the full evaluation program you must (1) elect the full evaluation program at registration; (2) have mild or greater hearing loss; (3) execute a release or dismissal.

 Points System 

The Full Evaluation Program is based on a point system intended to pay compensation based on a more detailed review of hearing loss records. The points are assigned based on the severity of hearing loss as well as the link between hearing loss and the time period in which you wore the earplugs. Other factors that will be considered include whether the hearing loss is in one or both ears, your age, and whether you also have tinnitus. The final settlement amount will be calculated by multiplying a point total by a dollar value.
An Extraordinary Injury Fund will be available for claimants who have circumstances that are not adequately compensated by the point system. We will have more details on this at a later date.

 Timeline 

Those who choose the Full Evaluation Program will receive payments over time. The first payment will occur immediately after registering for the Full Evaluation Program. After that payments will likely occur over the dates listed below so long as certain conditions are satisfied: October 1, 2025 October 1, 2026 October 1, 2027 October 1, 2028 October 1, 2029
Our staff will continue to reach out over the next few weeks to collect additional documentation from you. If you have not completed a DOEHRs release it is absolutely imperative that you do so. We cannot prove your hearing loss without this document.
If you have any documents that will support your claim in the settlement, including hearing tests or a VA rating letter for tinnitus and/or hearing loss please send those documents to as soon as possible
This letter from a claimant (Mike C.) in the comments section found on the Lawsuit Center Blog highlighted below in Section A.
9/26 Update:
TIMELINE:
9/23/23: Judge Rodgers issued a new order (CMO #68) due to missing information like social security numbers and birth dates. The court’s order requires people who submitted these incomplete or wrong documents and their lawyers to fix the information. They needed to do this by 9/25/23.
9/26/23: Brown Greer completes deduplication from their records
9/27/23: Brown Greer provides ID Order list to Archer.
10/9/23: (Subject to change if Brown Greer is late) Target date that claimants will start receiving registration forms and releases (continues on a rolling basis). Should be done by around Veteran’s Day.
1/12/24: Initial Registration Date The initial deadline for submission of Registration Forms shall be four (4) months from the Reference Date (the “Initial Registration Date” from 9/12/23).
3/12/24: Final Registration Date Non-Participating Claimants who elect not to participate by the Initial Registration Date 1/12/24), but change their mind following the notice from Archer and/or a meeting with the Court and submit a Registration Form shall be treated as Registered Claimants.
Credit to: Darth_Username_1313
So if you’re going to “Opt Out”, or are undecided, this gives you a heads up on how much time you have to put a trial team / case together in case your current Attorney fires you.
MORE OPT OUT Information:
I don't know what folks have done individually in their efforts to find representation to OPT OUT, but having a solid number of folks get ahold of Attorney Ben Krause might be helpful towards this aim. He is already in Minnesota (home to 3M), and loves taking on big issues on behalf of Veterans. He specializes in Veteran Law and considers himself an activist. If you are not familiar with him, his website is here: https://www.disabledveterans.org/contact/ And if he won't be able to help, perhaps he'll know someone who can?
You can write directly to him here: https://www.disabledveterans.org/contact/
Or comment in his article questioning the 3M settlement here: https://www.disabledveterans.org/3m-earplug-lawsuit/ (COMMENTS NOW CLOSED) or here: https://www.facebook.com/benjaminlkrause/posts/811065261028584/?_rdr
Credit to Dewy Cheatum & Howe
9/24 Update:
Things have clearly slowed down in this post, I can see folks are still reading and passing information along but there have been no new comments for a while. As noted below, for the best up to date information you would be wise to check the following blog daily, and the comments at the bottom are worth reading as well.
https://www.lawsuit-information-center.com/13-million-3m-earplug-verdict.html
Some recent takeaways from there:
Free alternatives to PACER (PACER was discussed in Section H below):
https://www.docketalarm.com/
https://pacermonitor.com/
Evidently there was a 4th “wave” of cases that were being readied for trial when 3M settled. You can find out if you were in this 4th wave by reading the comment section on the Lawsuit Information Center’s blog and following the instruction in several posts from earlier this weekend. Again, there is no charge to get that information from docketalarm or pacermonitor. (If you were in the first three waves, you would already know this, as your case was worked up for trial already supposedly.)
Lastly, a couple of weeks ago I suggested people start saving information off the web as it will be getting taken down. Looks like it’s already starting, https://www.3mearplugmdl.com/ is no longer active. If you are thinking about opting out, you would be wise to glean as much of this stuff as you can while it’s still around. (Use the “way back machine” at www.archive.org to find copies of defunct websites such as that one.)
Good luck everyone!
9/20 Update:
As always, be vigilant, it sounds like there are some scammers circling around this case still:
https://www.reddit.com/VeteransBenefits/comments/16n7jo3/awko_dd214_direct/
If you are thinking about taking the settlement, please consider the following:
"FOR THOSE THAT KNOW THEY WANT TO SETTLE...:
(...AND WANT/NEED MONEY FAST):
Don’t forget the "FIFO" part of the deal. If I knew I needed money ASAP, I would be on the horn pronto with my law firm letting them know I want to agree to this settlement. As there is a “First in, First out” payment plan in place and this will mean I get paid fastest. Perhaps even prior to Christmas some say.
(...AND DON’T NEED MONEY FAST):
Wait for your firm to contact you with your settlement options. This will occur in the next four months. Every time you reach out to them you are probably accruing an additional “quarter hour” of billing time (or more). Read your retainer agreement if you don’t know how your firm is going to bill you. Be mindful of YOUR bottom line!"
Credit to Dewey Cheatum & Howe
The lead Attorney made it very clear in the 9/8 Judge Rodgers video (watch this in 9/12 update if you haven’t already seen it) that he is going to recommend 100% of his clients take the 3M deal, and not “opt out”. Reports confirming this are starting to trickle in on the Lawsuit Information Center’s comment section (see link in Section A if you haven’t already seen it – comments, are at bottom).
If you are thinking about “opting out”, you have a steep road ahead of you. Watch Jeff Marion video in Section C if you haven't already. Discuss your options with your Attorney. If they will no longer represent, you do NOT fire your Attorney, wait for them to petition the Court to remove themselves as your counsel. While that is going on, consider the following:
FOR THOSE THAT KNOW THEY WANT TO OPT OUT::
"First, read your retainer agreement. See if this is even an option. Some firms put language in place that allows them to decide whether you opt in or not. It sounds like most Attorneys are going to play this card (of “opting in”) for you if they can.
If after reading what you signed you find you still have the option to “opt out”, then consider the following:
Start researching Attorneys in your State (or licensed to practice in your State) NOW. It’s probably best to eventually consult with them (first appt. should be free), see if they are interested in “inheriting” your case should the need arise. Use Google, the yellow pages, call your cousin's spouse that's an Attorney, etc. etc. Prepare before you meet with them as best you can.
Gather up all your evidence, and either type or record your story. Bullet points (at a minimum) will help you remember things that are important. Take some sort of notes in to meet with your potential new Attorney. Find your DD214’s, comb through your medical records, comb through your non-medical records. (Obtain those records if you haven’t already!)
Once you are in front of an Attorney, you will want to inquire about the following potential damages: Past, present and future injury; loss of enjoyment of the capacity of life; emotional distress; lost wages and permanent impairment. There are probably others, inquire! If you are married, your spouse has a potential claim as well. There is a concept in the legal world called “loss of consortium” that you should inquire about. There may be other damages, inquire!
The time to start moving forward is NOW!
Remember, the deal is designed to funnel as many claimants as possible into the “Opt In” option. You do NOT have time to wait around to see what the next four months will bring before deciding to move forward, you need to be ready ASAP or you’re probably in for yet another surprise...."
Credit to Dewey Cheatum & Howe
Refer to Section N below to see how to start gathering your evidence.
A couple of interesting articles on the settlment:
https://www.stripes.com/opinion/2023-09-19/veterans-should-accept-3m-earplug-settlement-11426427.html
https://www.vfw.org/media-and-events/latest-releases/archives/2023/9/3m-lawsuit-ends-in-victory-for-veterans
9/18 Update:
You will be hearing from ARCHER about your proposed settlement anytime between now and 12/29. This is from slide 3 on the following link: https://3m-earplugsettlement.com/wp-content/uploads/2023/09/2023.09.08-BrownGreer-CMC-Presentation.pdf
You would be wise to send every piece of evidence you have to your Law Firm ASAP, along with your story explaining how you were damaged. If you are/were married, let them know this too, it may mean more damages. They will send this to Archer who will compute your damages off this. (If you don't have evidence, I have added a "how to get get your evidence" section down below in Section N. It is worth doing this as not all Law Firms may have able to get this information, as at some point in the past Judge Rodgers put a hold on this so as not to flood the VA / DOD - or so I've read anyway.)
The Lawsuit Information Center Blog (link in Section A below) is reporting that 12,000 more cases were removed by Judge Rodgers from the MDL, making the latest update 242,604 cases. Evidently this number will continue to move around for a while. One commenter on that blog thinks it may go to as low as 170k before its all said and done. This would be great news, as more money for the EIF fund, etc. Link here to most recent data:
https://www.jpml.uscourts.gov/sites/jpml/files/Pending_MDL_Dockets_By_Actions_Pending-September-15-2023.pdf
If folks are thinking about participating on the Lawsuit Information Blog, please don't use your Facebook account. Most if not all Law Firms are strongly suggesting you maintain "radio silence" on your case for a reason, don't DOX yourself in case you eventually end up in court with 3M (via opt out option OR any other scenario), and if you already have, be very careful about what you say. Don't say things like "I only have this problem", or "My symptoms aren't that bad...", etc. Don't "incriminate yourself!"
9/17 Update:
This is an expansion on Section E below. There are actually 3 Special Masters, links to each one below, fourth link explains what they do.
John Perry (MSA Master) - https://www.acctm.org/jperry/
Matt Garretson (Special Administration Master) - https://wolfgarretson.com/about
Chris Klotz (EIF Master) - http://www.samklotz.com/Bio/JKlotz.html
If anyone contacts them with questions, please share with us what they say! (You might be wise to jot their phone numbers and email addresses down in case these get temporarilty removed later.)
Here's how they fit into the grand scheme of things:
https://3m-earplugsettlement.com/court-appointments/
9/16 Update:
~15 minute video from AWKO Attorney who worked on the Settlement as well as most of the Bellweather Cases:
https://www.youtube.com/watch?v=7h7wWCN_WQc
~9 minute video update from Attorney Jeff Marion on Settlement (He was Attorney shown in Section C down below):
https://www.youtube.com/watch?v=HggGit0M6EI
This is probably the most interesting find of the day - MDL data from a half year ago. It shows various ways that damages could be accounted for. A) Turns out my "Fancy Pants Math" was pretty close in guessing the number of tinnitus vs. hearing loss (2/3 vs. 1/3), only it was FLIP-FLOPPED (lol). About 1/3 of claimants have tinnitus only, and 2/3 have hearing loss & tinnitus. B) Almost 150k cases were dismissed by FEB '23!
https://www.flnd.uscourts.gov/sites/flnd/files/Data%20Day%20Presentation%202-23-23.pdf
9/12 Update:
See here for nice summary of Archer portion of 9/8 presentation: https://www.reddit.com/Veterans/comments/16gtbyl/3m_earplug_comprehensive_case_management_notes/ Credit to: u/Separate-Series-8175
The slides used to compute the information above can now be found at the bottom of this page: https://3m-earplugsettlement.com/court-documents-2/
The 9/8 hearing in which the slides were discussed can be found in the video here: https://www.flnd.uscourts.gov/3m-products-liability-litigation-mdl-no-2885
9/11 Update:
There was a deadline of 11:59pm central time 9/12 to have your case registered.
See Section L below for more information.
9/10 Update:
I am back from a 72 hour “timeout”, courtesy of Reddit. Their bots (or whomever) determined I was “spamming” too many people w/ information about how to get in on this settlement...lol Thus, the lack of updates over the weekend! My sentence is now over, and I’ll go back to mainly reading soon. I’ve been posting about this case since joining Reddit, and I’m ready to kick back and see where it goes from here.
Video of Friday 9/8 hearing:
https://www.flnd.uscourts.gov/case-management-conference-no-23-zoom-recording
SKIP the first 35 minutes or so if you want to avoid all the "thank you's" and praise.
9/7 Update:
I had done some computations (aka: Fancy Pants Math) below, and cleaned it up and put it in the original post. But it got lost in an update, and I didn't have it saved to my hard drive. It was never very accurate, it just looked at averages, and is now pretty outdated since 9/8 hearing.
Here is a PDF purported to show that the VA will not try to "claw back" funds for related medical care and that particpants will not lose benefits due to settlement.
https://www.reddit.com/media?url=https%3A%2F%2Fpreview.redd.it%2Fr8bfng50otmb1.jpeg%3Fwidth%3D750%26format%3Dpjpg%26auto%3Dwebp%26s%3Dc250643b0bac35d27c0ea1133f05aef6847fc7b5
Here is a link to story noting Judge Rogers has ruled that lenders cannot front claimants money w/o her prior approval:
https://www.reuters.com/legal/transactional/3m-judge-issues-extraordinary-order-shut-down-predatory-litigation-funding-2023-09-05/
9/6 Update:
Here is a PDF showing the Judge's timeline: https://www.flnd.uscourts.gov/sites/flnd/files/3M-Settlement%20Implementation%20Timeline.pdf
Here is the current "master website" for the injured, LOTS of great information on there! https://www.3mearplugmdl.com/filings-and-key-documents
9/5: TLDR (see below for more detail on each section - new stuff that was posted in Section M is now posted above:
A. LAWSUIT INFORMATION CENTER. Good resource for the times you don’t hear anything from your own Attorney. (You know, like pretty much ALL the time! :-)
B. VIDEO WITH ATTORNEY MIKE BURNS. He worked on settlement, and breaks it down.
C. VIDEO WITH ATTORNEY JEFF MARION. He talked about how hard it will be to opt out.
D. ARCHER SETTLMENT COMPANY. These guys are getting paid big bucks to sort the settlement all out it looks like…
E. SPECIAL MASTER IS ATTORNEY JOHN PERRY. If you don’t take the “expedited payout”, this is the guy ranking cases by some sort of points system.
F. SEC FILING. Fofrom this 3M settlement. Important to Wall Street perhaps?
G. ATTORNEYS FEES. Time to re-negotiate!?!
H. PACER. This is like the legal version of MyHealthEVet. You can start by checking to see if they filed your case, and find out who your Lawyer is (if you forgot), etc.
I. HEARING LOSS DEGREE OF DAMAGE. My thoughts (NOW DELETED - SEE 9/28 UPDATE INSTEAD).
J. WEBSITE WITH THE ACTUAL COURT FILINGS: Super handy if you can wade through legal writing (aka: legalese)! And free!
K. YOU ARE ALREADY INVOLVED IN THE LAWSUIT, WHAT DO YOU DO NEXT? Reach out to your Attorney!
L. YOU ARE NOT INVOLVED IN THE LAWSUIT YET, WHAT DO YOU DO NEXT? Talk to your local personal injury lawyer about starting a new MDL?
M. MISC: This was originally for additions after the fact! (But they are now added above.)
N. HOW TO GET EVIDENCE (AUDIOGRAMS, SERVICE CONNECTION LETTERS, MEDICAL NOTES,ETC.) There are lots of ways you can get your own evidence w/o running up additional costs through your Law Firm.
If you have other helpful information or websites, please link below or create your own post!
A. LAWSUIT INFORMATION CENTER:
The Lawsuit Information Center has done the best job of keeping folks up to date on this case the past few years. They are reporting $8333 per client is the starting point for the settlement. Their website is here, and the comments at the bottom are excellent: https://www.lawsuit-information-center.com/13-million-3m-earplug-verdict.html.
B. VIDEO WITH ATTORNEY MIKE BURNS: (PRETTY MUCH EVERYTHING THIS GUY PUT OUT HAS COME TO PASS, 30 DAYS LATER!) $8333 (as noted above) becomes $5000 when you take 40% lawyer’s fee off. So, I think this is where Mike Burns (Attorney who worked on settlement) gets this number from in the next video. He also talks about 3 “lanes” – expedited, review and opt out (he may use slightly different terms). He also talks about settlements at $5k, $10k, $16k and $24k. Based on my $5k math above, I suspect all four of these numbers are after Atty’s fees (which are typically 40%). GREAT video, please watch if you haven’t already! https://www.youtube.com/watch?v=9reeN-q9LbI
C. VIDEO WITH ATTORNEY JEFF MARION: One more video (this one is short). This Attorney talks about the fact that it will be difficult for many to go Option 3 (opt out), as there is language in MANY retainer agreements saying that if your Law Firm thinks this is a good deal, then they can force you to take it. (My retainer does NOT say this thankfully. If you don’t have a copy of what you signed, get one from your law firm ASAP, this is super important if you are thinking about opting out. https://www.youtube.com/watch?v=6JeNxyPpsnA So, if you think you will opt out and take a shot at this yourself, you might be wise to start saving evidence online now before 3M has everybody scrub it from the web. There are a lot of firms with good information up about this case. It appears that part of “opting out” may require your current law firm to no longer represent you, and I’m sure all the big firms involved in this settlement are tied to this agreement as well. Your best bet then may be some new law school graduate in combination with yourself most likely?!? If you are thinking about "opting out", please review (especially the second half) of Judge Roger's Case Management Order 57, link below.: https://www.uscourts.gov/courts/flnd/3M-Case_Management_Order_No_57_Docket_Control_Order_and_Exhibits_1-3.pdf
D. ARCHER SETTLEMENT COMPANY: Archer is going to run the settlement. Look for info. from them, don’t blow them off! https://www.archersystems.com/mass-tort/
E. SPECIAL MASTER IS ATTORNEY JOHN PERRY: (AS NOTED ABOVE IN 9/17 UPDATE, THERE ARE MULTIPLE SPECIAL MASTERS.) John Perry is going to be a Special Master. https://www.acctm.org/jperry/
F. SEC FILING: Some think the SEC filing is important, it can be found here: https://www.sec.gov/Archives/edgadata/66740/000006674023000073/a829238kex-991.htm
G. ATTORNEY'S FEES: Note that you can (at least) inquire whether its possible to re-negotiate the fee you agreed to pay your Attorney. I read one comment online that said they were going to threaten to “opt out” unless their Attorney cut their high rate, which they deemed unfair given the rather disappointing settlement numbers... (Then their soon to be former Attorney would get 100% of $0…!) See more ideas here: https://www.legalmatch.com/law-library/article/contingency-fee.html
H. PACER: (SEE UPDATES ABOVE ON 9/24 FOR FREE PACER ALTERNATIVES) (EVERYONE MAY NOT BE IN PACER. CALL YOUR LAWYER IF QUESTIONS!) You can search to see if your law firm filed for you here in PACER. It will require a credit card to search immediately, and it appears each search costs 10 cents. You can also search for rulings, etc. from the case in here. (Super helpful if you forgot who your law firm is/was!) https://www.uscourts.gov/court-records/find-case-pacer For a complete record of the filings in this matter to date, please log into PACER and review the master MDL docket, Case No. 3:19md2885
I. HEARING LOSS DEGREE OF DAMAGE: (SEE letter 9/28 update for correct info.)
J. WEBSITE WITH THE ACTUAL COURT FILINGS: https://www.flnd.uscourts.gov/3m-products-liability-litigation-mdl-no-2885 You can read the settlement, the case management orders, etc. You can dig into the previous year’s (2019>2022) rulings, etc. by using PACER (or its free alternates).
K. YOU ARE ALREADY INVOLVED IN THE LAWSUIT, WHAT DO YOU DO NEXT? It looks like folks already involved have until 9/12 to update their contact information with their Attorney’s. Probably helpful to let them know if you got married or divorced since first signing up as well. They will be reaching out to you soon. Reach out to them if you have a new email and/or cell phone though!!
See timeline up top for the next deadline (which I think is 4 months out no,w).
L. YOU ARE NOT ALREADY INVOLVED IN THE LAWSUIT, WHAT DO YOU DO NEXT? Folks had until 9/12 to throw their hat in the ring and get signed up for THIS MDL. (PROPS to the Mods on this subreddit for allowing this information, it has helped a lot of people!)
M. MISC: (EVERYTHING THAT WAS IN HERE GOT MOVED TO TOP!)
N. HOW TO GET EVIDENCE (AUDIOGRAMS, SERVICE CONNECTION LETTERS, MEDICAL NOTES,ETC.) This INTEL comes from the comment section in the Lawsuit Information Blog:
https://www.lawsuit-information-center.com/13-million-3m-earplug-verdict.html
Here's how to get your: military medical records, VBA service connection information, military records (non-medical) and VHA medical records.
TLDR: A. Getting your military medical records via (A) VA, (B) National Archives & (C) Tricare
B. Getting your VA service-connection records (VBA)
C. Getting your military records (non-medical)
D. Getting your VA medical records (VHA)
A(A). If you have ever filed a claim for service-connection, then the VBA has your military medical records. This will obviously be most of us. How / where these are stored may vary a bit by V.A. Regional Office (VARO) - each state has their own VARO. These CAN be printed off and given to you in a personal visit to your VARO, but most places won't do this as they have other things they need to do. So call your VARO to see if this is an option before you drive a long ways there! Most places will want a signed ROI mailed in and it will probably take months to get them. (It took me a year and a half the first time I tried!) It might be best to use VERA to call and inquire at your specific VARO, go here to make an appointment:
VERA Link: https://va.my.site.com/VAVERA/s/
It's probably also wise to send in a FOI request pronto as well. For several years you could email these in, but that might no longer be the case. The folks on a VERA call would know.
A(B). If you have never filed a claim for connection, then your medical records are in St Louis in the National Archive. It was super slow to open after COVID, and super backed up for a long time afterwards as a result, but hopefully this has gotten better. [This is the same place that folks can get their DD214(s) from.] National Archives Link: https://www.archives.gov/veterans/military-service-records/medical-records.html
A(C). For those of you in from around 2005 to about 2020, your medical records should be on TRICAREONLINE.
https://www.tricareonline.com Please google if the link doesn't work.
You may have to make an account to log in. This site is scheduled to go away at the end of October 2023 as they transition to Genesis. I do not think you will be able to access your records after that date, so don't wait. They say they will be available until Oct 2024, but i have doubts.
All of your encounters (clinical appointments) should be under the ENCOUNTERS tab. Some audiology appointments merged your audiograms results to the clinic appointment.
HOWEVER, VA DBQs and some Audiograms will be under the DOCUMENTS tab. This is very, very important. When you download your medical records via the 'BLUE BUTTON', you DO NOT get anything from the DOCUMENTS tab. This is why many who downloaded their records that way do not see all of your audiograms. You will have to do that manually.
Simply click on DOCUMENTS on the left hand side. Put in the date range you think your records will reside. Each entry will have a download button on the right hand side. If you open up the entry, there will be another button to click to view the encounter or document that should automatically download that document.
Again, this site is scheduled to go away next month so the clock may be ticking against you.
Up to this point - A(C). CREDIT TO: ~shadd
I logged into Tricare and my medical records showed the dates of audiograms, but I could not find the actual audiogram. I kept looking around the site and got to the point where I could select what I was looking for I selected “notes“ and a list of notes came up all with the same date, I decided to click on one of the notes and a whole slew of documents came up. As I went down the list BOOM there they were. To open the notes. You have to click on the middle section which was blank on mine. This information was not available to me on E benefits as I didn’t start going to the VA until I got out of the military.
Last Paragraph of A(C). CREDIT TO: ~Chad S.
B. If you need service-connection records, those are at the VBA. You should request a complete copy of your c-file at some point, if you don't already have it. (No, this will not be a claim if you are worried about keeping your P&T rating.) You can do this by FOI request at your local VARO.
You can print out your service-connection award letters here: https://www.va.gov/track-claims/your-claim-letters
See here for C-file requests (will contain ALL of your VBA data): https://www.reddit.com/VeteransBenefits/wiki/cfile/#wiki_how_to_request_your_c-file
C. You can download your non-medical military records here through DPRIS. (Depending on your situation, there may be hearing related data in here, there was for me.) They are typically ready PDQ, about as quickly as MyHealthEVet records are.
DPRIS access: https://milconnect.dmdc.osd.mil/milconnect/public/faq/Documentation-DPRIS (This worked for me using my old eBenefits login information.) May also be available on the newer va.gov version of eBenefits.
I have NO IDEA how to access your clothing records. I'm under the impression those are given to you when you separate or they are destroyed, but this may not be accurate anymore.
D. You can get all of your VHA medical records from the last decade or so from MyHealthEVet. Older stuff (paper copy era) will need to come from the Medical Center's records office. You will need a signed ROI for the older non-electronic health records. CBOC's will be attached to a specific Medical Center's records office, they don't have their own. If you received treatment from different Medical Centers or CBOC's prior to the MyHealthEVet era (early 2010's) - you'll need to contact each Medical Center's records office for those records. MyHealthEVet: https://www.myhealth.va.gov/mhv-portal-web/home (May require a visit to your Medical Center for full access)
About 5 years ago or so (maybe a bit longer), the VA stopped putting C&P exams in your VHA medical records. But there was a period of time, say up to 2017(ish), where they did. This will likely vary a bit by Medical Center, but some of you, this simply means less work for you if that is what you are looking for. VHA sites (Medical Centers, CBOC's), will NOT be able to get your military medical records. They do have more access to some of the data from your military records (in CPRS) than they used to, but not what you need. The VA is required by law to keep your medical records for 75 years, don't let some new hire tell you differently. Your older records may not be stored at their Medical Center anymore, but they are stored somewhere in the VISN (Network), and you can get access to them. May require some tenacity, but I assure you they exist.
It's been a few years since I was regularly dealing with all of this stuff, so some things may have changed, YMMV.
Section N. credit to: Dewey Cheatum & Howe (unless otherwise specified)
Lastly, I Am Not A LAWYER (IANAL) and this is NOT legal advice!
Good luck to all!

I have code to loop adding rows to an excel file. I use a variable i to loop through the number of rows. For each row I add I want to merge cells C and D in that row. Using the debug tool I see that the code in the title is only selecting the C column cell. Why is this, and how can I modify this line to select C and D in each row so that the .Merge command actually does something? I can provide the full code upon request

I would like to repost this question, now I am asking how to write a function script, I would like to do it with code.
I am not familiar with Google scripts, I know excel VBA a little.
Below are sample sheet (sheet name = "Credit Card"), and code structure logic. It is just basic logic, explain what I want to achieve, please refer to comments for more detail.
How to write a function and assign A2value to cell A2?
​
//A2:C2 is merged cell
//Top rows(frozen rows): no negative numbers
​
​
A2Value = ""
//define initial value for cell A2 or A2:C2 as blank.
​
​
//I can do it manually, no need to go with a FOR loop
​
If SUMIF(IF(B:B<0,C:C),"SoFi Mastercard",B:B) < 0 Then
A2Value = "SoFi:$" & SUMIF(IF(B:B<0,C:C),"SoFi MasterCard",B:B) & "; "
End If
//Check column C, if Column C value is "SoFi Mastercard", then add the negative numbers together.
//I don't really mean writing SUMIF in the code, I mean turning the SUMIF logic into the code, or turn below logic into code. For each cell in column C, if the value is "SoFi Mastercard" and its column B value is negative, then add all negative numbers together. In the screenshot, the total of -$6.5 and $-8 is $-14.5
//At this point, I should expect A2Value = "SoFi:$-14.5; "
//I manually use SoFi in the output value, instead of using SoFi Mastercard in the output value, in order to keep A2Value short. That is why I don't want to do it with FOR loop. I will manually do a few IF statement.
​
​
​
If SUMIF(IF(B:B<0,C:C),"Citi Costco Visa",B:B) < 0 Then
A2Value = "Citi Costco:$" & SUMIF(IF(B:B<0,C:C),"Citi Costco Visa",B:B) & "; "
End If
//At this point, there is no negative transaction for Citi Costco Visa, therefore, A2Value remains as "SoFi:$-14.5; "
​
​
​
If SUMIF(IF(B:B<0,C:C),"Amazon Visa",B:B) < 0 Then
A2Value = "Amazon:$" & SUMIF(IF(B:B<0,C:C),"Amazon Visa",B:B) & "; "
End If
//At this point, I should expect A2Value = "SoFi:$-14.5; Amazon:$-7.49; "
​
​
​
If A2Value <> "" Then //if A2Value is not blank, and there is negative transaction in column B
Remove last two characters off A2Value, which removes last empty space and semicolon
At this point, I should expect A2Value = "SoFi:$-14.5; Amazon:$-7.49"
Assign A2Value to cell A2 or cell A2:C2
In the end, cell A2 should be SoFi:$-14.5; Amazon:$-7.49
End if
​
​
Edit: Here is sample sheet https://docs.google.com/spreadsheets/d/1YajaybQMK0wM4yDH3-Akd--4fleenUKrfLO7WxZQSUo/edit#gid=0
from above description, I want to return SoFi:$-14.5; Amazon:$-7.49 into cell A2:C2
Another point: I don't know how many transaction rows, so it is dynamic, the code should loop through all the way to the bottom of sheet

SO Ive got two sheets, sourceSh and DestSH. in the main code I call on this function to handle merged cells. it all works when i have the DestSh open on my excel vindow and im running my code through the VBA text editor. but in the final form of my file i will have a button in the SourceSh, and I've just foundout that my code does not work when anything other than my DestSh is open or at least if my SourceSh is open. Here is the function:

'here n is where you want to end counting the merged cells so pass LastRow from main sub Function NumberOfMergedRows(n As Long, a As Integer) As Integer MsgBox "starting to count from :" & a For i = a To n RowCount = Range("A" & i).MergeArea.Rows.Count If RowCount > 1 Then 'MsgBox ("Cell [A" & i & "] has " & RowCount & " merged rows") i = i + RowCount - 1 'note -1 here NumberOfMergedRows = i Exit For End If Next i End Function 

here is how i call it:

 'locates the start and end of the current restaurant RestaurantStart = StartOfMergedRows(LastRow, RestaurantStart) RestaurantEnd = NumberOfMergedRows(LastRow, RestaurantStart) . . . 

If i runt he code while destSh is open i get an error 1004: Methond of 'Range' of object'_Global' failed on this line RowCount = Range("A" & i).MergeArea.Rows.Count . isthere a way for me to casll this function so that it only looks at the DestSh? I've already tried doing DestSh.RowCount = Range("A" & i).MergeArea.Rows.Count but that did not work. maybe the Sheet can be worked in as a variable that needs to be passed into the function, like in my case n As Long, a As Integer is?

Hi. I'm trying my first steps into vba programming, as my job shows many small opportunities for improving automation. I recently watched a course on youtube, and decided I could at least start writing something instead of just watching the teacher do that.
One of the problems I'm faced with is that I'm trying to improve an excel file that has a form inside. Said form gets compiled with data, then the data is copied to the worksheet, creating a dedicated row.
Think of having 200+ variables already populated that need to be put in the worksheet. My code is the following:

Dim sh As Worksheet Dim DataArray As Variant Set sh = ThisWorkbook.Sheets("SheetName") DataArray = Array(v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14) ' I did a test with just the first 14 values of the row sh.Range("A4").End(xlDown).Select ' cell a4 is selected bc the guy who made the worksheet merged the first three rows of every column and that makes the command not work properly ActiveCell.Offset(1).Value = DataArray 

What it should do:

• create an array of fixed size populated with the already defined variables
• find the last populated row in the worksheet, then go lower by 1 (first empty row)
• paste the value of each variable loaded in the array in each cell of the worksheet

What it does:

• when it gets to the paste phase, only the first variable's value is paste
• If I stop the code right before that, I can see in VBE that the values are actually loaded in the array, so I don't understand why the don't get pasted

Hello everyone,
I am trying to insert Arabic numbers in excel, but after using customer type and setting cell format, it only reflects as display but in the above formula bar it still shows as English numbers. So when I try to use mail merge in word docs to extract data from excel, the Arabic numbers gets converted back to English.
So in a brief I am trying to create an excel sheet with Name & Age & ID ( the ID & Age are in Arabic ) and then extract these data to a word doc using mail merge feature while retaining the Arabic numbers.
I tried to use custom function in VBA, but VBA doesn't read Arabic numbers eithers.
Tried to install Arabic languages packs / change language from control panel. Nothing is working.
Any Ideas??
​
https://preview.redd.it/65dpi78akkeb1.png?width=802&format=png&auto=webp&s=f4504d7ab4403be2a39491cdc58f8a23f47de618

I have copied data from multiple source sheets to a destination sheet and i need the destination cell to merge with the one below it after merge. However each consecutive pasting goes to a new line.
So far ive got this
If lastRow < 3 then destinationSheet.cells(3, "A").Value = Cells(targer.row, "C")
Else destinationSheet.cells(lastRow + 3, "A").Value = Cells(targer.row, "C")
( lastRow here is the last row that has cell in column C not blank)
Ive fond examples of "sheet1.range("A1:A2"). Merge"
But how do i translate this type of selection "lastRow + 3, "A" ( with the looking for next empty row) to this type of selection range("A1:A2")?

Hey Team.
Hope you are doing well.
Heheh. Apologies. Force of habit.
I've joined Deloitte in Dec 22. And it's been a bumpy road for me.
So, my concern is how to tackle a situation.
The situation - I am pretty good with Excel and VBA. I was given a Task to write a code to read an Excel file( exported from pdf ). Basically, to read a pdf file in Excel. The Excel file have merged cells and no consistency, yet i figured a way to read 90 percent of data correctly. My manager(that handles automation for the team, been here for 10+ years) asked the approach. I told him. I used dictionary to store the starting row and ending row of the dataset and used for loop/ifs to check data.
His reply made me think the decision of joining Deloitte. He literally said "what made you use dictionary, you could have used Google. People Google now a days". I had to explain him, dictionary and arrays.
And he jockingly said. Just use for loops. Everything with be fine.
Guys, after all this, he demands to read that file with 99+ accuracy. He also suggested that, the data I wasn't able to locate can be highlighted( and should copy/paste into other file - for future reference). Like if i could read it, i would not have missed it.
Help me understand, what can I do? I need to make him understand what can I do and what i can't. How can Deloitte make him a manager to lead teams? I feel, I am unable to utilise my full knowledge.
Also, he's too dumb. If someone tells him to pick things from right, his question would be your right or mine?
Edit - the ones who are downvoting are not better than my manager. I asked for suggestion, not for judgement.