👀 Views: 66 💬 Answers: 1 📅 Created: 2025-07-07

Excel VBA Application.WorksheetFunction

I'm currently working on a VBA script that processes a list of numbers in an Excel sheet to calculate the square root of each value using `Application.WorksheetFunction.Sqr`. However, I'm running into a 'Type Mismatch' behavior when trying to iterate over the cells in a loop. Here's the relevant part of my code: ```vba Sub CalculateSquareRoots() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim sqrtValue As Double Set ws = ThisWorkbook.Sheets("Data") Set rng = ws.Range("A1:A10") For Each cell In rng sqrtValue = Application.WorksheetFunction.Sqr(cell.Value) cell.Offset(0, 1).Value = sqrtValue Next cell End Sub ``` I've checked the range and confirmed that it contains numeric values, but I'm still receiving the 'Type Mismatch' behavior at the line where I call `Application.WorksheetFunction.Sqr`. I also added behavior handling to skip any non-numeric values, but it doesn't seem to make a difference. The code runs fine if I manually enter numeric values into the cells, but fails when I have formulas or blank cells present. I've tried modifying the loop to include checks for numeric values using `IsNumeric()` but that didn't resolve the scenario. Any advice on how to handle this scenario, especially when dealing with both formulas and empty cells in the range? I’m using Excel 2019. Thanks!