< návrat zpět
MS Excel
Téma: Najít závislost ověření dat na jiných buňkách 
Zaslal/a pavelo 7.3.2017 15:13
Dobrý den, poradí někdo, jak odhalit buňku/buňky, na něž je prostřednictvím její/jejich adresy odkazováno v nastaveném ověření dat u jiné buňky/jiných buněk někde v listu? Jinak řečeno potřebuji u rozsáhlé tabulky, ve které jsou jednak hodnoty, jednak spoustu vzorců a také nastavená ověření dat pro zadávání hodnot do buněk, zjistit závislosti výsledků ověření na hodnotách jiných buněk, popřípadě potvrdit, že takové závislosti v listu neexistují.
Vzhledem k rozsáhlosti tabulky se mi nechce jít cestou vyhledání všech buněk, kde je nastaveno nějaké ověření (což excel umí) a u nich pak u všech jednotlivě nahlédnout do nastavení ověření, co tam je nastaveno.
Děkuji předem za jakýkoli podnět.
pavelo(8.3.2017 13:25)#035376 
Jeza.m napsal/a:
Pak by to nějak mohlo fungovat v rámci stejného listu, s tím že oblast pro výpis jsem nastavil na AA:AB, což lze změnit dle počtu používaných sloupců. Tyto pomocné pak lze smazat.
...
Ve VBA moc zběhlý nejsem, můžeš mi prosím jako pro nedouka napsat, kam mám přesně kód makra vložit a jak ho budu spouštět? Volné buňky na listu začínají sloupcem "BD". Díky.
citovatJeza.m(8.3.2017 13:36)#035377 
Viz. obrázek.
+ upravené makro které udělá výpis do buněk CA:CB.
Public Sub Najdi()
Dim rd As Single
Range("CA1:CB1") = Split("Vzorec;Buňka", ";")
rd = 2
ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Select
For Each cell In Selection
Range("CA" & rd).Formula = cell.Validation.Formula1
Range("CB" & rd) = cell.Address
rd = rd + 1
Next
End Sub
M@
Příloha: 
35377_makro.png (173kB, staženo 40x)
citovatpavelo(8.3.2017 14:03)#035379 Po spuštění makra to hlásí chybu - viz screen
Nevím proč, ale nejde mi sem přiložit soubor s obrázkem...
citovatpavelo(8.3.2017 14:15)#035380 Jeza.m(8.3.2017 14:59)#035381 Není ten list třeba zamčený?
Ono těžko říct, když to člověk nevidí :-)
citovat
Pretože sa v liste nenachádza žiadne xlCellTypeAllValidation, a nieje čo Selectnúť.
citovatJeza.m(8.3.2017 15:17)#035383 Tak ještě jedna úprava kódu :-)
Public Sub Najdi()
If VALIDACE = False Then Exit Sub
Dim rd As Single
Range("CA1:CB1") = Split("Vzorec;Buňka", ";")
rd = 2
ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Select
For Each cell In Selection
Range("CA" & rd).Formula = cell.Validation.Formula1
Range("CB" & rd) = cell.Address
rd = rd + 1
If cell.Validation.Formula2 <> "" Then
Range("CA" & rd).Formula = cell.Validation.Formula2
Range("CB" & rd) = cell.Address
rd = rd + 1
End If
Next
End Sub
Public Function VALIDACE() As Boolean
On Error GoTo x
VALIDACE = False
ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Select
VALIDACE = True
Exit Function
x:
End FunctioncitovatIný príklad:
Public Sub Najdi()
Dim rd As Single, aVAL(), RNG As Range, Cell As Range, E As Long
ReDim aVAL(1 To 2, 1 To 1)
aVAL(1, 1) = "Vzorec": aVAL(2, 1) = "Buňka"
rd = 1
On Error Resume Next
Set RNG = Worksheets("Data").Cells.SpecialCells(xlCellTypeAllValidation)
E = Err
On Error GoTo 0
If E = 0 Then
ReDim Preserve aVAL(1 To 2, 1 To RNG.Cells.Count + 1)
For Each Cell In RNG
rd = rd + 1
With Cell
aVAL(1, rd) = .Validation.Formula1
aVAL(2, rd) = .Address
If .Validation.Formula2 <> "" Then
ReDim Preserve aVAL(1 To 2, 1 To UBound(aVAL, 2) + 1)
rd = rd + 1
aVAL(1, rd) = .Validation.Formula2
aVAL(2, rd) = .Address
End If
End With
Next Cell
Set RNG = Nothing: Set Cell = Nothing
End If
With Worksheets("Overenie")
.Range(.Cells(1, "CB"), .Cells(Rows.Count, "CA").End(xlUp)).ClearContents
.Cells(1, "CA").Resize(rd, 2).Formula = WorksheetFunction.Transpose(aVAL)
End With
End Sub
EDIT: malá úprava podľa nápadu Jeza.m s iným druhom Formula2 (pri rôznych typoch Validation)
citovatAnonym(8.3.2017 20:27)#035390 Ahoj, asi by se dalo doplnil:
a) Validation.Formula1/2 zaznamenávat jen pokud výraz začíná "="
b) pak pro konkrétní ověřovanou buňku/buňky vyhodnotit
Set rng =
OverovanaBunka.Dependents
jeli průnik rng ve sloupci CA
ostatní smazat, ať je to přehlednější.
citovatpavelo(9.3.2017 10:01)#035399 Bohužel všechny předložené varianty mi hází chybu. Přikládám soubor, ve kterém bych chtěl analýzu aplikovat.
citovat
RSS nejnovější články