CODEWITHSUNDEEP
vbaexcel
Raul Gonzales
Raul Gonzales

Reputation: 906

VBA type mismatch when code runs and cell is empty or has no value

I have the following IF Statement:

If Cells(i, 4).NumberFormat <> "0.0%" Or IsEmpty(Cells(i, 4)) Or Cells(i, 4).Value2 = "" Then
    Cells(i, 4).NumberFormat = "0.0%"
    Cells(i, 4).Value = Cells(i, 4).Value / 100
'Else
    'Cells(i, 4).Value = Cells(i, 4).Value
    'Cells(i, 4).Value = Cells(i, 4).Value
End If

When I launch the code, it runs for every cell that has data in it but,

if the cell is empty it does not run and gives me an error saying "Type Mismatch"

Here is the whole code:

Public Sub SortMyData()

Dim i As Integer
Dim N_Values As Integer

N_Values = Cells(Rows.Count, 2).End(xlUp).Row

For i = 6 To N_Values
    'Cells(i, 3).NumberFormat = "0"

    If Cells(i, 2).NumberFormat <> "0.0%" Then
        Cells(i, 2).NumberFormat = "0.0%"
        Cells(i, 2).Value = Cells(i, 2).Value / 100
    'Else
        'Cells(i, 2).Value = Cells(i, 2).Value
        'Cells(i, 3).Value = Cells(i, 3).Value
    End If

        If (Cells(i, 3).Value) > 1000000 Then
           Cells(i, 3).Value = Cells(i, 3).Value / 1000000 & "Mb"
           Cells(i, 3).HorizontalAlignment = xlRight

            ElseIf (Cells(i, 3).Value) > 1000 Then
                Cells(i, 3).Value = Cells(i, 3).Value / 1000 & "kb"
                Cells(i, 3).HorizontalAlignment = xlRight

            ElseIf Cells(i, 3).Value = Null Or Cells(i, 3).Text = Null Or Cells(i, 3).Value = "" Or Cells(i, 3).Text = "" Then
                Cells(i, 3).Value = 0
                Cells(i, 3).HorizontalAlignment = xlRight
        End If

            If Cells(i, 4).NumberFormat <> "0.0%" Or IsEmpty(Cells(i, 4)) Or Cells(i, 4).Value2 = "" Then
                Cells(i, 4).NumberFormat = "0.0%"
                Cells(i, 4).Value = Cells(i, 4).Value / 100
            'Else
                'Cells(i, 4).Value = Cells(i, 4).Value
                'Cells(i, 4).Value = Cells(i, 4).Value
            End If
Next i

End Sub

Upvotes: 1

Views: 3955

Answers (1)

R3uK
R3uK

Reputation: 14537

I added some With for better readability and tested the values before dividing them : 

Public Sub SortMyData()
Dim wS As Worksheet
Dim i As Long
Dim N_Values As Long

Set wS = ThisWorkbook.Sheets("Sheet1")

N_Values = wS.Cells(wS.Rows.Count, 2).End(xlUp).Row


With wS
    For i = 6 To N_Values
        With .Cells(i, 2)
            If .NumberFormat <> "0.0%" Then
                .NumberFormat = "0.0%"
                If .Value2 <> vbNullString And IsNumeric(.Value2) Then .Value = .Value / 100
            Else
            End If
        End With

        With .Cells(i, 3)
            .HorizontalAlignment = xlRight
            Select Case .Value
                Case Is > 1000000
                    .Value = .Value / 1000000 & "Mb"
                Case Is > 1000
                    .Value = .Value / 1000 & "kb"
                Case Is > 1
                    .Value = .Value & "b"
                Case Else
                    .Value = 0
            End Select
'            If (.Value) > 1000000 Then
'               .Value = .Value / 1000000 & "Mb"
'            ElseIf (.Value) > 1000 Then
'                .Value = .Value / 1000 & "kb"
'            ElseIf .Value = Null Or .Text = Null Or .Value = "" Or .Text = "" Then
'                .Value = 0
'            End If
        End With

        With .Cells(i, 4)
            If .NumberFormat <> "0.0%" Then
                .NumberFormat = "0.0%"
                If .Value2 <> vbNullString And IsNumeric(.Value2) Then .Value = .Value / 100
            Else
            End If
        End With

    Next i
End With

End Sub

Upvotes: 1

Related Questions