joeserrone
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER