Solved

Remove #DIV/0! from Macro formula

Posted on 2013-10-24
2
311 Views
Last Modified: 2013-10-24
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

0
Comment
Question by:joeserrone
2 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:joeserrone
Comment Utility
Thank you!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now