For Each .. Next statement

[Peekhamer]

Ik laat op een sheet kijken of de via VBA ingevulde formules fouten veroorzaken. Dit betekent dan dat data niet aanwezig is. Die zou ik graag gepresenteerd krijgen op een aparte sheet (de code start met eerst het verwijderen van die sheet en de link mochten die er zijn)

Wat ik wil: van elke fout die in mijn tabel wordt gevonden wil ik graag het adres als hyperlink op de sheet Fouten krijgen, startend vanaf A1.

Zoals ik het nu in de code heb is dat er een check is of er fouten zijn, dan wordt een link gemaakt die je dan naar de sheet brengt, maar dan naar cel a1.
Dat zegt nog niks, dan moet je nog gaan zoeken naar waar de foutwaarden zich bevinden. Daarom wil ik het graag op een aparte sheet met linkjes per fout hebben. Dat zit nog niet in de code hieronder omdat ik niet uit die For ... loop kom.

With .Cells.SpecialCells(xlCellTypeFormulas, 16)
            If .Count > 0 Then
                Dim J as integer
                Sheets.Add.Name = "Fouten"
               For Each Fout In .Cells.SpecialCells(xlCellTypeFormulas, 16)
                    Fout.Address = Sheets("Fout").Range("A, fout").Value
                    j = j+1
                Next
                '' melding+link dat er fouten gevonden zijn!
                With Sheets("DT Vestigingen").[L36]
                    .Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="LijstNieuw!A1", TextToDisplay:="Er zijn fouten aanwezig binnen de tabel op ""LijstNieuw""!"
                    With .Font
                        .Color = -16776961
                        .TintAndShade = 0
                    End With
                End With
            End If
        End With

Wie kan me verder helpen? Ik heb het gevoel dat ik wel in de buurt zit?

 

[HSV]

sub hsv()
dim cl as range, msg as string
on error resume next
  for each cl in activesheet.usedrange.specialcells(-4123, 16)
                msg = msg & cl.address & vblf
     next cl
if len(msg)>0 then msgbox msg
end sub

 

[Ginger]

@HSV, je procedure toont netjes de fouten in een messagebox, maar de TS wil er graag naartoe worden gebracht.

@Peekhamer, het is mij niet duidelijk WAAROM je langs al de fouten wilt lopen. Fout is toch fout? En kan je in 1 klap deleten als je géén error-codes in je sheet wilt zien? Of ga ik nu te kort door de bocht?
Mocht je wél langs elke fout moeten gaan om te beoordelen, dan zou ik dat doen via een methode zoals HSV die aangeeft, maar dan niet de foute cellen tonen in een msgbox maar je er rechtstreeks steeds naartoe laten brengen en dan de keuze geven of je 'm wilt verwijderen of niet. Is een stuk efficiënter lijkt mij.

 

[HSV]

Weet ik Leo; daar Ts al bezig is met een eigen code dacht ik dat het wel zou moeten lukken met een mix van beide codes, zodat ik niet alles op een presenteerblaadje geef.
Mocht hij/zij er niet uitkomen zal ik het kunnen plaatsen.

 

[Peekhamer]

@ Ginger,
De gebruiker van deze lijst ben ik uiteindelijk niet zelf, ik doe alleen het beheer. De macro toepassen is slechts 1 knop klikken. Indien er dus fouten zijn is het te gemakkelijk om dat over het hoofd te zien en daarom zou ik eerst dit stukje willen laten lopen. De lijst bevat een export uit een CRM-systeem. Fouten betekent foutieve data in het CRM systeem en de gebruiker moet deze fouten oplossen, maar idd niet door ze weg te gooien. Daarom wil ik fouten presenteren op een apart werkblad.
Dat blad is er alleen als er daadwerkelijk fouten zijn (want dat komt lang niet altijd voor) dus dat valt op. De "foutenblad-tab" laat ik nog een aparte kleur krijgen.

@HSV,
bedankt, ik heb zitten puzzelen maar kom er niet uit.

Op het eerste gezicht zit er niet zo heel veel verschil tussen jouw en mijn code toch? Ik had 'fout' niet gedimd as range
Er zit al een check op als er fouten zijn dus die "if len>0...." heb ik dan niet nodig toch?

