Value.2 type mismatch

Question

I got this code that divides a range of values (the values have a number format like 9.12) by 100.
Before dividing, I format the range by applying a percentage format so the value becomes 912%
and after dividing it becomes 9.12%
The range also has blank cells (and zero values), which after dividing they take a value of 0%.
So I need to clear all the values that are zeros, and for this, I tried this code, but got an error "Type mismatch".

Dim ColSelRange as Range
Dim DivCell     as string


Set ColSelRange = Range("B2","B100").Select
DivCell = "A1"
Range(DivCell) = 100
Range(DiCell).Copy

ColSelRange.Select

        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide, _
            SkipBlanks:=False, Transpose:=False
      

        If Selection.Value2 = "0%" Then

            Selection.ClearContents

        End If

Is there any way to bypass this error? or does exist other different solutions (without using for loops)?

Recommended reading: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Sep 15 '22 at 16:44
you cannot compare multiple cells to a single value. You will need to loop the range and test each individually. — Scott Craner, Sep 15 '22 at 16:55
@ScottCraner - yeah good point, I got caught up on the `Set ColSelRange = Range("B2","B100").Select`, which would throw an RTE 424, not a type mismatch... — BigBen, Sep 15 '22 at 16:57

score 0 · Accepted Answer · answered Sep 15 '22 at 19:57

Loops are really a great way to do things... But use arrays. Much-many-extra-fast-wow.

The below is your code converted to a really quick and easy conversion using arrays and loops.

Option Explicit

Sub Convert_To_Percent()
    
    Dim ColSelRange As Range    'Conversion Range
    Dim RangeArray()            'Data Workspace Array
    Dim I As Long               'Iterations
    
    Set ColSelRange = Sheet1.Range("B2:B100")   'Set Range
    RangeArray = ColSelRange                    'Move values from range to array
    'Range("A1") = 100                          'Not required, but you might still need it...
    
    For I = 1 To UBound(RangeArray, 1)                  'Iterate from 1 to upperbound of array
        If RangeArray(I, 1) <> 0 Then                   'If not 0
            RangeArray(I, 1) = RangeArray(I, 1) / 100   'data = Data/100
        End If                                          'End if
    Next I                                              'Next data item
    
    ColSelRange = RangeArray            'Output data to range
    ColSelRange.NumberFormat = "0.00%"  'Reformat range
    
End Sub

Value.2 type mismatch

1 Answers1