Solved

Remove #DIV/0! from Macro formula

Posted on 2013-10-24
2
316 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 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39597468
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
ID: 39599275
Thank you!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Getting error in connectionstring with Excel. 30 32
Excel User Form VBA Help 18 32
Clear Filter 8 39
sumifs excel 2013 3 15
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

773 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