Ik heb er nu dit van gemaakt:

 With .Cells.SpecialCells(xlCellTypeFormulas, 16) '' check of de formules foutwaarden hebben opgeleverd!
            If .Count > 0 Then
                Sheets.Add.Name = "Fouten"
                Dim Fout As Range
                Dim J As Integer
                J = 1
                For Each Fout In Sheets("Lijstnieuw").UsedRange.SpecialCells(xlCellTypeFormulas, 16)
                    Sheets("Fouten").Range("a", J).Value = Fout.Address
                    J = J + 1
                Next Fout

Dit levert me nog steeds een 1004 Door het object gedefinieerde fout op, dat zit m in de regel waar hij de fouten zou moeten wegschrijven.
Naar mijn idee doe ik nu hetzelfde als jij met de msgbox maar ik verwijs kennelijk ongeldig.

ALs dit dan gelukt is dan zou ik graag willen dat die foutenlijst de hyperlinkjes zijn naar de cellen met de fouten.

 

[VenA]

.Range("a", J) moet zijn .Range("a"&J) of .cells(j,1)

 

[Peekhamer]

Top, gelukt, weer een stap verder! Bedankt!
Nu nog zover zien te krijgen dat dat automatisch de verwijzing naar de fout-cel wordt.

 

[Peekhamer]

De hyperlink is gelukt, maar alleen als er fouten

With .Cells.SpecialCells(xlCellTypeFormulas, 16) '' check of de formules foutwaarden hebben opgeleverd!
            If .Count > 0 Then
                Sheets.Add.Name = "Fouten"
                With ActiveWorkbook.Sheets("Fouten").Tab
                    .Color = 255
                    .TintAndShade = 0
                End With
                Dim Fout As Range
                Dim J As Integer
                J = 1
                For Each Fout In Sheets("Lijstnieuw").UsedRange.SpecialCells(xlCellTypeFormulas, 16)
                    Sheets("Fouten").Cells(J, 1).Value = Fout.Address
                    ActiveSheet.Hyperlinks.Add Anchor:=Cells(J, 1), Address:="", SubAddress:="LIjstNieuw!" & Fout.Address, TextToDisplay:="Lijstnieuw!" & Fout.Address
                    J = J + 1
                Next Fout
                With Sheets("DT Vestigingen").[L36]
                    .Hyperlinks.Add Anchor:=Sheets("DT VEstigingen").[L36], Address:="", SubAddress:="Fouten!A1", TextToDisplay:="Er zijn fouten aanwezig binnen de tabel op ""LijstNieuw""!"
                    With .Font
                        .Color = -16776961
                        .TintAndShade = 0
                    End With
                End With
            End If
        End With

Als er GEEN fouten zijn dan krijg ik een runtime error 1004 omdat er geen fouten gevonden zijn.
Eraan voorbij gaan met On Error Resume Next werkt niet, als ik de statement ombouw tot

If .Cells.SpecialCells(xlCellTypeFormulas, 16).Count > 0 Then

werkt ook niet, in beide gevallen nog steeds dezelfde foutcode.
Ook niet als ik er

if Not .Cells... =0

van maak? De foutcode krijg ik omdat ik count denk ik?

En als aanvulling nog dit wat ik niet snap.

Sheets("DT Vestigingen").Select
    ActiveSheet.UsedRange.EntireRow.Hidden = False
    Range("B24, B25, B58, B59, B60, b61, b62, b63, b64, b65").EntireRow.Hidden = True

Dit is de pagina waar de gebruiker moet uitkomen. Daarom wordt de sheet selected.
Ik krijg een foutcode op de laatste rij code: "methode Range van Object _Global is mislukt"
Als ik vervolgens naar die fout ga en F8 of F5 doe dan werkt het gewoon wel? ik snap het niet

 

[HSV]

Plaats anders het document.

 

[Peekhamer]

het bekende verhaal; de sheet is complex

Volgens mij heb ik hem gevonden.

Dim R As Range
        On Error Resume Next
        Set R = .Cells.SpecialCells(xlCellTypeFormulas, 16)
        Err.Clear
        On Error GoTo 0
        
        If Not R Is Nothing Then