macro #div/0!

[Dores]

Hallo, zou iemand mij kunnen vertellen hoe ik het beste de volgende code kan afronden zodat uitkomst #div/0! wordt veranderd in 0

Range("BR3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-33]/RC[-39]<85%,RC[-39]*0.85,IF(RC[-33]/RC[-39]>105%,RC[-33]+2*(RC[-33]-1.05*RC[-39]),RC[-33]))"
    Selection.AutoFill Destination:=Range("BR3:BW3"), Type:=xlFillDefault
    Range("BR3:BW3").Select
    lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
    Selection.AutoFill Destination:=Range("BR3:BW" & lMaxRows), Type:=xlFillDefault

Bedankt,

Dores

 

[Cobbe]

Zou het?

Range("BR3").FormulaLocal = "=ALS.FOUT(ALS(AK3/AE3<85%;AE3*0,85;ALS(AK3/AE3>105%;AK3+2*(AK3-1,05*AE3);AK3));0)"
 Selection.AutoFill Destination:=Range("BR3:BW3"), Type:=xlFillDefault
 Range("BR3:BW3").Select
 lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
 Selection.AutoFill Destination:=Range("BR3:BW" & lMaxRows), Type:=xlFillDefault

Het kan verkeren als je een versie gebruikt lager dan xl2007!!!

Cobbe

 

[Dores]

Hi Cobbe,

Ik heb de volgende code gevonden en werkt ook goed.

Range("BR3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-33]/RC[-39]<85%,RC[-39]*0.85,IF(RC[-33]/RC[-39]>105%,RC[-33]+2*(RC[-33]-1.05*RC[-39]),RC[-33]))"
Selection.AutoFill Destination:=Range("BR3:BW3"), Type:=xlFillDefault
Range("BR3:BW3").Select
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Selection.AutoFill Destination:=Range("BR3:BW" & lMaxRows), Type:=xlFillDefault
Dim mystr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
mystr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & mystr & "),0," & mystr & ")"
End If
End If
Next
Range("BR3:BW3").Select
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Selection.AutoFill Destination:=Range("BR3:BW" & lMaxRows), Type:=xlFillDefault
End Sub

In iedergeval ga ik die van jouw ook proberen, bedankt voor je reactie.

Groet,