XL2003 VBA: Replacing DIV/0 Error with String

[TechTank]

Hi,

I have a sheet with rows of cells with values. In X column I have a formula that looks for the MIN value of these cells and displays it.

The cells in the rows are populated from another sheet that grabs the data from a URL and some jiggery pokery through VBA. The row cells show the average value from a lot of data on another worksheet that is then copied to a "Master" worksheet where Column X does it's MIN formula.

If the average equates to a DIV/0 error I want to be able to show a String like NULL or NULL_ITEMS to stop it throwing the MIN formula out when it's copied across. I've tried all sorts but I can't seem to get it to do this but it will show a 0 using the code below:

'Removes #DIV/0! Errors
    For Each DIVRange In Selection
    If IsError(DIVRange.Value) Then
    If DIVRange.Value = CVErr(xlErrDiv0) Then DIVRange.Value = 0
    End If
    Next DIVRange

I tried adding this in my PUBLIC declarations: Public NULLITEMS As String
And in another module I added: NULLITEMS = "Null Items"

To try this:

'Removes #DIV/0! Errors
    For Each DIVRange In Selection
    If IsError(DIVRange.Value) Then
    If DIVRange.Value = CVErr(xlErrDiv0) Then DIVRange.Value = NULLITEMS
    End If
    Next DIVRange

This didn't work and I don't know why. Any help greatly appreciated as this is throwing my MIN values right out along with what little hair I have left.

Thank you and sorry for the long blurb.

Mark.

EDIT:

The entire code I'm using to manipulate this data on another sheet before copying to the "Master" is below for reference:

'Clears Row 11
Rows("11:11").ClearContents

'Creates an Average of All Cells Above Range Set
    Range("C11").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-10]C:R[-1]C)"
    
'Removes #DIV/0! Errors
    For Each DIVRange In Selection
    If IsError(DIVRange.Value) Then
    If DIVRange.Value = CVErr(xlErrDiv0) Then DIVRange.Value = 0
    End If
    Next DIVRange
    
'Sets the Value to the Variable 
    VALUE = Cells(11, "C")

Sheets("MASTER").Select
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = VALUE

 

[Gary's Student]

Easy replacement:

Sub DivideAndConquer()
Dim r As Range
For Each r In ActiveSheet.UsedRange
    With r
        If .Text = "#DIV/0!" Then
            .Clear
            .NumberFormat = "@"
            .Value = "NULL"
        End If
    End With
Next
End Sub

 

[TechTank]

Hi Gary's Student,

That's certainly replacing the DIV/0 with NULL but it seems that the error could now be tied in with copying the variable of VALUE to the "MASTER" sheet from the "Do Jiggery Pokery" sheet:

Sheets("MASTER").Select         
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = VALUE

It seems to be holding the last value that was stored in the VALUE variable. If this value is a number then it changes but not for copying the NULL, any ideas?

Thanks for the quick reply though, much appreciated.

Mark.

 

[TechTank]

Confirmed that this is a Copy issue. The original code works fine it seems:

'Removes #DIV/0! Errors
    For Each DIVRange In Selection
    If IsError(DIVRange.Value) Then
    If DIVRange.Value = CVErr(xlErrDiv0) Then DIVRange.Value = "NULL"
    End If
    Next DIVRange

But this bit will not copy across the characters:

Sheets("MASTER").Select
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = VALUE

EDIT: Could it be that the VALUE isn't copying and pasting the characters because I am trying to get it to copy and paste characters\string instead of numbers\integer?

The declaration I've made for this is 'Public VALUE As Long'.

 

[TechTank]

FIXED:

Changed the Public declaration to 'Public VALUE As Variant' so that it could hold both strings and integers.

 

[Gary's Student]

Good catch!

 

[TechTank]

Thank you but couldn't have done it without your code to confirm that it wasn't my code that was the issue for changing the DIV/0, I was clueless without you.

Best regards and thank you.

Mark.