Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remove #DIV/0! from Macro formula

Posted on 2013-10-24
2
Medium Priority
?
334 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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

963 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