office 2010: opmaak cellen via vba code: cellen met #N/B?

[arjoderoon]

Ik heb een bestand waarin ik een vba code de opmaak van de cellen wil laten regelen op basis van de waarde in de cel. Dit is dus geen voorwaardelijke opmaak regel die gemaakt wordt.

ik heb door van andere codes te kijken wat er staat, onderstaande code hiervoor:

    For Each cell In .Range("AE14:AE" & aantalrijen + x)
      If cell.Value > 0 Then
        cell.Interior.Color = 13561798
        cell.Font.Color = -16752384
        cell.Font.Bold = True
      Else
        If cell.Value < 0 Then
          cell.Interior.Color = 13551615
          cell.Font.Color = RGB(156, 0, 6)
          cell.Font.Bold = False
         Else
          cell.Interior.Color = 12566463
          cell.Font.Color = -16777216
          cell.Font.Bold = False
        End If
      End If
    Next cell

De eerste 2 parameters werken prima. als de waarde groter is dan 0 krijg ik een groene achtergrond en groene vette tekst. Als de waarde kleiner is dan 0 krijg ik een rode achtergrond en tekst.
Alleen heb ik ook een hoop cellen waar door een 'als' formule, een #N/B in geplaatst wordt.
deze formule is:

 .Range("AE14:AE" & aantalrijen + x) = "=IF(ISERROR((RC[-1]*0.5)-RC[-17]-(RC[-5])),""#N/B"",(RC[-1]*0.5)-RC[-17]-(RC[-5]))"

alleen de cellen waar #N/B komt te staan, krijgen dezelfde opmaak als de cellen met een waarde groter 0.

ik heb de code diverse malen nagelopen. En ik zie niet waarom die #N/B cellen dezelfde opmaak krijgen als de cellen met een waarde groter dan 0.
Ik heb daarvoor al geprobeerd om na de 2e 'Else' dit te doen:

         Else
          if cell.value = "#N/B" Then
          cell.Interior.Color = 12566463
          cell.Font.Color = -16777216
          cell.Font.Bold = False
          End if

        End If

Hoe krijg ik alle cellen waarin #N/B gezet is toch met de opmaak zoals beschreven (grijze achtergrond, zwarte tekst)?

 

[edmoor]

Die #N/B is een gereserveerde waarde. In beeld zie je inderdaad #N/B maar de waarde van het veld is: Fout 2042
Je moet dus met de If constructie niet controleren op #N/B. Doe het als volgt:

    Dim NB As Variant
    NB = Cell.Value
    If Typename(NB) = "Error" Then

Dan is het dus "#N/B" en anders niet.
De foutcode van die error is 2042.

 

[arjoderoon]

thanks voor je hulp / suggestie.

zo werkt hij nu bij mij:

Sub kopie_formule()
Dim x As Integer
Dim aantalrijen As Integer
[b][i]Dim NB As Variant[/i][/b]

With Worksheets("Promo Evaluatie")

