Mismatch error at end of loop - blank cell the problem?

[kyleharry]

Hi - I am a complete newbie to VBA, but my boss asked me to find a 1-click way for my less tech-savvy colleagues to modify a spreadsheet we receive regularly from a third-party.

In column E is a list of currency values. I need to copy the original values from E to D, then replace the values in E with E multiplied by a decimal number entered into input box by the user.

In a seperate module I make sure to set the cell format in both D and E to "Currency".

Sub Check()
    Dim rng As Range
    Dim i As Long
    Dim comMultiplyer As Variant
    Dim origCellValue As Variant
    
    'ask for commission multiply variable from user
    comMultiplyer = InputBox("Paste the correct commission multiplyer.")


    'Set the range in column E you want to loop through
    Set rng = Range("E6:E250")
    For Each cell In rng
        'test if cell is empty
        If cell.Value <> "" Then
            'write to adjacent cell
            cell.Offset(0, -1).Value = cell.Value
            cell.Value = cell.Value * comMultiplyer
        End If
    Next
End Sub

The loop seems to complete fine apart from it gets to the cell after that last non-blank cell in E where is throws the mismatch error. I have tried CDec on various parts of this, but I'm not sure what I am doing with that.

Any suggestions?

Kyle

 

[Norie]

Kyle

You could check the cell has a numeric value before multiplying.

If IsNumeric(cell.Value) Then
    cell.Value = cell.Value * comMultiplyer
End If

 

[kyleharry]

Thank you! This worked.

Kyle

You could check the cell has a numeric value before multiplying.

If IsNumeric(cell.Value) Then
    cell.Value = cell.Value * comMultiplyer
End If