Change #DIV/0! to Blank in VBA

[nhinx]

Dear Excel Experts,

I have a VBA below which works fine but when there is no value in column I to Column J it will give a value of #DIV/0! in computing the average What shall I do to convert it to blank? Kindly help me please. See code below

Private Sub Worksheet_Activate()

Row = 8 'row of description
Column = 6 'column of description

Dim sheet As Worksheet

Do Until ThisWorkbook.ActiveSheet.Cells(Row, Column).Value <> ""
Row = Row + 1
Loop

Do Until ThisWorkbook.ActiveSheet.Cells(Row, Column).Value = "TOTAL"

If ThisWorkbook.ActiveSheet.Cells(Row, Column).Value <> "" Then
ThisWorkbook.ActiveSheet.Cells(Row, 12).Value = "=AVERAGE(RC[-3]:RC[-1]" & ")"

ElseIf ThisWorkbook.ActiveSheet.Cells(Row, Column).Value = "" Then
ThisWorkbook.ActiveSheet.Cells(Row, 12).Value = ""

End If

Row = Row + 1

Loop

End Sub

Thanks,

Nhinx

 

[Sanjeev1976]

You can try the below code :

Private Sub Worksheet_Activate()

Row = 8 'row of description

Column = 6 'column of description

Dim sheet As Worksheet

Do Until ThisWorkbook.ActiveSheet.Cells(Row, Column).Value <> ""
    
    Row = Row + 1

Loop

Do Until ThisWorkbook.ActiveSheet.Cells(Row, Column).Value = "TOTAL"

    If ThisWorkbook.ActiveSheet.Cells(Row, Column).Value <> "" Then
    
        ThisWorkbook.ActiveSheet.Cells(Row, 12).Value = "=IFERROR(AVERAGE(RC[-3]:RC[-1]" & "),"")"
    
    End If
    
    Row = Row + 1

Loop

End Sub

 

[Alex Blakenburg]

The above will error out, you need to double up on the quotes.

ThisWorkbook.ActiveSheet.Cells(ROW, 12).Value = "=IFERROR(AVERAGE(RC[-3]:RC[-1]" & "),"""")"

 

[nhinx]

You can try the below code :

Private Sub Worksheet_Activate()

Row = 8 'row of description

Column = 6 'column of description

Dim sheet As Worksheet

Do Until ThisWorkbook.ActiveSheet.Cells(Row, Column).Value <> ""
   
    Row = Row + 1

Loop

Do Until ThisWorkbook.ActiveSheet.Cells(Row, Column).Value = "TOTAL"

    If ThisWorkbook.ActiveSheet.Cells(Row, Column).Value <> "" Then
   
        ThisWorkbook.ActiveSheet.Cells(Row, 12).Value = "=IFERROR(AVERAGE(RC[-3]:RC[-1]" & "),"")"
   
    End If
   
    Row = Row + 1

Loop

End Sub

Thanks Sanjeev. But it says Run time error 1004

 

[nhinx]

The above will error out, you need to double up on the quotes.

ThisWorkbook.ActiveSheet.Cells(ROW, 12).Value = "=IFERROR(AVERAGE(RC[-3]:RC[-1]" & "),"""")"

Thanks Alex. This statement works.

 

[Alex Blakenburg]

You're welcome. Glad we could help.