Remove #DIV/0! from Macro formula

I created a button (Button 11) that when pressed will run this following code. Is it possible to eliminate the ugly #DIV/0! if the formula has nothing in the amount and rate field. Perhaps either leave it blank or just add a "-" ?


Public Sub HoursCalculation_V2()
'---------------------------------------------------------------------------------------
' Procedure : Update Percentage HoursCalculation
' Purpose   : This Procedure will walk through the columns and insert a formula instead
'             of the content of the asset is included in the vendor list
'---------------------------------------------------------------------------------------
Dim intNumber   As Integer
Dim intMonth    As Integer
Dim intColumns  As Integer
Dim rngEach     As Range
Dim rngData     As Range
Dim i           As Long
Dim lngRows     As Long


ThisWorkbook.Worksheets("InputFcst").Activate
lngRows = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
If lngRows < 4 Then
    MsgBox ("No data found - run cancelled.")
    Exit Sub
End If


'State how many columns you have for each month
' THE NUMBER 5 REPRESENTS HOW MANY COLUMNS THE CODE SHOULD SKIP FROM THE STARTING FROM THE COLUMN THAT RECEIVES THE FIRST FORMULA IN THIS CASE H4
intColumns = 5

'States the first cell that will be tested
' H4 RAPRESENT THE FIRST CELL THAT WILL RECEIVE THIS FORMULA
Set rngEach = Range("H4")

'States how many times you should change columns. In your workbook how many month that would be in the sheet
' THIS IS THE AMOUNT OF MONTHS, IN THIS CASE I AM UPDATING 12 MONTHS IF THE TEMPLATE CHANGES WE NEED TO UPDATE THIS VALUE
For intMonth = 1 To 12

    Set rngData = rngEach.Offset(-1, (intMonth - 1) * intColumns).Resize(lngRows + 2, 1).Find(What:="", After:=rngEach.Offset(-1, (intMonth - 1) * intColumns), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If rngData Is Nothing Then
        MsgBox ("No data found for " & rngEach.Offset(-1, (intMonth - 1) * intColumns) & " - month skipped.")
    ElseIf rngData.Address = "$H$4" Then
        MsgBox ("No data found for " & rngEach.Offset(-1, (intMonth - 1) * intColumns) & " - month skipped.")
    Else
        Range(rngEach.Offset(0, (intMonth - 1) * intColumns), rngData.Offset(-1, 0)).FormulaR1C1 _
            = "=8*RC[2]*HLOOKUP(MID(R3C,1,3),WorkingDays!R1C1:R2C12,2,0)"
    End If
    
Next

End Sub


Public Sub Forecast_Actual_Calc()
'---------------------------------------------------------------------------------------
' Procedure : Forecast_Actual_Calc
' Purpose   : This macro reads the headers and wherever it finds "Forecast Hours" at the end it
'             fills the rows below with the formula
'---------------------------------------------------------------------------------------

' THE CODE FINDS EVERYTHING WITH A HEADING OF "Forecast Hours" AND CHANGES THE VALUE IN A CALCULATED FORMULA OF (AMOUNT / RATE)
' IF COLUMN HEADINGS CHANGE OR THE POSITION OF THE HOURS AND RATE CHANGE THEN THE CODE MUST BE ADJUSTED
    Dim cel As Range
    For Each cel In Range("3:3").SpecialCells(xlCellTypeConstants)
        If Right(cel, 14) = "Forecast Hours" Then
            cel.Offset(1).Select
            cel.Offset(1).Resize(cel.End(xlDown).Row - cel.Row).FormulaR1C1 = "=rc[+3]/rc[+1]"
        End If
    Next cel
ActiveSheet.Buttons("Button 11").Delete
End Sub

Public Sub Master_Macro()
Application.Run ("Forecast_Actual_Calc")
Application.Run ("HoursCalculation_V2()")
Application.Run ("Vlookup_for_ProjectCode")
End Sub

Open in new window

joeserroneAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
i,

I suppose you want to correct this formula

"=rc[+3]/rc[+1]"

change it to "=IFERROR(rc[+3]/rc[+1],""-"")"

for Excel 2003 "=IF(ISERROR(rc[+3]/rc[+1]),""-"",rc[+3]/rc[+1])" or shorter
"=IF((rc[+1])=0,""-"",rc[+3]/rc[+1])"



Regards
0
 
joeserroneAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.