Trying to get Excel VBA to return a number 0 in Cell D16(Conversion Rate) if cell D7 (Submissions) is a zero when dividing cell D13 (Hires) / D7 (Submissions). We sometimes don't have submissions but have hires from previous submissions (that we aren't allowed to record as a submission twice). The user clicks on a convert button to get the conversion rate, but the user keeps getting a Run-time error '11': Division by zero. We know that we have to occasionally put a zero in cell D7, but the user can't access the conversion rate box to manually type in the zero and can't leave D7 blank when submitting the form.

My current code for the division is:

Private Sub CommandButton2_Click()
Range("D16") = Range("D13") / Range("D7")
End Sub

Can anyone help?

User Form

With Zero

CodePudding user response：

You can better solve this in the cell and not by code like:

=if (D7 = 0, 0, D13/D7)

We first check if D7 = zero and if so the cell gets filled wiht zero else with your formula.

if you want to do it in code:

if Range("D7").value = 0 or IsEmpty(Range("D7").value) then
    Range("D16").value = 0
else
    Range("D16").value = Range("D13").value / Range("D7").value
end if

CodePudding user response：

Knowledge is power. Alders answered the question perfectly. I accept the answer