👀 Views: 25 💬 Answers: 1 📅 Created: 2025-06-21

excel vba aggregation

I'm migrating some code and I've searched everywhere and can't find a clear answer... Hey everyone, I'm running into an issue that's driving me crazy. I'm relatively new to this, so bear with me... I'm trying to write a VBA script that aggregates data from multiple sheets within my Excel workbook into a summary sheet. However, I'm working with a 'Type Mismatch' behavior when I attempt to assign values from the cells. Here's a snippet of my code where the behavior occurs: ```vba Dim ws As Worksheet Dim summarySheet As Worksheet Dim lastRow As Long Dim aggregateRow As Long Set summarySheet = ThisWorkbook.Sheets("Summary") aggregateRow = 2 ' Start from the second row of the summary sheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row summarySheet.Cells(aggregateRow, 1).Value = ws.Name ' Grab the sheet name summarySheet.Cells(aggregateRow, 2).Value = ws.Cells(lastRow, 1).Value ' Last row of column A aggregateRow = aggregateRow + 1 End If Next ws ``` The 'Type Mismatch' behavior occurs on the line where I'm trying to assign `ws.Cells(lastRow, 1).Value` to `summarySheet.Cells(aggregateRow, 2).Value`. I’ve checked that all sheets contain data in column A, but I’m not sure if there’s an scenario with data types. I also tried adding a check to ensure that the last row is not empty before the assignment: ```vba If Not IsEmpty(ws.Cells(lastRow, 1).Value) Then summarySheet.Cells(aggregateRow, 2).Value = ws.Cells(lastRow, 1).Value End If ``` But the behavior continues. I'm using Excel 2019, and I suspect it might be related to some non-numeric values in certain sheets. How can I resolve this behavior and ensure that I can aggregate data properly? Any best practices for handling different data types in this context would also be appreciated. This is part of a larger API I'm building. Has anyone else encountered this? I'm working on a CLI tool that needs to handle this. Is this even possible? For reference, this is a production web app. I'd really appreciate any guidance on this.