.Unprotect Password:="TM"
x = 13
    aantalrijen = .Range("G14", .Range("G13").End(xlDown)).Cells.Count
    .Range("X14:X" & aantalrijen + x) = "=IF(ISERROR((RC[-7]-RC[-2])),""#N/B"",IF(RC[-7]-RC[-2]<0,0,(RC[-7]-RC[-2])))"
    .Range("Y14:Y" & aantalrijen + x) = "=IF(ISERROR(RC[-1]/RC[-8]),""#N/B"",RC[-1]/RC[-8])"
    .Range("Z14:Z" & aantalrijen + x) = "=IF(ISERROR(RC[-11]*RC[-1]),""#N/B"",RC[-11]*RC[-1])"
    .Range("AA14:AA" & aantalrijen + x) = "=IF(ISERROR(RC[-8]/RC[-22]/RC[-7]),""#N/B"",RC[-8]/RC[-22]/RC[-7])"
    .Range("AB14:AB" & aantalrijen + x) = "=IF(ISERROR(RC[-6]/RC[-23]/RC[-5]),""#N/B"",RC[-6]/RC[-23]/RC[-5])"
    .Range("AC14:AC" & aantalrijen + x) = "=IF(RC[-11]="""",""#N/B"",IF(RC[-9]="""",""#N/B"",IF(RC[-24]="""",""#N/B"",RC[-11]-(RC[-9]*RC[-24]))))"
    .Range("AD14:AD" & aantalrijen + x) = "=IF(ISERROR(RC[-1]*0.5),""#N/B"",RC[-1]*0.5)"
    .Range("AE14:AE" & aantalrijen + x) = "=IF(ISERROR((RC[-1]*0.5)-RC[-17]-(RC[-5])),""#N/B"",(RC[-1]*0.5)-RC[-17]-(RC[-5]))"
    .Range("AF14:AF" & aantalrijen + x) = "=IF(ISERROR((RC[-18]+RC[-17])/RC[-3]),""#N/B"",IF(((RC[-18]+RC[-17])/RC[-3])<0,""#N/B"",((RC[-18]+RC[-17])/RC[-3])))"
    .Range("AG14:AG" & aantalrijen + x) = "=IF(ISERROR((RC[-19]+RC[-18]+RC[-7])/RC[-4]),""#N/B"",IF(((RC[-19]+RC[-18]+RC[-7])/RC[-4])<0,""#N/B"",(RC[-19]+RC[-18]+RC[-7])/RC[-4]))"

  For Each cell In .Range("AE14:AE" & aantalrijen + x)
  [b][i]NB = cell.Value
     If NB = "#N/B" Then[/i][/b]
          cell.Interior.Color = RGB(191, 191, 191)
          cell.Font.Color = RGB(0, 0, 0)
          cell.Font.Bold = False
            Else
            If cell.Value > 0 Then
                cell.Interior.Color = 13561798
                cell.Font.Color = -16752384
                cell.Font.Bold = True
                Else
                If cell.Value < 0 Then
                  cell.Interior.Color = 13551615
                  cell.Font.Color = RGB(156, 0, 6)
                  cell.Font.Bold = False
                End If
            End If
      End If
    Next cell
      
.Protect Password:="TM", AllowFormattingCells:=True, AllowFiltering:=True

End With

End Sub

 

[edmoor]

Zorg er wel voor dat je inspringpunten kloppen. Dan is het een stuk makkelijker te lezen en eventueel te corrigeren. Zelf zou ik het zo doen:

    For Each cell In .Range("AE14:AE" & aantalrijen + x)
        NB = cell.Value
        If TypeName(NB) = "Error" Then
            cell.Interior.Color = RGB(191, 191, 191)
            cell.Font.Color = RGB(0, 0, 0)
            cell.Font.Bold = False
        Else
            Select Case cell.Value
                Case Is > 0
                    cell.Interior.Color = 13561798
                    cell.Font.Color = -16752384
                    cell.Font.Bold = True
                Case Is < 0
                    cell.Interior.Color = 13551615
                    cell.Font.Color = RGB(156, 0, 6)
                    cell.Font.Bold = False
            End Select
        End If
    Next cell

Ik zie net dat je het weer hebt aangepast.
Dit moet je dus niet gebruiken:
If NB = "#N/B" Then

Maar dit:
If TypeName(NB) = "Error"

 

[arjoderoon]

toen ik "Error" had werkte het niet. Maar met "#N/B" juist wel...

Maar kan dat komen doordat ik de if formule, een stukje eerder in de routine, een #N/B waarde weg laat schrijven als de isfout functie een fout detecteert?

 .Range("AE14:AE" & aantalrijen + x) = "=IF([b]ISERROR[/b]((RC[-1]*0.5)-RC[-17]-(RC[-5])),[b]""#N/B""[/b],(RC[-1]*0.5)-RC[-17]-(RC[-5]))"

ik heb door vaak op F8 te drukken de verschillende cel-waardes die langs kwamen gevolgd en daar kwam bij de 'isfout' rijen "#N/B" als cel-waarde naar voren. Vandaar dat ik nu If NB = "#N/B" Then gedaan heb. En toen werkte het direct perfect...

 

[edmoor]

Het ligt eraan hoe die #N/B in de cel terecht komt. Als tekstwaarde of als zodanige waarde dat Excel de ervoor gereserveerde error gebruikt. Daar zit verschil in. Net als met de woorden WAAR en ONWAAR.

 

[arjoderoon]

In dit geval zal de #N/B er als tekst waarde in komen. Ik had in plaats van #N/B ook 'nee' kunnen doen, dan was het misschien al duidelijker.
De #N/B is bij mij nu een conditionele resultant van de isfout functie op de werkelijke functie.