CODEWITHSUNDEEP
vbaexcel
FriedEgg
FriedEgg

Reputation: 23

VBA Code. Trying to fix the "IF" PARTS

So I have this written in my VBA:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G9:G28")) Is Nothing Then
    Application.EnableEvents = False
    With Target
        If IsNumeric(.Value) Then .Value = .Value / 100
    End With
    Application.EnableEvents = True
End If

End Sub

When I try to erase the data in the "G" Cells a 0% stays locked in. I think bc ".Value/100" is the 0% that my code says must go inside that cell. The above code is suppose to turn any number into a percentage but I think I wrote it and when its suppose to be blank it shows a "0%" but I want it to be blank.

Upvotes: 0

Views: 37

Answers (4)

Gary's Student
Gary's Student

Reputation: 96753

I would use a loop:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range

    If Not Intersect(Target, Range("G9:G28")) Is Nothing Then
        Application.EnableEvents = False
            For Each r In Intersect(Target, Range("G9:G28"))
                If IsNumeric(r.Value) Then r.Value = r.Value / 100
            Next r
        Application.EnableEvents = True
    End If

End Sub

This code will allow more than one cell to be changed.

Upvotes: 2

tyg
tyg

Reputation: 15075

Just test if the cell in question is empty with IsEmpty(Target.Value) before performing the conversion to percent, like this:

If Not IsEmpty(.Value) And IsNumeric(.Value) Then .Value = .Value / 100

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

Try the code below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G9:G28")) Is Nothing Then
    If Target.Count <= 1 Then ' make sure not more than 1 cell is changed
        Application.EnableEvents = False
        With Target
            If IsNumeric(.Value) Then
                If .Value = 0 Then
                    .Value = ""
                Else
                    .Value = .Value / 100
                End If
            End If
        End With
        Application.EnableEvents = True
    End If
End If

End Sub

Upvotes: 1

Pierre
Pierre

Reputation: 1046

Target can be SEVERAL cells. And an array divided by 100 makes no sense.

Upvotes: 0

Related Questions