Hello everyone I have written a line of code that goes down a column and does a vlookup. the results of that function are then placed into an array to later be changed. some of the lookup values are not in the lookup range and thus returns an error. I am trying to place this column in an array, change the values of error to 0 or an empty cell, then replace the original column with the array. here is what i have written but i keep getting a type mismatch error on line:
If myArray(x) = "#N/A" Then myArray(x) = Empty
here is the code in question.
Dim myArray() As Variant
Dim DataRange As Range
Dim cell As Range
Dim x As Long
Set DataRange = ActiveSheet.UsedRange
ReDim myArray(DataRange.Cells.Count)
For Each cell In DataRange.Cells
myArray(x) = cell.Value
x = x + 1
Next cell
For x = LBound(myArray) To UBound(myArray)
If myArray(x) = "#N/A" Then myArray(x) = Empty
Next X
End Sub
When i print the array the errors's turn into Error 2042 i have tried replacing the if with
if myArray(x) = "Error 2042" then myArray(x) = Empty
but this still returns a type mismatch error
String. So you can't compare an actual#N/Aerror to the strings"#N/A"or"Error 2042". See the linked thread for the correct approach.Range.SpecialCells(xlCellTypeErrors), no need for an intermediate array. And even if you were to use an array, it's better to use this approach to create it, modify it, and write it back to the sheet. No need to loop over each cell.